cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
coreyweiss Member
Member

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Consecutive Row Counter Column

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. 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

13 REPLIES 13
Super User
Super User

Re: Consecutive Row Counter Column

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

coreyweiss Member
Member

Re: Consecutive Row Counter Column

Sure!

Super User
Super User

Re: Consecutive Row Counter Column

Hi @coreyweiss,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




coreyweiss Member
Member

Re: Consecutive Row Counter Column

Thanks Felix for your reply!

 

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

Super User
Super User

Re: Consecutive Row Counter Column

You can change the Animal by any column you have in your table I picked up your example so I assumed you had that column on your source data.

Regards
MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Consecutive Row Counter Column

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"

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




coreyweiss Member
Member

Re: Consecutive Row Counter Column

@ImkeF@MFelixThank you so much for replying!!!

 

This dataset is much more hairy than what I posted as an example and cannot begin to share the source data 😕 . I require a DAX solution unfortunately.

Super User
Super User

Re: Consecutive Row Counter Column

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. 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Sean Super Contributor
Super Contributor

Re: Consecutive Row Counter Column

I just wanted to add this link to this topic!

Related to ImkeF's solution...

https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/

 

Anyway I'll "process" Smiley Very Happy @Greg_Deckler's solution later today Smiley Wink

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 202 members 2,202 guests
Please welcome our newest community members: