Forum Discussion

AuroraNI's avatar
AuroraNI
Icon for Helper III rankHelper III
6 years ago
Solved

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

CountryValue
Algeria1
Belgium1
Belgium2
Belgium3
Canada1
Canada2
Canada3
Canada4
Chile1
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    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.

8 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

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

    • AuroraNI's avatar
      AuroraNI
      Icon for Helper III rankHelper III
      6 years ago

      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

      Algeria1
      Belgium1
      Belgium2
      Belgium3
      Canada1
      Canada2
      Canada3
      Canada4
      Chile1
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        6 years ago

        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.

  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.