Advanced unpivot data query

[han_rj]

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

[SundarRaj]

Here's the solution to your query. It might be a long solution but do have a look.

[Akash_Varuna]

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

[han_rj]

Hi Akash, Thanks For the Respose, Please may I have help on the updated request

[Akash_Varuna]

Could You specify ?

[han_rj]

I would like to get an output like this with Year , Sales and Unit sales like this

[AlienSx · Accepted solution]

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

[han_rj]

Thank You @AlienSx , This was  very helpful

[dufoq3]

Hi @han_rj, if the bottom table is your expected result - it does not make sense for me.