Help with Transpose/UnPivot table in power BI

[phaneendra]

Hi Everyone,

I need your support in resolving an issue I am currently facing in Power BI

I have a sample data table as mentioned below

Emp ID	Designation	Location	Jan_HC	Jan_AC	Feb_HC	Feb_AC	Mar_HC	Mar_AC	Apr_HC	Apr_AC	May_HC	May_AC	Jun_HC	Jun_AC	Jul_HC	Jul_AC	Aug_HC	Aug_AC	Sep_HC	Sep_AC	Oct_HC	Oct_AC	Nov_HC	Nov_AC	Dec_HC	Dec_AC
123456789	Manager	India	1	0	1	0	1	0	1	0	1	0	0.7	0	1	0	0.2	0	1	0	1	0	1	0	1	0
234567890	Assistant Manager	India	1	0	1	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
234567890	Manager	USA	0	0	0	0	0	0	1	0	1	0	1	0	0.5	0	0.5	0	0.5	0	0.5	0	0.5	0	0	0
234567890	Manager	Europe	0	0	0	0	0	0	0	0	0	0	0	0	0.5	0	0.5	0	0.5	0	0.5	0	0.5	0	1	0

HC represents Heacount & AC represents Attrition count

For every user we have 12 months of Headcount & Attrition data

The desired output is something like this in Power BI. preferably using Power Query transformations.

Emp ID	Designation	Location	Month	HC	AC
123456789	Manager	India	Jan	1	0
123456789	Manager	India	Feb	1	0
123456789	Manager	India	Mar	1	0
123456789	Manager	India	Apr	1	0
123456789	Manager	India	May	1	0
123456789	Manager	India	Jun	0.7	0
123456789	Manager	India	Jul	1	0
123456789	Manager	India	Aug	0.2	0
123456789	Manager	India	Sep	1	0
123456789	Manager	India	Oct	1	0
123456789	Manager	India	Nov	1	0
123456789	Manager	India	Dec	1	0
234567890	Assistant Manager	India	Jan	1	0
234567890	Assistant Manager	India	Feb	1	0
234567890	Assistant Manager	India	Mar	1	0
234567890	Assistant Manager	India	Apr	0	0
234567890	Assistant Manager	India	May	0	0
234567890	Assistant Manager	India	Jun	0	0
234567890	Assistant Manager	India	Jul	0	0
234567890	Assistant Manager	India	Aug	0	0
234567890	Assistant Manager	India	Sep	0	0
234567890	Assistant Manager	India	Oct	0	0
234567890	Assistant Manager	India	Nov	0	0
234567890	Assistant Manager	India	Dec	0	0
234567890	Manager	USA	Jan	0	0
234567890	Manager	USA	Feb	0	0
234567890	Manager	USA	Mar	0	0
234567890	Manager	USA	Apr	1	0
234567890	Manager	USA	May	1	0
234567890	Manager	USA	Jun	1	0
234567890	Manager	USA	Jul	0.5	0
234567890	Manager	USA	Aug	0.5	0
234567890	Manager	USA	Sep	0.5	0
234567890	Manager	USA	Oct	0.5	0
234567890	Manager	USA	Nov	0.5	0
234567890	Manager	USA	Dec	0	0
234567890	Manager	Europe	Jan	0	0
234567890	Manager	Europe	Feb	0	0
234567890	Manager	Europe	Mar	0	0
234567890	Manager	Europe	Apr	0	0
234567890	Manager	Europe	May	0	0
234567890	Manager	Europe	Jun	0	0
234567890	Manager	Europe	Jul	0.5	0
234567890	Manager	Europe	Aug	0.5	0
234567890	Manager	Europe	Sep	0.5	0
234567890	Manager	Europe	Oct	0.5	0
234567890	Manager	Europe	Nov	0.5	0
234567890	Manager	Europe	Dec	1	0

It would be highly appreciated if any one could provide a feasible solution for this issue

Thanks in advance.

Regards,

Phani

[Daryl-Lynch-Bzy · Accepted solution]

Hi @phaneendra  - please try video like the following to learn how to pivot and unpivot data.  How and why to Unpivot data with Power Query  

[v-yiruan-msft · Accepted solution]

Hi @phaneendra ,

You can follow the below steps to get it in Power Query Editor, please find the details in the attachment.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1M7ewVNJR8k3MS0xPLQKyPPNSMhOBtCEQGxBBG+iZY4gYEaU3VidaCeYEkKhjcXFmcUliXokC8a6hDkZ3CcL+0GBHPDpxh4kpqSwC7nAtLcovSCXHcyQ7BRo5sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Emp ID" = _t, Designation = _t, Location = _t, Jan_HC = _t, Jan_AC = _t, Feb_HC = _t, Feb_AC = _t, Mar_HC = _t, Mar_AC = _t, Apr_HC = _t, Apr_AC = _t, May_HC = _t, May_AC = _t, Jun_HC = _t, Jun_AC = _t, Jul_HC = _t, Jul_AC = _t, Aug_HC = _t, Aug_AC = _t, Sep_HC = _t, Sep_AC = _t, Oct_HC = _t, Oct_AC = _t, Nov_HC = _t, Nov_AC = _t, Dec_HC = _t, Dec_AC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp ID", Int64.Type}, {"Designation", type text}, {"Location", type text}, {"Jan_HC", Int64.Type}, {"Jan_AC", Int64.Type}, {"Feb_HC", Int64.Type}, {"Feb_AC", Int64.Type}, {"Mar_HC", Int64.Type}, {"Mar_AC", Int64.Type}, {"Apr_HC", Int64.Type}, {"Apr_AC", Int64.Type}, {"May_HC", Int64.Type}, {"May_AC", Int64.Type}, {"Jun_HC", type number}, {"Jun_AC", Int64.Type}, {"Jul_HC", type number}, {"Jul_AC", Int64.Type}, {"Aug_HC", type number}, {"Aug_AC", Int64.Type}, {"Sep_HC", type number}, {"Sep_AC", Int64.Type}, {"Oct_HC", type number}, {"Oct_AC", Int64.Type}, {"Nov_HC", type number}, {"Nov_AC", Int64.Type}, {"Dec_HC", Int64.Type}, {"Dec_AC", Int64.Type}}),
    #"Reversed Rows" = Table.ReverseRows(#"Changed Type"),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Reversed Rows", {"Dec_AC", "Dec_HC", "Nov_AC", "Nov_HC", "Oct_AC", "Oct_HC", "Sep_AC", "Sep_HC", "Aug_AC", "Aug_HC", "Jul_AC", "Jul_HC", "Jun_AC", "Jun_HC", "May_AC", "May_HC", "Apr_AC", "Apr_HC", "Mar_AC", "Mar_HC", "Feb_AC", "Feb_HC", "Jan_AC", "Jan_HC"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Only Selected Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Month", "Type"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Type]), "Type", "Value", List.Sum)
in
    #"Pivoted Column"

Best Regards

[phaneendra]

Thank you so much for the support!!!

Highly Appreciated!!!!👍

[Daryl-Lynch-Bzy · Accepted solution]

Hi @phaneendra  - please try video like the following to learn how to pivot and unpivot data.  How and why to Unpivot data with Power Query