Unpivot - Help Required

[sedu1110]

Hi ,

I am very new to power query . I have a raw set of data . In excel I use a three way look up to get the required answer. The attached input file and the related output file after using formula will give you an idea of what I do. Since my actual data set is very large, the formulas take a very long time to process.

I also need to make this report twice a week. I would require help on doing the same in Power query.

I have tried using Unpivot . But I believe I am doing something wrong as I do not get my intended results.
I request guidance on how can I do the same in Power Query.

 

[lrobbo314]

This seems to do the trick.

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Candidate ID", Int64.Type}, {"Name", type text}, {"Stage", type text}, {"Stage ID", type date}}),
    Pivot = Table.Pivot(Type, List.Distinct(Type[Stage]), "Stage", "Stage ID")
in
    Pivot

 

[sedu1110]

This seems to do the trick.

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Candidate ID", Int64.Type}, {"Name", type text}, {"Stage", type text}, {"Stage ID", type date}}),
    Pivot = Table.Pivot(Type, List.Distinct(Type[Stage]), "Stage", "Stage ID")
in
    Pivot

The code you provided is truly beneficial for me.
Thank you for your support.

 

[nikio8]

you could not use normal pivot, with stage name in top right box (headings) ?