none
How to move row to previous row RRS feed

  • Question

  • I have a situation where I am getting a log file and the error rows create two rows in the data. I need to find a way to move the second row to the end of the row above it in column B.

    In the example below, line 103 should be appended to row 102 in column B

    Tuesday, August 15, 2017 8:52 PM

Answers

  • Assuming your file has 1 column (before splitting), the code below adds an Index and a Group column: with the Index value if the first part of the row is date/time, otherwise null. Next this column is filled down, meaning that the rows without date/time as first part, get the same Group as the previous row(s).

    Now the table is grouped on this group and text is combined (separated with a space) within each group. Then the Group column is removed.

    let
        Source = Table1,
        Indexed = Table.AddIndexColumn(Source, "Index", 1, 1),
        AddedGroup = Table.AddColumn(Indexed, "Group", each if (try DateTime.From(Text.Start([Column1],19)))[HasError] then null else [Index]),
        FilledDown = Table.FillDown(AddedGroup,{"Group"}),
        Grouped = Table.Group(FilledDown, {"Group"}, {{"Column1", each Text.Combine([Column1]," "), type text}}),
        RemovedGroup = Table.RemoveColumns(Grouped,{"Group"})
    in
        RemovedGroup


    Wednesday, August 16, 2017 3:55 AM