Unpivot Multiple pairs of columns in Power Query

[notfred87]

Hi all, I am new to Power Query/Power BI. I have a table that I need to unpivot multiple columns into 3 sets of pairs all at once. I used List.Zip({}) and it works perfectly for unpivoting Product columns and Type Columns. However it only supports 2 lists and I can't do the Category columns alongside the other two columns sets. I need to transpose Product, Type and Category Columns at once keeping their positions relative to other columns in the table. Quick note, "Type" columns are INT and will have many NULLs and I need to preserve them. The NULLs in Category column can be replaced with dummy values if needed. 

I have included my List.Zip code, current table layout and desired table layout. Thank you very much in advance for any help.

List.Zip code example (there are actually 11 of the columns per set that need transposed. I didn't include all in my table example below to save space):

List.Zip( {
{"Product1","Product2","Product3","Product4","Product5","Product6","Product7","Product8","Product9","Product10","Product11"},
{[Product1],[Product2],[Product3],[Product4],[Product5],[Product6],[Product7],[Product8],[Product9],[Product10],[Product11]},
{"Type1","Type2","Type3","Type4","Type5","Type6","Type7","Type8","Type9","Type10","Type11"},
{[Type1],[Type2],[Type3],[Type4],[Type5],[Type6],[Type7],[Type8],[Type9],[Type10],[Type11]}
} )

Table looks like:

Unique_ID

Random 1

Random 2

Random 3

Product 1

Product 2

Product 3

Product 4

Product 5

Product 6

Product 7

Type 1

Type 2

Type 3

Type 4

Type 5

Type 6

Type 7

Category 1

Category 2

Category 3

Category 4

Category 5

Category 6

Category 7

123

R1

R3

R5

Product Value 1

Product Value 2

Product Value 3

Product Value 4

Product Value 5

Product Value 6

Product Value 7

Type Value 1

Type Value 2

Type Value 3

Type Value 4

Type Value 5

Type Value 6

Type Value 7

Category Value 1

Category Value 2

Category Value 3

Category Value 4

Category Value 5

Category Value 6

Category Value 7

456

R2

R4

R6

Product Value 60

Product Value 8

Product Value 9

Product Value 10

Product Value 11

Product Value 12

Product Value 13

Type Value 8

Type Value 9

Type Value 10

Type Value 11

Type Value 12

Type Value 13

Type Value 14

Category Value 8

Category Value 9

Category Value 10

Category Value 11

Category Value 12

Category Value 13

Category Value 14

I need table to look like: 

Unique_ID	Random 1	Random 2	Random 3	Product Category	Product Value	Type Category	Type Value	Category Name	Category Value
123	R1	R3	R5	Product 1	Product Value 1	Type 1	Type Value 1	Category 1	Category Value 1
123	R1	R3	R5	Product 2	Product Value 2	Type 2	Type Value 2	Category 2	Category Value 2
123	R1	R3	R5	Product 3	Product Value 3	Type 3	Type Value 3	Category 3	Category Value 3
123	R1	R3	R5	Product 4	Product Value 4	Type 4	Type Value 4	Category 4	Category Value 4
123	R1	R3	R5	Product 5	Product Value 5	Type 5	Type Value 5	Category 5	Category Value 5
123	R1	R3	R5	Product 6	Product Value 6	Type 6	Type Value 6	Category 6	Category Value 6
123	R1	R3	R5	Product 7	Product Value 7	Type 7	Type Value 7	Category 7	Category Value 7
456	R2	R4	R6	Product 1	Product Value 8	Type 1	Type Value 8	Category 1	Category Value 8
456	R2	R4	R6	Product 2	Product Value 9	Type 2	Type Value 9	Category 2	Category Value 9
456	R2	R4	R6	Product 3	Product Value 10	Type 3	Type Value 10	Category 3	Category Value 10
456	R2	R4	R6	Product 4	Product Value 11	Type 4	Type Value 11	Category 4	Category Value 11
456	R2	R4	R6	Product 5	Product Value 12	Type 5	Type Value 12	Category 5	Category Value 12
456	R2	R4	R6	Product 6	Product Value 13	Type 6	Type Value 13	Category 6	Category Value 13
456	R2	R4	R6	Product 7	Product Value 14	Type 7	Type Value 14	Category 7	Category Value 14

[v-mdharahman]

Hi @notfred87,

Could you please confirm if the issue has been resolved after raising a support case? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

Thank you.

[v-mdharahman]

Hi @notfred87,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[v-mdharahman]

Hi @notfred87,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help?

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[v-mdharahman]

Hi @notfred87,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you want to do some transformation with your columns. Please go through the solution provided by @dufoq3. If the solution solves your issue, please consider it accepting as Solution.

I would also take a moment to personally thank @dufoq3 ,@AlienSx and @Chewdata for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real differenc

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[dufoq3 · Accepted solution]

Hi @notfred87, check this:

Output

I don't know how many columns do you have so you have to edit these 2 steps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZG7DoMwDEV/BWVmiPMC5v5AFVVdEANqUZdKVAgG/p6kU+Lrxco9UezIZxwVGataFSmX/8mnct/W9/Ham+f8PZaGgBggFogDgp0DkC6Rx/lbitFFNHW0dXR19HUMdcyDbvO+fNbtLIYxZBBZRA6RRxQQdWpqR+V8vop5VsytIq4laEA9kAHN4TNCm4Q6ia22r+PALGmWmTZi3nh3EhbYIxoEZVpggkcSRJJgMv1kmi4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Unique_ID = _t, #"Random 1" = _t, #"Random 2" = _t, #"Random 3" = _t, #"Product 1" = _t, #"Product 2" = _t, #"Product 3" = _t, #"Product 4" = _t, #"Product 5" = _t, #"Product 6" = _t, #"Product 7" = _t, #"Type 1 " = _t, #"Type 2" = _t, #"Type 3" = _t, #"Type 4" = _t, #"Type 5" = _t, #"Type 6" = _t, #"Type 7" = _t, #"Category 1" = _t, #"Category 2" = _t, #"Category 3" = _t, #"Category 4" = _t, #"Category 5" = _t, #"Category 6" = _t, #"Category 7" = _t]),
    // Change number of first N columns to preserve.
    ColsToPreserve = List.Buffer(List.FirstN(Table.ColumnNames(Source), 4)),
    // Enter type of columns to unpivot in same order as they are in Source step.
    ColsToUnpivot = List.Buffer({"Product", "Type", "Category"}),
    TransformedData = List.TransformMany(Table.ToRows(Source),
        each {List.Split(List.Skip(_, List.Count(ColsToPreserve)), (List.Count(_) - List.Count(ColsToPreserve)) / List.Count(ColsToUnpivot))},
        (x,y)=> List.Split(List.FirstN(x, List.Count(ColsToPreserve)), 1) & y ),
    TransformedColNames = [ a = List.Skip(Table.ColumnNames(Source), List.Count(ColsToPreserve)),
    b = List.Split(a, List.Count(a)/ List.Count(ColsToUnpivot))
  ][b],
    CombinedData = Table.Combine(List.Transform(TransformedData, each Table.FromColumns(List.FirstN(_, List.Count(ColsToPreserve)) & List.Combine(List.Zip({ TransformedColNames, List.Skip(_, List.Count(ColsToPreserve)) }))))),
    NewColNames = ColsToPreserve & List.Combine(List.Zip({ ColsToUnpivot, List.Transform(ColsToUnpivot, each _ & " Value") })),
    RenamedColumns = Table.RenameColumns(CombinedData, List.Zip({ Table.ColumnNames(CombinedData), NewColNames })),
    FilledDown = Table.FillDown(RenamedColumns, ColsToPreserve)
in
    FilledDown

[notfred87]

@AlienSx, this didn't work, I got the following error: 

"DataFormat.Error: There were more columns in the result than expected.
Details:
Count=10"

I have created a dummy PBIX file with my table but I cannot upload here. What is the best way to share this here? 

[AlienSx]

upoad file to any cloud service available to you and provide a working link here. 

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    headers = List.Buffer(Table.ColumnNames(Source)), 
    first4 = List.FirstN(headers, 4),
    to_unpivot = List.Buffer(List.Skip(headers, 4)), 
    lst = List.TransformMany(
        Table.ToList(Source, each _),
        (x) => List.Transform(List.Zip(List.Split(List.Zip({to_unpivot, List.Skip(x, 4)}), 3)), List.Combine),
        (x, y) => List.FirstN(x, 4) & y
    ), 
    result = Table.FromList(lst, each _, first4 & {"Product Category", "Product Value", "Type Category", "Type Value", "Category Name", "Category Value"})
in
    result

[notfred87]

This didn't work, I got the error: "DataFormat.Error: There were more columns in the result than expected.
Details:
Count=10"

I have created a dummy PBIX file with my source table. What is the best way to upload the file here? 

[notfred87]

This seemed to come close to working with my file but I had an error saying too many columns (there are additional columns in the table). I wasn't able to successfully adapt the code to get it to work. How can I share a pbix or excel file with the exact table I have? 

[Chewdata]

Hey!

You can achieve this by splitting it into three seperate unpivot actions and then combining the three tables into 1:

let
    Source = YOURDATA,
    // Get and unpivot Productdata
    GetProductColumns = Table.SelectColumns(Source,{"Unique_ID", "Product 7", "Product 6", "Product 5", "Product 4", "Product 3", "Product 2", "Product 1"}),
    UnpivotProducts = Table.UnpivotOtherColumns(GetProductColumns, {"Unique_ID"}, "Product", "ProductValue"),
    // Get and unpivot Type data
    GetTypeColumns = Table.SelectColumns(Source,{"Unique_ID", "Type 1 ", "Type 2", "Type 3", "Type 4", "Type 5", "Type 6", "Type 7"}),
    UnpivotType = Table.UnpivotOtherColumns(GetTypeColumns, {"Unique_ID"}, "Type", "Type Value"),
    // Get and unpivot Category data
    GetCategoryColumns = Table.SelectColumns(Source,{"Unique_ID", "Category 1", "Category 2", "Category 3", "Category 4", "Category 5", "Category 6", "Category 7"}),
    UnpivotCategory = Table.UnpivotOtherColumns(GetCategoryColumns, {"Unique_ID"}, "Category", "CategoryValue"),

    // Get the starting columns (ID and Random) and join them with the three seperate queries
    GetFirstColumns = Table.SelectColumns(Source,{"Unique_ID", "Random 1", "Random 2", "Random 3"}),
    leftouter_Products = Table.NestedJoin(GetFirstColumns, {"Unique_ID"}, UnpivotProducts, {"Unique_ID"}, "UnpivotProducts", JoinKind.LeftOuter),
    leftouter_Type = Table.NestedJoin(leftouter_Products, {"Unique_ID"}, UnpivotType, {"Unique_ID"}, "UnpivotType", JoinKind.LeftOuter),
    leftouter_Category = Table.NestedJoin(leftouter_Type, {"Unique_ID"}, UnpivotCategory, {"Unique_ID"}, "UnpivotCategory", JoinKind.LeftOuter),

    // Expand all tables
    #"Expanded UnpivotProducts" = Table.ExpandTableColumn(leftouter_Category, "UnpivotProducts", {"Product", "ProductValue"}, {"Product", "ProductValue"}),
    #"Expanded UnpivotType" = Table.ExpandTableColumn(#"Expanded UnpivotProducts", "UnpivotType", {"Type", "Type Value"}, {"Type", "Type Value"}),
    #"Expanded UnpivotCategory" = Table.ExpandTableColumn(#"Expanded UnpivotType", "UnpivotCategory", {"Category", "CategoryValue"}, {"Category", "CategoryValue"})
in
    #"Expanded UnpivotCategory"

Output:

Hopefully this helps! IF so please consider accepting this as a solution, so other users can quickly find it.