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 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"
5 Replies
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.
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.
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"
