power query - M Language - Convert Columns Into Rows

0
votes

I have a spreadsheet that contains column Names as the product name, quantity, cost.

I want to convert this to rows of data that contain Product Name, Quantity, Cost.

See image below as to what I want.

enter image description here

What is the best way to handle this in Power Query M Language?

Not sure if I want to pivot just the columns that have prod name, quantity and cost?

Thanks

2
powerbipowerquerym

2 Answers

0
votes

Here's A way...

Starting with this table as Table1:

enter image description here

You can select the Customer column and Unpivot Other Columns to get this:

enter image description here

Then you can add an index column (keep it named Index) and then also a custom column (keep it named Custom) with if Text.EndsWith([Attribute],"Cost") then 1 else 0 as its formula to get this:

enter image description here

Then add another custom column... Name it Total Cost and enter #"Unpivoted Other Columns"[Value]{[Index]+(List.Count(#"Added Custom"[Custom])/List.Sum(#"Added Custom"[Custom]))} as its formula to get:

enter image description here

The two steps above were, first, to set up to locate the corresponding Cost of the Tshirts based on the Cost's position in the Value column and, then, to actually locate the cost and record it on the same line as the respective Tshirts. The Index column provides row positioning information while the Custom column provides count information--both the overall list count and the count of rows with Cost. I use the count information to determine how many index positions to move down the Value column to get associated cost values dynamically.

Then filter on the Attribute column, using Text Filters > Does Not End With... and type the word Cost. All the rows with an Attribute entry ending with the word Cost should disappear:

enter image description here

Remove the Index and Custom columns and Rename the Attribute and Value columns to Product Name and Quantity, respectively to get your final result:

enter image description here

Here's my M code:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer"}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Text.EndsWith([Attribute],"Cost") then 1 else 0),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Total Cost", each #"Unpivoted Other Columns"[Value]{[Index]+(List.Count(#"Added Custom"[Custom])/List.Sum(#"Added Custom"[Custom]))}),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each not Text.EndsWith([Attribute], "Cost")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Product Name"}, {"Value", "Quantity"}})
in
#"Renamed Columns"
0
votes

They key here is pivoting and unpivoting.

Starting with a table like this,

Start

Select the right four columns and click Transform > Unpivot Columns to get this table:

Unpivoted

Now create a custom column that classifies the value using this formula.

if Text.EndsWith([Attribute], "Cost") then "Cost" else "Quantity"

I also chopped off the " Cost" piece at the end of the Attribute column. You can either Transform > Replace Values and replace " Cost" with nothing or Transform > Extract > Text Before Delimiter " Cost".

Custom Column

Now pivot the custom column (choose the Value column as your Values Column choice) and, finally, rename the Attribute column to Product Name.

Pivoted

Here's my M code for all the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcknNK0stUnAuLS7Jz00tUtJRMjIGEoYmIJapqZ6pAYhnZKpnYKAUqxOt5JyRmZyYno+swdAQSJiagtUZgNQBeeYQDbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Product Orange T-shirt" = _t, #"Product Blue T-shirt" = _t, #"Product Orange T-shirt Cost" = _t, #"Product Blue T-shirt Cost" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Product Orange T-shirt", Int64.Type}, {"Product Blue T-shirt", Int64.Type}, {"Product Orange T-shirt Cost", type number}, {"Product Blue T-shirt Cost", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if Text.EndsWith([Attribute], "Cost") then "Cost" else "Quantity"),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom"," Cost","",Replacer.ReplaceText,{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Custom]), "Custom", "Value", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "Product Name"}})
in
    #"Renamed Columns"