Flat table to aggregated cross tab in Power Query

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!