Loop through Matrix table and store in new sheets of column table [duplicate]

Here’s the Powerquery solution in case you find it easier than the VBA one in the comments. (SO is detecting the instructions as code, even though they aren’t)

Make sure every column has a title>highlight your data>insert>add table
Data>from table/range
Select product Name>right click>unpivot other columns
Filter out N/A
Rename columns/arrange order
Add column>duplicate product name and price book
Merge new columns/rename
save&load

Before/After enter image description here

Code (can be copied into view>advanced editor. Be sure to leave source as whatever your source is)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Name", type text}, {"China Price Book", type text}, {"US Price Book", Int64.Type}, {"UK Price Book", Int64.Type}, {"SG Price Book", Int64.Type}, {"JP Price Book", Int64.Type}, {"Standard Price book", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "N/A")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Price Book"}, {"Value", "List Price"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Product Name", "List Price", "Price Book"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns", "Product Name", "Product Name - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Price Book", "Price Book - Copy"),
    #"Merged Columns" = Table.CombineColumns(#"Duplicated Column1",{"Product Name - Copy", "Price Book - Copy"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Renamed Columns1" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Product Key"}})
in
    #"Renamed Columns1"

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top