Forum Discussion

mathias1998's avatar
mathias1998
Frequent Visitor
3 years ago
Solved

New columns based on count of another column

Hi, 

 

I currently have the following column in a table: 

 

 

I have created a new table, and in that I would like to create new columns that looks like this: 

 

 

I.e. essentially what I need is a Power Query function that counts each occurence in the Status column, and creates a new column for each status with the count of each status. 

Need Help
Show and Tell
  • mussaenda's avatar
    mussaenda
    3 years ago

    try this, open a blank query, open advanced editor, select all, then paste this code.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUxiigNFYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Status"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Status]), "Status", "Count")
in
    #"Pivoted Column"

     

    Hope this helps.

2 Replies

  • mussaenda's avatar
    mussaenda
    Icon for Community Champion rankCommunity Champion
    3 years ago

    try this, open a blank query, open advanced editor, select all, then paste this code.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUxiigNFYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Status"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Status]), "Status", "Count")
in
    #"Pivoted Column"

     

    Hope this helps.