Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Consecutive Row Counter Column

 Hi there!

 

I am looking to make a counter that counts the consecutive rows and resets when there is a nonconsecutive value.

 

Here is an example:

 

 

AnimalCounter
Tiger1
Tiger2
Tiger3
Tiger4
Tiger5
Lion1
Lion2
Lion3
Tiger1
Tiger2
Elephant1
Elephant2
Elephant3
Tiger1
Tiger2
Tiger3

 

Any thoughts?

 

Thanks!

  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    See if this works:

     

    Column 2 = 
VAR __index = CALCULATE(MAX([Index]))
VAR __tmpTable1 = FILTER('Table34',[Animal]=EARLIER([Animal])&&[Index]<EARLIER([Index]))
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table34'),[Index]<EARLIER([Index]) && [Animal]=EARLIER([Animal])),[Index]))
VAR __max = MAXX(__tmpTable2,[Index])
VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index])
VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart)
RETURN IF(ISBLANK(__max),1,IF(__max=[Index]-1,COUNTX(__tmpTable3,[Index])+1,1))

    PBIX is attached. 

13 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi Anonymous,

     

    Based on the solution made by ImkeF on this post I have made some adjustments and arrived to the requested result on query editor.

     

    Basically I add the solution from in the post but added some previous steps :

    • Added an index column
    • Based on the index column I get the previous row animal name
    • Added a column to compare the Animal name in original column and the new one and return on that column the Index number if animal is diffent
    • Fill down the null values in order to get an additional column with the same number for each animal

     

    This last column is the base for you keeping the order if you apply the solution on the post above you will get the animals together and number from 1 to 10 on the tiger for example

     

    Check the M Code below:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslMTy1S0lEyVIrVQfCMUHjGKDwTFJ4pmOeTmZ8HNwTKMULmoBqBzTLXnNSCjMS8Erg0kgCGCsLGIbk9FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Animal = _t, Counter = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Animal", type text}, {"Counter", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Counter"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 0 then [Animal] else #"Added Index"{[Index] - 1}[Animal] ),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Index] = 0 then [Index] else if [Animal] = [Custom] then null else [Index]),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom.1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Filled Down",{"Index", "Custom"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Animal", "Custom.1"}, {{"Grouping", each _, type table}}),
    Custom1 = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Grouping], "Index", 1,1)),
    #"Removed Columns2" = Table.RemoveColumns(Custom1,{"Grouping", "Custom.1"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns2", "Custom", {"Index"}, {"Custom.Index"})
in
    #"Expanded Custom"

    If you want I can post a gif image.

     

    ImkeF do you have any suggestion to make this more easier or do you agree with this approach?

     

    Regards,

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks Felix for your reply!

       

      This unfortunately must be done with a calculated column since the "Animal" column does not exist in the query. 

      • ImkeF's avatar
        ImkeF
        Community Champion
        7 years ago

        Hi MFelix,

        setting the 4th (optional) parameter in the Table.Group-function to "GroupKind.Local" will do some magic in this use case ;-)

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslMTy1SitUhnuWTmZ+Hg4FNuWtOakFGYl4JAQ4eO2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Animal = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Animal", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Animal"}, {{"Partition", each Table.AddIndexColumn(_, "Counter", 1,1), type table}}, GroupKind.Local),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"Counter"}, {"Counter"})
in
    #"Expanded Partition"

        Anonymous: May I ask what your source-data exactly looks like?

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Can we assume that you would add an Index column to this in Power BI?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Sure!