How to increment a number variable if column value equals something

[JollyRoger01]

I have what I thought was a simple request in Power Query. I want to increase a number variable called 'index' whenever it comes across a blank value in a column. I also want to then add this index value as a custom column. This process of incrementing a variable inside an if statement is pretty standard across languages but for the life of me I cannot work this one out in M.

Also, can someone please tell me how I add the code to this message in M language? It is not one of the options in the dropdown.

let
    Source = Csv.Document(File.Contents("C:\Users\user\Desktop\Metrics.csv"),[Delimiter=",", Columns=174, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    index = 1,
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] = "" then null ### index = index + 1 #### else index)
in
    #"Added Custom"

[mahoneypat]

It isn't straight forward, but here's one way to do it in the query editor with List.Accumulate.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyBUyCiVQwmQYm08FkFkIyG0zmgMlcpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TextColumn = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"TextColumn"}),
    Custom1 = let thislist = #"Replaced Value"[TextColumn] in List.Accumulate({0..List.Count(thislist)-1}, {0}, (state, current) => if thislist{current} = null then state & {List.Last(state) + 1} else state & {List.Last(state)}),
    Custom2 = Table.FromColumns({#"Replaced Value"[TextColumn], Custom1}),
    #"Added Custom" = Table.AddColumn(Custom2, "Index", each if [Column1] = null then null else [Column2] + 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column2"})
in
    #"Removed Columns"

Pat

[mahoneypat]

Thought of a simpler way to do it too.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyBUyCiVQwmQYm08FkFkIyG0zmgMlcpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TextColumn = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"TextColumn"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [TextColumn] = null then 1 else 0, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let thisindex = [Index] in List.Sum(Table.SelectRows(#"Added Custom", each [Index]<=thisindex)[Custom]) + 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Custom"})
in
    #"Removed Columns"

Pat

[JollyRoger01]

Thanks Pat. I was able to get your first example working, though I can't work out how to use your Index column with my original table (I need all the original columns). Below is the working code of where I am at:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZA7DoAgEAXvQs2NCIV8Ez73b2VhQiFq4iQT3xSrxqhL6Q+sNsoNe0dWT+sPZA204UDWIc9HXkeiCG67rGmVCfx2GTNphrBd1kJbYOvPMZWo8p0Jr3Ntq2xcmfE2x07aObPg47faGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"20201218" = _t, #"20201221" = _t, #"20201222" = _t, #"20201223" = _t, #"20210104" = _t, #"20210105" = _t]),
    #"Replaced Value1" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"(blank)", "20201218", "20201221", "20201222", "20201223", "20210104", "20210105"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"(blank)", "TextColumn"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"TextColumn", type text}, {"20201218", type text}, {"20201221", type text}, {"20201222", type text}, {"20201223", type text}, {"20210104", type text}, {"20210105", type text}}),
    Custom1 = let thislist = #"Changed Type"[TextColumn] in List.Accumulate({0..List.Count(thislist)-1}, {0}, (state, current) => if thislist{current} = null then state & {List.Last(state) + 1} else state & {List.Last(state)}),
    Custom2 = Table.FromColumns({#"Changed Type"[TextColumn], Custom1}),
    #"Added Custom" = Table.AddColumn(Custom2, "Index", each if [Column1] = null then null else [Column2] + 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column2"})

// The lines commented below are not working as expected. I want to tack on the "Index" column to the end of my original table but it is creating more rows.
//    #"Table" = Table.Combine({#"Changed Type", #"Removed Columns"})
//    in 
//        #"Table"

in
    #"Removed Columns"

[AlB]

Hi @JollyRoger01 

Do yo want to share some sample input data plus what the expected output would look like?  At least the first table in text-tabular format so that the contents can be easily copied

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[JollyRoger01]

Current:

Desired:

Note, I am not just trying to increment the index where there is a null or blank value, there could be a variety of other values I want to do this to. I am aware there may be ways to do this with grouping etc. but I want to know how to increment indexes this way as there are other unrelated problems I need to apply this to.