How to create an index that reset if in a custom column change of value

[Danielecc]

Hi everyone,

I need to create a new column with an index or count that restar if one of my calculated column called IND_DC change of value.

I need create this on power query and not in DAX, and I hope this image could help to understand this question (because my english is very bad)  Thanks to all:

[parry2k]

@Danielecc hey this video is now available here: 

[parry2k]

@Danielecc it's all good, indeed if you subscribe you will get notified when it is out. Anyhow I always post interesting content on my channel, and I'm sure you will learn something new from it. 

[parry2k]

@Danielecc I'm glad you have a solution in place. Good for you. I'm going to do a video on this topic, it is an interesting question and will be good to do a video with all the explanations so that others can make use of it. If you are interested I will share the link here when it is ready.  

[Danielecc]

@parry2k  of course I am interested, I dont understand that you made only I aplied fallowing your instructions, for that reason I will waitng for that link here for your video and I think it's a good idea suscribe me to your channel too. Thanks again for help to us (and sorry for my bad english 😄 ).

[parry2k]

@Danielecc the code I gave was just to show how it will work, you have to apply all the steps in your tables, on the expand table step you will have all the columns available from your table.

[Danielecc]

@parry2k , sorry I dont know nothing of M, then I dont understand your first reply, but I made that you said in your second post and works OK. Then I have to:

1. Accept as Solution your first post

2. Thanks a lot for your solution, time and explanation.

Best regards...

[parry2k · Accepted solution]

@Danielecc here is M code to achieve this, to understand it, start a blank query -> click advanced editor and paste this code. You will see all the steps on how this solution works, you can apply the same in your actual table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMkAiMUXwy5JqAhoZCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IND_DC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IND_DC", Int64.Type}}),
    #"Added Index Current Row" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Index Previous Row" = Table.AddIndexColumn(#"Added Index Current Row", "Previous Index", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index Previous Row", {"Index"}, #"Added Index Previous Row", {"Previous Index"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Next Row Value" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"IND_DC"}, {"Next IND_DC"}),
    #"Sorted Rows Current Row Index" = Table.Sort(#"Expanded Next Row Value",{{"Index", Order.Ascending}}),
    #"Added Group Column" = Table.AddColumn(#"Sorted Rows Current Row Index", "Group", each if [IND_DC]<>[Next IND_DC] or [Next IND_DC]=null then [Index] else null, Int64.Type),
    #"Filled Up Group" = Table.FillUp(#"Added Group Column",{"Group"}),
    #"Grouped Rows1" = Table.Group(#"Filled Up Group", {"Group"}, {{"All", each Table.AddIndexColumn(_,"New Index",1,1), type table [IND_DC=nullable number, Index=number, Previous Index=number, Next IND_DC=nullable number, Group=number, New Index=Int64.Type]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows1", "All", {"IND_DC", "New Index"}, {"IND_DC", "Index"}),
    #"Removed Group Columns" = Table.RemoveColumns(#"Expanded All",{"Group"})
in
    #"Removed Group Columns"

[Danielecc]

Hi @parry2k , thanks a lot for you answer.

I use your code adding a new step, and the counter/index works, but...I see only the first 22 rows and the real have a lot of rows. And I see only 2 columns (IND_DC and Index) but I need to see all columns (Source, Datos, IND_DC and Index). Do you know what I must change to see all rows and all columns?