none
Combine rows, keeping columns RRS feed

  • Question

  • Hello Power Query Community.

    I have a challenge that I don't seem to be able to figure out. I wish to combine rows with the same ID, but maintain the existing columns. This picture will provide a better explanation.

    What I wish to happen (as in this example) is for Order B stage date/times to be placed in the same row. There are numerous rows in my source file so the solution can't be specific to Order B, it has to apply to EVERY order in the table.

    Any help would be appreciated.

    Thanks,

    -Steve

    Thursday, May 3, 2018 8:48 PM

Answers

  • Hi Steve,

    The following code shows one way to accomplish your requirement:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        UnpivotedColumns = Table.UnpivotOtherColumns(Source, {"ID "}, "Attribute", "Value"),
        PivotedColumn = Table.Pivot(UnpivotedColumns, List.Distinct(UnpivotedColumns[Attribute]), "Attribute", "Value"),
        CombinedRows = Table.Combine({Table.FirstN(Source, 2), PivotedColumn}),
        RemovedFirstRows = Table.RemoveFirstN(CombinedRows, 2)
    in
        RemovedFirstRows

    Thursday, May 3, 2018 10:02 PM

All replies

  • Hi Steve,

    The following code shows one way to accomplish your requirement:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        UnpivotedColumns = Table.UnpivotOtherColumns(Source, {"ID "}, "Attribute", "Value"),
        PivotedColumn = Table.Pivot(UnpivotedColumns, List.Distinct(UnpivotedColumns[Attribute]), "Attribute", "Value"),
        CombinedRows = Table.Combine({Table.FirstN(Source, 2), PivotedColumn}),
        RemovedFirstRows = Table.RemoveFirstN(CombinedRows, 2)
    in
        RemovedFirstRows

    Thursday, May 3, 2018 10:02 PM
  • That did it, thank you very much Colin!
    Friday, May 4, 2018 12:06 PM
  • Hi Steve,

    No problem. Since my last post, I've created three additional versions of the code. The most straightforward of them all is provided below. I'm using a small dataset, so I am unable to determine whether there would be a significant performance difference with a large dataset.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        StageColumnsNames = List.Select(Table.ColumnNames(Source), each  _ <> "ID"),
        GroupedRows = Table.Group(Source, {"ID"}, List.Accumulate(StageColumnsNames, {}, (state, current) => state & {{current, each List.Max(Table.Column(_, current))}}))
    in
        GroupedRows

    Sunday, May 6, 2018 1:17 PM