Forum Discussion

Memorable Member

9 years ago

Solved

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 abl...

Need Help

ImkeF

9 years ago

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

ImkeF

Community Champion

9 years ago

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