Forum Discussion
AuroraNI
Helper III
Helper IIICreate Column counting values in ascending order
Hi, Was hoping people could help. I would like to add a column in Query editor counting the number of times a value appears in a column in ascending order (see below desired output). I have tried ...
Hi AuroraNI
Add the index column first, and then add the calculated column:
Column = CALCULATE(DISTINCTCOUNT('Table'[Index]),FILTER('Table',[Country]=EARLIER('Table'[Country])&&[Index]<=EARLIER('Table'[Index])))Pbix attached.
Ashish_Mathur
Super User
Super UserHi,
Your question is not clear. Share 2 seperate tables - input and output.
AuroraNIHelper III
Helper IIIHi, apologies here is the input column
Country |
| Algeria |
| Belgium |
| Belgium |
| Belgium |
| Canada |
| Canada |
| Canada |
| Canada |
| Chile |
and here is the output I would like
Country | Value |
| Algeria | 1 |
| Belgium | 1 |
| Belgium | 2 |
| Belgium | 3 |
| Canada | 1 |
| Canada | 2 |
| Canada | 3 |
| Canada | 4 |
| Chile | 1 |
- Ashish_Mathur
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}}), Partition = Table.Group(#"Changed Type", {"Country"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index"}, {"Index"}) in #"Expanded Partition"Hope this helps.
- AuroraNI
Thanks for this answer, I have tried this and works thank you. I will go with the calculated column option as simpler in my current dashboard
- Ashish_Mathur
You are welcome.