Unpivot multiple parameter columns

Power Query
1

Hi Query Editor experts
I’ve got a tricky situation where I need to transform a Purchase Order (PO) dataset from columns to rows accordingly. Each order has multiple lines but presented in column format for multiple parameters such as Date, Quantity and Amount. Below is a snip of the sample data:

image
image.png994×78 3.35 KB

The desired result is as per below:

image

At the first glance I thought this was a simple Unpivot step for each measure. However, this thought appears incorrect as after the second round of unpivot for the second measure it duplicates all the lines.

Could you please help on this issue? Attached is the sample file.

Thank you.

Regards.

Unpivot Multiple Parameter Columns - PO Example.xlsx (14.2 KB)

2

Hi kbi,

Check this out and let me know if this one work out.

Chrs,
Qasim

3

Hi @kbi,

Welcome back! I unpivoted your data, split the Attribute by the right most delimiter and then pivoted your data back again… Here’s the full query.

let
    Source = Excel.Workbook(File.Contents(FileLocation), null, true),
    #"Raw Data_Sheet" = Source{[Item="Raw Data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Raw Data_Sheet", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([PO Number] <> null)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"PO Number"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.1]), "Attribute.1", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.2", "Line"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Delivery Date Line", type date}, {"Qty Line", type number}, {"Amount Line", Currency.Type}, {"Line", Int64.Type}})
in
    #"Changed Type"

.
With this result:

image
image.png837×165 5.3 KB

I hope this is helpful.
eDNA - Unpivot multiple parameter columns.pbix (25.6 KB)

2 Likes
4

This is great! Thanks for your help! I have rarely used “pivot” function before hence it did not automatically click in my mind. Got too used to the other way around “unpivot” thing. Thanks so much for your help!

5

This solution is similar to the one provided by Melissa below, so yes it will work! Thanks for your help! :slight_smile: