Need auto increment column based on condition

[prateekraina]

Hi Guys,

I have this requirement where i need to generate auto inrement numbers based on a condition.
Below table explains it all. I have tried using Index column in Query Editor but was not able to get the requried result.
I require the solution in Power Query M, any Leads would be appreciated.

			What i get	What i require
X	Y	Z	Index	Index
null	null	null	1	null
null	null	null	2	null
1	2	5	3	1
null	null	null	4	null
null	null	null	5	null
3	1	1	6	2
2	6	8	7	3

Prateek Raina

[ImkeF]

What you get is a good start.

Next filter that table on the rows where you want to apply your final Index to (in this case: Filter out rows with null).

Then create a new Index on that result and merge that table back with the previous step (where all rows where still in) on the old Index-column as key.

Delete the old Index column and expand the new one.

[prateekraina]

Hi @ImkeF,

I have applied final index and then created new index on that result. However, i do not know how to merge that result with previuos step. All i can see is that we can merge two different tables and not query steps. Is there anything which i am missing.

Kindly let me know.

Prateek Raina

[ImkeF]

Pls check if this vid helps: https://www.youtube.com/watch?v=looCm3cbINw

[prateekraina]

Hi @ImkeF,

I understood what the video has showcased, but in my scenario its not helping because of following reasons:
What i have done:
Step 1:  I added Index column. It creates a step named say "Added Index"

What i have done post your answer:

Step 2: I have filtered the null rows. It creates a step named say "Filtered Rows"
Step 3: Now in generate the Index column again. Step name "Added NewIndex"
Step 4: Now i do self merge on OldIndex  and it creates a step say "Merged Queries"
Power Query generated is:

= Table.NestedJoin(#"Added NewIndex",{"Index"},#"Added NewIndex",{"NewIndex"},"NewColumn",JoinKind.LeftOuter)

Step 5: Now, as per you i should merge with Step 1 in which all my rows were present, so when i edit the above M Query to replace  #"Added NewIndex" with #"Added Index"
When i do this "NewIndex" column gives error since it did not exist in that step.


Please let me know if i am on the right track or not.

Prateek Raina

[ImkeF · Accepted solution]

Nearly 🙂

Here comes the full code with sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSQaFidXAKGwI5RkBsik8RDmFjIMcQjEE8kClmQGyhFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [X = _t, Y = _t, Z = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"X", Int64.Type}, {"Y", Int64.Type}, {"Z", Int64.Type}}),
    FirstIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Filtered Rows" = Table.SelectRows(FirstIndex, each ([Z] <> null)),
    SecondIndex = Table.AddIndexColumn(#"Filtered Rows", "NewIndex", 1, 1),
    #"Merged Queries" = Table.NestedJoin(FirstIndex,{"Index"},SecondIndex,{"Index"},"NewColumn",JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Index"}),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Removed Columns", "NewColumn", {"NewIndex"}, {"Index"})
in
    #"Expanded NewColumn"

If you have trouble dealing with it, please watch this video: http://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-co...

[prateekraina]

Hi @ImkeF,

Thank you so much for the help.

Prateek Raina

[prateekraina]

Hi @ImkeF,

Thanks for the prompt response I will try this approach and share the result soon.

Prateek Raina