How to do an accumulative count in Power Query just like we do in excel to create unique values with this formula Countif(a$1:a1,a1).
Please also note that it will be applied on actual data which is arround 20 column and 400k rows.
| Name | Count |
| Ahmed | 1 |
| Ali | 1 |
| Wahab | 1 |
| Rehan | 1 |
| Ahmed | 2 |
| Ali | 2 |
| Rehan | 2 |
| Wahab | 2 |
| Ahmed | 3 |
| Ahmed | 4 |
| Junaid | 1 |
Hi Muhammad,
Power Query isn't designed to be the tool for aggregations like this. Power Query is for getting and cleaning your data ready for loading into the Excel worksheet or Power Pivot model where you do your analysis like this.
This task is best done in a PivotTable or a Power Pivot PivotTable using a DAX measure.
Mynda
Hi Muhammad,
It can be done, but as Mynda said, it's best to do it in Power Pivot, because Power Query is not as fast.
Here is a query you can try, it will do that countif:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let Name=[Name], Index=[Index] in
List.Count(Table.SelectRows(#"Added Index", each [Index]<=Index and [Name]=Name)[Name]))
in
#"Added Custom"
