Forum Discussion
How to increment a number variable if column value equals something
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 v...
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
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
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"
