Create Column counting values in ascending order

[AuroraNI]

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 to combine countrows, filter and earliest but haven't quite figured it out. Thanks!

Country	Value
Algeria	1
Belgium	1
Belgium	2
Belgium	3
Canada	1
Canada	2
Canada	3
Canada	4
Chile	1

[v-diye-msft · Accepted solution]

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.

[AuroraNI]

Thanks for this, worked really well!

[Ashish_Mathur]

Hi,

Your question is not clear.  Share 2 seperate tables - input and output.

[AuroraNI]

Hi, 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.

[Vvelarde]

@AuroraNI 

Hi, maybe there are better ways but this is my first way:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixKT83JTFSK1YlWci7KT0yGsgNSi0rBDGQFrq6hoWCGb2pFZnI+qphTUWJxZg5uzXBBDFNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Pais = _t]),


    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pais", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Pais"}, {{"Count", each _, type table [Pais=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Pais", "index"}, {"Custom.Pais", "Custom.index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Pais", "Count"})
in
    #"Removed Columns"

Regards

Victor