Forum Discussion
UnPivot twice in same tabel. How can I do that???
Hi there,
I have a tabel where I now have un-pivot 4 (Priority 1, 2, 3 and 4) columns to 2 columns (Priority type and then the Priority value).
Now I wish to unpivot 4 other columns (Workload 1, 2, 3, and 4) to 2 columns (Workload type and then the workload value) from the same tabel.
When I do that some of the rows gets dublicated/twisted like so:
As you can see the values gets twisted/doblicated some how, because Workload 1 belongs to Priority 1 and Workload 2 belongs to Priority 2. They should not get mixed. How can I avoid that?
Hi,
lbendlin ,thanks for your concern about the problem, and i want to offer some more infotmation for user to refer to.
hello Anonymous , you can create a blank query and put the following code to advanced editor in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUSowBBFGIMIYRJgAiXKQWDlIrBwkVm6iFKsTrZREkupk4lXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, #"Priority 1" = _t, #"Priority 2" = _t, #"Priority 3" = _t, #"Priority 4" = _t, #"Workload 1" = _t, #"Workload 2" = _t, #"Workload 3" = _t, #"Workload 4" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Priority 1", type text}, {"Priority 2", type text}, {"Priority 3", type text}, {"Priority 4", type text}, {"Workload 1", type text}, {"Workload 2", type text}, {"Workload 3", type text}, {"Workload 4", type text}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Type", "Priority 1", "Priority 2", "Priority 3", "Priority 4"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Type"}, "Attribute", "Value"), #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 1, Int64.Type), Custom1 = Table.SelectColumns(#"Changed Type",{"Type", "Workload 1", "Workload 2", "Workload 3", "Workload 4"}), #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(Custom1, {"Type"}, "Attribute", "Value"), #"Added Index1" = Table.AddIndexColumn(#"Unpivoted Other Columns1", "Index", 1, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index", {"Index"}, "Added Index", JoinKind.LeftOuter), #"Expanded Added Index" = Table.ExpandTableColumn(#"Merged Queries", "Added Index", {"Attribute", "Value"}, {"Attribute.1", "Value.1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index",{"Index"}) in #"Removed Columns"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi,
lbendlin ,thanks for your concern about the problem, and i want to offer some more infotmation for user to refer to.
hello Anonymous , you can create a blank query and put the following code to advanced editor in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUSowBBFGIMIYRJgAiXKQWDlIrBwkVm6iFKsTrZREkupk4lXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, #"Priority 1" = _t, #"Priority 2" = _t, #"Priority 3" = _t, #"Priority 4" = _t, #"Workload 1" = _t, #"Workload 2" = _t, #"Workload 3" = _t, #"Workload 4" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Priority 1", type text}, {"Priority 2", type text}, {"Priority 3", type text}, {"Priority 4", type text}, {"Workload 1", type text}, {"Workload 2", type text}, {"Workload 3", type text}, {"Workload 4", type text}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Type", "Priority 1", "Priority 2", "Priority 3", "Priority 4"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Type"}, "Attribute", "Value"), #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 1, Int64.Type), Custom1 = Table.SelectColumns(#"Changed Type",{"Type", "Workload 1", "Workload 2", "Workload 3", "Workload 4"}), #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(Custom1, {"Type"}, "Attribute", "Value"), #"Added Index1" = Table.AddIndexColumn(#"Unpivoted Other Columns1", "Index", 1, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index", {"Index"}, "Added Index", JoinKind.LeftOuter), #"Expanded Added Index" = Table.ExpandTableColumn(#"Merged Queries", "Added Index", {"Attribute", "Value"}, {"Attribute.1", "Value.1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index",{"Index"}) in #"Removed Columns"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
