Forum Discussion
How to use COUNTIF in Power Query.
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!
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!
1 Reply
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!