Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Flat table to aggregated cross tab in Power Query

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.

brownrice_0-1659706604457.png

brownrice_1-1659706615467.png

 

1 ACCEPTED SOLUTION
liuqi_pbi
Resolver II
Resolver II

Hi @Anonymous 

 

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. 

liuqi_pbi_0-1660037625394.pngliuqi_pbi_1-1660037650378.png

 

Step #2 Add suffix " MONTH" to the month column. 

liuqi_pbi_2-1660037725465.png

 

Step #3 Pivot the month column and select Count column for values. Select Sum aggregation type on Count. 

liuqi_pbi_4-1660037837338.png

You will get the expected result as below. 

liuqi_pbi_5-1660037861356.png

 

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!

View solution in original post

2 REPLIES 2
liuqi_pbi
Resolver II
Resolver II

Hi @Anonymous 

 

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. 

liuqi_pbi_0-1660037625394.pngliuqi_pbi_1-1660037650378.png

 

Step #2 Add suffix " MONTH" to the month column. 

liuqi_pbi_2-1660037725465.png

 

Step #3 Pivot the month column and select Count column for values. Select Sum aggregation type on Count. 

liuqi_pbi_4-1660037837338.png

You will get the expected result as below. 

liuqi_pbi_5-1660037861356.png

 

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!

lbendlin
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.