Forum Discussion

KelvinMorel's avatar
KelvinMorel
Helper II
2 years ago

Transform duplicate rows into columns ordered by categories

Hi all, I got to transform duplciated category rows into columns using: = Table.Group(Source, {"ID", "ORDER"}, {{"CATEGORY", each Text.Combine([CATEGORY], ","), type nullable text}}) then split th...
Need Help
ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

Why not make it easy for someone to help you by providing your dummy data as text which can be easily copy/pasted into Excel or Power BI? Your screenshot is not particularly useful, although you might find someone willing to take the time to transcribe it, you'll probably get more responses if you make it easier.

 

Also, I do not see in your data any method to decide which column the different listed categories should go into.

 

In other words, how do we know which are "mobile" and which are "sound"?

 

 

  • KelvinMorel's avatar
    KelvinMorel
    Helper II
    2 years ago

    Hi ronrsnfld,

    Fair enough but I didn't realize the "dummy data" would be a big deal, maybe I need reformulate the question:

    How do I order the data get the same sequence before transforming it?

     

    Cheers,

    • ronrosenfeld's avatar
      ronrosenfeld
      Frequent Visitor
      2 years ago

      One way would be by using the Table.Group function.

    • AlienSx's avatar
      AlienSx
      Super User
      2 years ago

      Hi, KelvinMorel 

      let
    Source = your_table,
    g = Table.Group(Source, {"ID", "ORDER"}, {{"all", each [a = List.Distinct([MOBILE]), b = Record.FromList(a, a)][b]}}),
    expand = Table.ExpandRecordColumn(g, "all", List.Distinct(Source[MOBILE]))
in
    expand

      you may rename columns later.