Transform duplicate rows into columns ordered by categories

[KelvinMorel]

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 the CATEGORY column

Starting dataset (Dummy data)

I get everything scrambled 

I would like to order the different categories by columns as follows

ID	ORDER	MOBILE	SOUND
008328	810079	Phone	Headset
008794	810078	Phone	null
008560	810077	null	Headset

Any idea is welcome

Cheers

 

[ronrsnfld]

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]

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,

[AlienSx]

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. 

[ronrosenfeld]

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