Log In
Register
Home
New Member
June 20, 2017
Offline
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 |
July 16, 2010
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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"
1 Guest(s)
