UnPivot twice in same tabel. How can I do that???

[Anonymous]

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? 

[v-xinruzhu-msft · Accepted solution]

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.

[lbendlin]

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

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/1447...