How to make a pivot using Power Query

Question

Hello,&nbsp;I am trying to build a pivot table using Power Query to later feed into my normal table as values but am quite unsure how to proceed. In excel is quite easy but in Power Bi not so much. I have the following raw data:&nbsp;SKUYes/NoColorDataH515322623Exclude&nbsp;TRUEM32523623IncludeYellowTRUEM32523623IncludeBlueTRUES325923512IncludeYellowTRUES325923512IncludeBlueTRUES325923512IncludeBlackTRUED21523652IncludeYellowTRUED21523652IncludeBlueTRUED21523652IncludeWhiteTRUEF36262362Exclude&nbsp;TRUEJ236277244Exclude&nbsp;TRUEK235238692IncludeYellowTRUEK235238692IncludeBlueTRUEK235238692IncludeBlackTRUEL236234774IncludeYellowTRUEL236234774IncludeRedTRUEL236234774IncludeBlackTRUEQ135236243Exclude&nbsp;TRUEU392582352Exclude&nbsp;TRUET2184914IncludeWhiteFALSEB3285203ExcludeRedFALSEAnd i am trying to get to the following pivot:Is it possible to do in Power query, as i need the results from last column to link back to my SKU in the main tables basically? Thanks for the help in advance.&nbsp;

cnenfrnl · Accepted Answer

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdLPC4IwFAfw/2VnD+29zekxSemHHfIHEeIhVCgadUnqz29qgppjwQ6Dfdj3vbdlGVlzyhHABiQW8d+FrMtK7dRKotQnuZWRPQIH7MTm3otTJeXjZXaerKuhipVyATkFw3Ua+Od9njwXt6FbAW2q46bYeTdNnVfHy/U5YoEah93MRDvcbXMqBDCmJTvVHaBju6bSNXBau5ZNJha2j8mEYIZcDYyq8g/1E3ug2H4jpv+RKbrAnaYNLUmAOsyl46z+fYJlGHfMQ3A4LMZRXeFflH8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU = _t, #"Yes/No" = _t, Color = _t, Data = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Color]), "Color", "Data", List.Count),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Sum", each List.Sum(List.Skip(Record.ToList(_),2)))
in
    #"Added Custom"

Forum Discussion

How to make a pivot using Power Query

2 Replies

Related content

Transpose & Pivot using Power Query

Pivot issue

Pivot / Unpivot?

Data transform using power query

Power BI Pivot Columns ?

SKU	Yes/No	Color	Data
H515322623	Exclude		TRUE
M32523623	Include	Yellow	TRUE
M32523623	Include	Blue	TRUE
S325923512	Include	Yellow	TRUE
S325923512	Include	Blue	TRUE
S325923512	Include	Black	TRUE
D21523652	Include	Yellow	TRUE
D21523652	Include	Blue	TRUE
D21523652	Include	White	TRUE
F36262362	Exclude		TRUE
J236277244	Exclude		TRUE
K235238692	Include	Yellow	TRUE
K235238692	Include	Blue	TRUE
K235238692	Include	Black	TRUE
L236234774	Include	Yellow	TRUE
L236234774	Include	Red	TRUE
L236234774	Include	Black	TRUE
Q135236243	Exclude		TRUE
U392582352	Exclude		TRUE
T2184914	Include	White	FALSE
B3285203	Exclude	Red	FALSE