Power Query transform dataset with unique id's and extend columns

Question

Straight to the point:

I need to turn a dataset (source from a CSV file) into a new table in Excel using power query editor (M language). The Product code in the example below should be unique, and for every URL there should be a new column, the name of the column could be "1-x" or "column 1-x", it doesn't matter. Just if the "IsDefault".value = 1, then it should go to the first column, for the 0 values, it doesn't matter (maybe from top to bottom would be perfect).

Source: Here's a picture of the dataset

ProductCode,URL,IsDefault
1,a,1
2,b,1
2,c,0
3,d,0
2,e,0
3,f,1

Goal:

ProductCode,column1,column2,column3
1,a,null,null,null
2,b,c,e
3,f,d,null,null

I already achieved to count them with grouping, but I can't figure out how to add the number of columns from the maximum value and then how to transform this into the new table.

let
    Quelle = Csv.Document(File.Contents("C:\Users\name\Desktop\cat-images-test.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Höher gestufte Header" = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true]),
    #"Gruppierte Zeilen" = Table.Group(#"Höher gestufte Header", {"ProductCode"}, {{"Anzahl", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
    #"Gruppierte Zeilen"

This code counts how many columns I need to insert, it's the max value of the column "Anzahl". There was no such scenario on other threads and I'm not very good in M language.

Here's a picture of the count.

Thanks for the help.

Rory Rory · Accepted Answer · 2021-01-13T13:56:49

Group the column on ProductCode, choosing All Rows for the URL aggregation. That will give you a new column with a Table for each row (I called it URLs). Now add a custom column using the formula:

=Table.Column([URLs], "URL")

which will produce List for each row. Click the little button in the top right of the new list column and choose Extract Values, and specify a comma delimiter. You can then Split this new column on the comma to create new columns to get your desired result.

Power Query transform dataset with unique id's and extend columns

1 Answers