How to use COUNTIF in Power Query.

[ADV__ROHIT]

Hi everyone,

I need some help with Power Query. I'm trying to create a new column that indicates if a transaction in my data is the first occurrence or not. In Excel, I achieved this with the following formula:
"=IF(COUNTIF($A$2:A2,A2)=1,1,0)"
This formula checks if the transaction is the first occurrence (returns 1) or not (returns 0). I want to replicate this functionality in Power Query. My project is currently stuck because I can't figure out how to do this. Any guidance would be greatly appreciated!

[PwerQueryKees · Accepted solution]

Start Table

The Code, I made some internmediate columns for clarity. You can combine the formulas or remove the columns as you like:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Duplicates" = Table.AddColumn(#"Added Index", "Custom", each Table.SelectRows(#"Added Index", (T) => T[Transaction] = [Transaction] and T[Index] <= [Index])),
    #"Added IsFirst" = Table.AddColumn(#"Added Duplicates", "isFirst", each Table.RowCount([Custom])= 1)
in
    #"Added IsFirst"

Result:

Result sorted by Transaction for easy checking:

You're welcome!