Power Query Pivot/Unpivot Columns Help

[Russty99]

Hi Team,

I have data that comes in the following format:

I want to change the attribute column to only show the product name (e.g. XP1 or XP2) and then I want additional columns, one for quantity and one for price as so:

is this possible in power query?

I have played around with pivoting and unpivoting columns but have not had much luck.

thanks for any help!

[v-cgao-msft · Accepted solution]

Hi @Russty99 ,

Please try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldBLCoAwDATQq0jXQjtTm6qn0J0g3v8apvWDggsDhZSQx8Csq4NrHQPpoa9rwJEcIcc2engG/c/NMtVLt7V/zXQaICcDK1HUGY1RxYAyGKOizs4YVQyS5Mr4xVK4WPTsHwWKwdwF9tnC3gX+jzouA4xRtQxjVDWSlW07", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Completion time" = _t, #"Please input indicative prject completion date" = _t, Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Completion time", type datetime}, {"Please input indicative prject completion date", type date}, {"Attribute", type text}, {"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute]), "Attribute", "Value", List.Sum),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column", {"ID", "Completion time", "Please input indicative prject completion date", "P XP1", "P XP2", "P XP3"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Attribute] = "Q XP1" then [P XP1] else if [Attribute] = "Q XP2" then [P XP2] else if [Attribute] = "Q XP3" then [P XP3] else null),
    #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Attribute", "Product"}, {"Value", "Quailty"}, {"Custom", "Price"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"P XP1", "P XP2", "P XP3"})
in
    #"Removed Columns"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[HotChilli]

It looks like the text in attribute.2 has extra space and characters in some entries so, before the Pivot, use Trim or Clean (or both) on that column.  All available in the interface from the column header (right-click ->Transform)

[HotChilli]

It is a Pivot to get where you want but you have to split the Attribute Column first.

You need to get the Price/Quantity text into one column then Pivot that column. 

[Russty99]

Hi @HotChilli 

That has definitely got me closer to where i need to be, thank you! Please see how it now looks:

Now how do I blend the rows so that each row has a value for quantity and price, leaving no null rows.

Thanks,

Russell