Forum Discussion

Cihan_G's avatar
Cihan_G
New Member
5 years ago
Solved

Count rows with conditions in Power Query

Hey Guys,   I need help counting the occurance of a value within a specific key value. The data I have looks something like this: Key Parameter within key Value A aaa 1 A aab 2 ...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi Cihan_G 

    Download this sample PBIX with code

    Use this in Power Query

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUpMTASShkqxOjB+EpA0QuIno/FTgKQJmO8EZCUlIfRD+Aj9ED5IvzESH6o/FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, #"Parameter within key" = _t, Value = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Value] = "2")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Key"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

     

    Regards

    Phil

Cihan_G's avatar
Cihan_G
New Member
5 years ago

Hey Phil,

 

thank you for your fast reply! 

This worked so far, but how can I now add more columns, based on other filters to that?

 

Key   //   # of 2's  //  # of 3's and '4s

A      //       2        //            1

B      //       1        //            2

 

Can you please help me out with this aswell?

 

Thank you very much,

Cihan