Log In
Register
Home
New Member
July 9, 2019
Offline
Hi,
I am dealing with a table that is challenging to do any look ups. I have explored the Unpivot option in Power Query, but this isn't providing the desired outcome (or I'm doing it wrong).
Below is a sample of my source data:
| CustomerIdName | kd_product01idName | KD_Qty01 | KD_Price01 | kd_product02idName | KD_Qty02 | KD_Price02 | kd_product03idName | KD_Qty03 | KD_Price03 |
| A | XYZ | 5 | 1 | XXX | 1 | 1 | |||
| B | XYZ | 5 | 1 | Y2K | 3 | 1 | 0 | ||
| C | Y2K | 5 | 1 | 0 | 0 |
Below is desired output:
| Product | Quantity | Price | |
| A | XYZ | 5 | 1 |
| A | XXX | 1 | 1 |
| B | XYZ | 5 | 1 |
| B | XXX | 3 | 1 |
| C | XYZ | 5 | 1 |
So, essentially, we want to unpivot each product, quantity and price and keep them together in a row, replicating all other columns except the ones we are unpivoting.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Online
Hi James,
It is possible, but there are some inconsistencies in your data. If product name is null, then qty and price should be null in all sections. If in your data you might have zero's instead of nulls, you will need to add a filter after unpivot, to filter out 0 values. See the file attached and the query below.
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"CustomerIdName"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Text.Contains([Attribute],"Name") then "Name" else if Text.Contains([Attribute],"Qty") then "Qty" else if Text.Contains([Attribute],"Price") then "Price" else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom.1", each if Text.Contains([Custom],"Name") then [CustomerIdName] & "|" & [Value] else null),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom.1"}),
#"Removed Columns1" = Table.RemoveColumns(#"Filled Down",{"CustomerIdName"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Custom.1", "Value", "Custom"}),
#"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Custom]), "Custom", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Custom.1", "CustomerName"}}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"CustomerName", each Text.BeforeDelimiter(_, "|"), type text}})
in
#"Extracted Text Before Delimiter"
1 Guest(s)
