Forum Discussion

Ocasio27's avatar
Ocasio27
Iron Contributor
Aug 07, 2020
Solved

How to consolidate repeated records on a table

I have 2 tables, I would like to convert, left to right in this example. Only way I have done it is by using a lot of steps on Power Query which slows down my computer and often crashed (original fil...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 10, 2020

    Ocasio27 

    The easiest way is with Power Query - query the source, group by ID using function as here for aggregation

    let
    Source = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content],
    #"Grouped Rows" = Table.Group(Source,
        {"ID"},
        {
            {"Locations", each Text.Combine(_[Location]," + ")}
        }
    )
in
    #"Grouped Rows"

    Result is like

Abiola1's avatar
Abiola1
MVP
Aug 07, 2020
Hello,

Kindly make me understand what you me by "I want to convert left table to right table"

I believe Flash Fill "will" do what you wanna achieve... Just need for more explanation based on the sample provided
  • Ocasio27's avatar
    Ocasio27
    Iron Contributor
    Aug 07, 2020

    Abiola1 

     

    These are 2 tables, one is column A and B, the other is column D and E. I want to convert table 1 to table 2

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 10, 2020

      Ocasio27 

      The easiest way is with Power Query - query the source, group by ID using function as here for aggregation

      let
    Source = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content],
    #"Grouped Rows" = Table.Group(Source,
        {"ID"},
        {
            {"Locations", each Text.Combine(_[Location]," + ")}
        }
    )
in
    #"Grouped Rows"

      Result is like

Resources