Forum Discussion

united2win's avatar
united2win
Icon for Helper III rankHelper III
7 years ago
Solved

COUNTIF or AGGREGATE Merge in Power Query

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-Au...
Need Help
Show and Tell
Tips and Tricks
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    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"

     

Cmcmahan's avatar
Cmcmahan
Icon for Resident Rockstar rankResident Rockstar
7 years ago

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's avatar
    Cmcmahan
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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"