Custom Column to count how many times the ID with the same date appears

[Katiek]

Hi

I'm new to Power Query and Power BI. 

I'm trying to figure out in my table (Controldata2023tbl) how many times the ID shows up with the same Date. This is an example with 2 criteria, sometimes i have 3 criteria. In excel I used to do this with the Countifs formula, I would appreciate any help with how to do this in Power Query.

Many thanks in advance 🙂

Date               ID   Count

26.07.2023	h11	1
26.07.2023	h11	2
27.07.2023	h11	1
28.07.2023	h33	1
28.07.2023	h33	2
28.07.2023	h33	3

[Anonymous · Accepted solution]

Hi @Katiek ,

1. add a index column:

2. add a custom column:

Output:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLTMzDXMzIwMlbSUTI0VIrVwSFmjkXMAknM2Jh4sVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"ID", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Count", each Table.RowCount(Table.SelectRows(#"Added Index", (x)=>x[Date]=[Date] and x[ID]=[ID] and x[Index]<=[Index]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Katiek]

Hi @Anonymous 

Thank you, you are a legend! It worked!!!

Thanks so much!

[Katiek]

Hi @Anonymous 

Thanks so much for your solution. I'm going to give it a go now!

[Syndicate_Admin]

Hi,

 One solution would be to duplicate the first Query, group by criteria with a count then merge back in to original data...

There probably is a clever way to do this in a single step but unsure how that would affect performance. This way you can add as many criteria as you like as well.

Thanks,

[Katiek]

Hi @Syndicate_Admin 

Thank you for your suggestion. 

[Katiek]

Sorry my example didn't upload properly. There are 3 columns Date, ID and count