Forum Discussion
Need auto increment column based on condition
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
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-code-into-your-existing/m-p/179314
7 Replies
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.
Hi ImkeF,
Thanks for the prompt response I will try this approach and share the result soon.
Prateek RainaHi 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
Pls check if this vid helps: https://www.youtube.com/watch?v=looCm3cbINw
