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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Cihan_G ,

 

you could follow these steps:

duplicate the table --> Filter and Groupby respectively--> finally merge the two tables as shown below:

 

But it will lead to an increase in memory/cpu to a certain extent since I have create a new table.And normally memory consumption will reduce the performance and brings heavier load to the model. 

 

So you could disable "Enable load" option.

 

Please take a look at the pbix file here.

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.