Forum Discussion
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.
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
Thanks Felix for your reply!
This unfortunately must be done with a calculated column since the "Animal" column does not exist in the query.
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?
- 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
