Flat table to aggregated cross tab in Power Query

[Anonymous]

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.

[liuqi_pbi · Accepted solution]

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. 

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!

[lbendlin]

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