Forum Discussion
Solved
Advanced unpivot data query
Hi Team, I have a scenario where I need to flatten/unpivot Orders and Unit Price columns as Years,Orders,Unit price columns. Please may I have help know how this can be achieved in power query ...
let Source = Excel.CurrentWorkbook(){[Name="data"]}[Content], rows = List.Buffer(Table.ToList(Source, each _)), years = List.Buffer(List.Range(rows{1}, 3, 3)), trx = List.TransformMany( List.Skip(rows, 2), (x) => List.Zip({years, List.Range(x, 3, 3), List.Range(x, 7, 3)}), (x, y) => List.FirstN(x, 3) & {x{6}} & {x{10}} & y ), result = Table.FromList(trx, each _, {"Product", "Prod Cat", "Prod Code", "Sales", "Inv", "Year", "Order", "Unit Price"}) in result
Hi han_rj , Could you try these
Unpivot Columns: Select year-based columns, right-click, and choose unpivot columns.
Combine Name & Year: Add a custom column with this formula:
Text.BeforeDelimiter([Attribute], " ") & "_" & Text.AfterDelimiter([Attribute], " ")
Rename & Cleanup: Rename columns as needed and remove unnecessary columns.
If this post helped please do give a kudos and accept this as a solution
Thanks In Advance
Hi Akash, Thanks For the Respose, Please may I have help on the updated request
Could You specify ?
I would like to get an output like this with Year , Sales and Unit sales like this
let Source = Excel.CurrentWorkbook(){[Name="data"]}[Content], rows = List.Buffer(Table.ToList(Source, each _)), years = List.Buffer(List.Range(rows{1}, 3, 3)), trx = List.TransformMany( List.Skip(rows, 2), (x) => List.Zip({years, List.Range(x, 3, 3), List.Range(x, 7, 3)}), (x, y) => List.FirstN(x, 3) & {x{6}} & {x{10}} & y ), result = Table.FromList(trx, each _, {"Product", "Prod Cat", "Prod Code", "Sales", "Inv", "Year", "Order", "Unit Price"}) in result