Forum Discussion
Need auto increment column based on condition
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
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
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