Forum Discussion

JollyRoger01's avatar
JollyRoger01
Helper III
5 years ago

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...
Need Help
mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

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's avatar
JollyRoger01
Helper III
5 years ago

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"