Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 unpi...
Need Help
  • liuqi_pbi's avatar
    liuqi_pbi
    4 years ago

    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!