locked
Cleanup the out of order ROWS RRS feed

  • Question

  • I am working with an excel output from a web application and I get data this nicely formatted for the most part but one of the issues is that the NOTES field is large and for some of the ROWS the NOTES get split into multiple columns and pushes the other data fields out of order

    Field 1 Field 2 Notes Field 4 Field 5 Field 6 NO NAME 1 NO NAME 2 NO NAME 3
    data data Notes 1 data data data
    data data part  A of Notes 2 part B of Notes 2 part C of Notes 2 part D of Notes 2 data for field 4 data for field 5 data for field 6
    data data Notes 3 data data data
    data data part A of Notes 4 part B of Notes 4 part C of Notes 4 data for field 4 data for field 5 data for field 6

    I was wondering if I could use Power Query to conditionally (only for the rows that have data in more than 6 COLUMNS) combine the different NOTES part and move following data fields left accordingly and make the table look like below

    Field 1 Field 2 Notes Field 4 Field 5 Field 6
    data data Notes 1 data data data
    data data part  A of Notes 2 part B of Notes 2 part C of Notes 2 part D of Notes 2 data for field 4 data for field 5 data for field 6
    data data Notes 3 data data data
    data data part A of Notes 4 part B of Notes 4 part C of Notes 4 data for field 4 data for field 5 data for field 6

    Thursday, March 28, 2019 7:55 PM

Answers

  • Hi VanGo9,

    Following code should work:

    let
        Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
        cols = List.FirstN(Table.ColumnNames(Source), 6),
        replace = Table.ReplaceValue(Source,null,"",Replacer.ReplaceValue,cols),
        rows = List.Transform(Table.ToRows(replace), List.RemoveNulls),
        transform = List.Transform(rows, each List.FirstN(_, 2) &
                                  {Text.Combine(List.Range(_, 2, List.Count(_)-5), " ")} & List.LastN(_, 3)),
        final = Table.FromRows(transform, cols)
    in
        final

    Friday, March 29, 2019 10:16 AM

All replies

  • Hi VanGo9,

    Following code should work:

    let
        Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
        cols = List.FirstN(Table.ColumnNames(Source), 6),
        replace = Table.ReplaceValue(Source,null,"",Replacer.ReplaceValue,cols),
        rows = List.Transform(Table.ToRows(replace), List.RemoveNulls),
        transform = List.Transform(rows, each List.FirstN(_, 2) &
                                  {Text.Combine(List.Range(_, 2, List.Count(_)-5), " ")} & List.LastN(_, 3)),
        final = Table.FromRows(transform, cols)
    in
        final

    Friday, March 29, 2019 10:16 AM
  • Nice solution Aleksei. Only thing I would change is the transform step to separate note parts by linefeed, as shown in the requested output table, instead of by space.

    transform = List.Transform(rows, each List.FirstN(_, 2) &
                                  {Text.Combine(List.Range(_, 2, List.Count(_)-5), "#(lf)")} & List.LastN(_, 3)),


    Friday, March 29, 2019 5:02 PM
  • Thanks, Colin. I had same idea) With your code the result is neat and I like it. But we don't know, what real data in Notes column is. That's why I kept result table's format from start topic. By sight that is not obviously, but after pasting the table in Excel, it becomes clear.

    Monday, April 1, 2019 10:43 AM