Count over rows of the same value

[anbeegod]

Hi,

The issue is a bit complicated, so I'll illustrate with a table.

Basically, I want to create the "times" column in Power Query.

I've tried googling it but the solutions were mostly in Dax, instead of M which is used by Power Query.

I want to calculate how many times a country has participated over the years.

Thank you

[v-yiruan-msft · Accepted solution]

Hi @anbeegod ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can achieve it in Power Query Editor, copy the below applied codes in your Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFDSUXLOyMxLBNKRqcVKsTpgYUPswkZIwjAxYyxiJljEQDa5pxblJuZVYtqFVcIIl4QxigSypZiiprgMAbknNNgR0y0YgkbYBI2xCZrABZGtx1Bmhk3QHElvLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Country = _t, Participated = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Country", type text}, {"Participated", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Participated] = "Yes" then 1 else null),
      #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Country"}, {{"Times", each List.Sum([Custom]), type nullable number}, {"Details", each _, type table [Year=nullable number, Country=nullable text, Participated=nullable text, Custom=nullable number]}}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"Year", "Participated"}, {"Year", "Participated"})
in
    #"Expanded Details"

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak]

@anbeegod , In power query you have to duplicate the table, Filter, yes, and then group by using country and then join it back with your table using merge