COUNTIF or AGGREGATE Merge in Power Query

[united2win]

Hi, 

I have two tables (Snippet Below):

Sales and Bookings 2019:

Unique Key:

4333-Austria-VAT Return

7384-Germany-VAT Return

WorkflowData

Unique Key:

4333-Austria-VAT Return

4333-Austria-VAT Return

7384-Germany-VAT Return

4333-Austria-VAT Return

4333-Austria-VAT Return

4333-Austria-VAT Return

7384-Germany-VAT Return

...

My goal is to create a new column on the Sales and Bookings 2019 table to count the number of times that unique key is matched in the Workflow Data. I tried using an aggregate merge I eventually cancelled this applied step as it was taking too long. The number of rows on the Workflow table is 150,000+

i.e.

Sales and Bookings 2019:

Unique Key:                                          ReturnCount

4333-Austria-VAT Return                           5

7384-Germany-VAT Return                        2

[Cmcmahan]

Before creating this as a calculated column, I would take a step back and ask why you need to store the info in a table instead of counting it on the fly.  PowerBI includes count as one of the default aggregations. I literally just added the data you provided and made sure the tables were related by the Key, and then dragged them into this table visual and set the Workflow Key to count.

[Cmcmahan · Accepted solution]

If you absolutely, for sure, no question need this as a calculated DAX column, there are more efficient ways than using MAXX and COUNTX.

ReturnCount = COUNTROWS( FILTER( WorkflowData, WorkflowData[Non-Unique Key] = [Unqiue Key]))

You can also do this in power query, if you don't want to load in all those rows in the first place.  The lines in red are where the joining happens.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE2NtZ1LC0uKcpMVIrViVYyN7Yw0XVPLcpNzKtUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Key = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Key", "Unique Key"}}),

    #"CountedKeys" = Table.Group(WorkflowData, {"Non-Unique Key"}, {{"ReturnCount", each Table.RowCount(_), type number}}),
    #"Merge Tables" = Table.Join(#"Renamed Columns", "Unique Key", #"CountedKeys", "Non-Unique Key")
in
    #"Merge Tables"

[amitchandak]

Try

Countx(filter(WorkflowData,'Sales and Bookings 2019'[Unique Key] =WorkflowData[Unique Key]),WorkflowData[Unique Key]) 

Edited 

[united2win]

Hi Amit,

Thank you for your reply. Unfortunately, doesn't seem to work. The filter function does not have enough arguments. 

Alex

[amitchandak]

I think some copy paste error while I replace in my formula 

Maxx(filter(sales,Sales[item_id]='Item'[Item ID]),Sales[Order Id]) 

CountX(filter('Other table','Other table'[id]='Self'[ID]),'Other table[Order Id])