Pivoting with Multiple Values of The Same Type

[Anonymous]

Hi,

I've got this set of data in Power Query:

CASEID	Type	ASSOCIATIONID
100000161	Sales Order	3010001838
100000161	Sales Order	3010003918
100000161	Item	1003706

But I'm trying to get to this solution:

CASEID	Sales Order #1	Sales Order #2	Item #1
100000161	3010001838	3010003918	1003706

How can I achieve this through Power Query?  I've tried using the Pivot and Unpivot functions, but these are not working.  Also, there will be instances where there are more than one Item and more than 2 sales orders.  TIA!

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this m code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAUMzQyUdpeDEnNRiBf+ilNQiIM/YACRnaGFsoRSrQ1ihsaUhpkLPktRcIAUUMjY3MINIY+i3BAJipKCGGRsbK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CASEID = _t, Type = _t, ASSOCIATIONID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CASEID", Int64.Type}, {"Type", type text}, {"ASSOCIATIONID", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"CASEID", "Type"},
{
{
"Rows", each
Table.CombineColumns(Table.TransformColumnTypes(
Table.AddIndexColumn(_, "Index", 1,1),
{"Index", type text}),
{"Type", "Index"}, Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "Type2"),

type table [CASEID=number, Type2=text, ASSOCIATIONID=number]
}
}
),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Rows"}),
#"Expanded Rows" = Table.ExpandTableColumn(#"Removed Other Columns", "Rows", {"CASEID", "Type2", "ASSOCIATIONID"}, {"CASEID", "Type2", "ASSOCIATIONID"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Rows", List.Distinct(#"Expanded Rows"[Type2]), "Type2", "ASSOCIATIONID")
in
#"Pivoted Column"

[Anonymous]

Thank you, @camargos88 !  This did it!