How to create a loop to change newValue in Table.ReplaceValue function

[Rettel98]

In main query table, there are a number of columns with null that need to be replaced with the name of the column. It can be done one by one, but I am trying to create one step that handles this.

So far I have this M code (2 of the column names have been placed as an example but the list of columns is longer):

List_nullCategories =
      {"Age", "Race"}


    FillDown_Categories =

      Table.ReplaceValue(Source,
        each [Age] = null,
        List_nullCategories{x},

        
        (currentValue, condition, newValue) =>
        if condition then newValue
        else currentValue,


        List_nullCategories
      )
in
    FillDown_Categories

I would like the x in List_nullCategories{x} to start off as a 0 and be replaced with the count of each list item until the end of the list.

Is there a way to wrap the whole Table.ReplaceValue function inside another function that loops through the list and changes the value of x? If there is a better way to do this iteration, then I am open to that as well.

 

[alex78]

Hi Rettel98,
try

List.Accumulate(Table.ColumnNames(Source), Source, (s,c)=> Table.TransformColumns(s, {{c, each Replacer.ReplaceValue(_,null, c)}}))

regards,

 

[Rettel98]

Hi Rettel98,
try

List.Accumulate(Table.ColumnNames(Source),  Source, (s,c)=> Table.TransformColumns(s, {{c, each Replacer.ReplaceValue(_,null, c)}}))

regards,

Thanks, Alex! Your code did the trick. I replaced Table.ColumnNames() with my custom list to limit which columns had the null values replaced. I was trying to use the List. Accumulate function but didn't place it within the transform columns function. In the transform operation, is the extra set of curly brackets used because the column names (and their values) are gotten from a list?

 

[alex78]

You 're right, the second set of curly brackets was not required!

Another way to solve your problem:

Table.TransformColumns(Source, List.Transform(Table.ColumnNames(Source), each let ColName = _ in {_, each Replacer.ReplaceValue(_,null,ColName)}))

Regards,

 

[Rettel98]

You 're right, the second set of curly brackets was not required!

Another way to solve your problem:

Table.TransformColumns(Source, List.Transform(Table.ColumnNames(Source), each let ColName = _ in {_, each Replacer.ReplaceValue(_,null,ColName)}))

Regards,

Thanks, Alex. I'm going to stick with the first one because I understood that one instantly. I will have to study this second one a bit.