Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |