Unpivot Multiple pairs of columns in Power Query

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.

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.

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

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? 

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.

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.

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.