Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm hoping it's not too complicated but can't figure out the steps to go from the below flat table to the following cross table within power query. i'm sure there is an amount of pivoting and/or unpivoting involved but i cannot get the steps right!
edit. to say if unclear 1 = 1 month, 2 = 2 month and 3 = 3 month
any help would be appreciated.
Solved! Go to Solution.
Hi @brownrice
It could be simple. You just need 3 steps.
Step #1 Group by name and month columns and select Count Rows operation on the group.
Step #2 Add suffix " MONTH" to the month column.
Step #3 Pivot the month column and select Count column for values. Select Sum aggregation type on Count.
You will get the expected result as below.
Full code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRMlSK1SHENkJiGxPNdnJygptDPNuIRLYxBtvZ2RluJnlsIxLZxrjZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t, #"Time in Post" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}, {"Time in Post", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"NAME", "Time in Post"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Added Suffix" = Table.TransformColumns(#"Grouped Rows", {{"Time in Post", each Text.From(_, "en-US") & " MONTH", type text}}),
#"Pivoted Column" = Table.Pivot(#"Added Suffix", List.Distinct(#"Added Suffix"[#"Time in Post"]), "Time in Post", "Count", List.Sum)
in
#"Pivoted Column"
Cheers
If this reply helps solve this problem, please mark it as Solution! Kudos are appreciated too!
Hi @brownrice
It could be simple. You just need 3 steps.
Step #1 Group by name and month columns and select Count Rows operation on the group.
Step #2 Add suffix " MONTH" to the month column.
Step #3 Pivot the month column and select Count column for values. Select Sum aggregation type on Count.
You will get the expected result as below.
Full code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRMlSK1SHENkJiGxPNdnJygptDPNuIRLYxBtvZ2RluJnlsIxLZxrjZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t, #"Time in Post" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}, {"Time in Post", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"NAME", "Time in Post"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Added Suffix" = Table.TransformColumns(#"Grouped Rows", {{"Time in Post", each Text.From(_, "en-US") & " MONTH", type text}}),
#"Pivoted Column" = Table.Pivot(#"Added Suffix", List.Distinct(#"Added Suffix"[#"Time in Post"]), "Time in Post", "Count", List.Sum)
in
#"Pivoted Column"
Cheers
If this reply helps solve this problem, please mark it as Solution! Kudos are appreciated too!
Please provide sanitized sample data that fully covers your issue.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |