How to make a pivot using Power Query

[partymia]

Hello,

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:

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

And 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. 

[CNENFRNL · Accepted solution]

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"

[amitchandak]

@partymia , You need unpivot color and data column. then use count when you do unpivot 

https://radacad.com/pivot-and-unpivot-with-power-bi

After that add another column, which sum these new 5 columns 

= [Yellow] +[blue] +[red] +[white] + [black]