The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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
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
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
Thank you so much for the support!!!
Highly Appreciated!!!!👍
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
76 | |
66 | |
52 | |
50 |
User | Count |
---|---|
121 | |
118 | |
77 | |
64 | |
63 |