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
