Custom function to replace null in all the table

[Mic1979]

Hello all

I am trying to write a function to replace in all the table null with "-".

I started with a single column,

(Input_Table as table, ColumnToChange as text) =>
let
NewTable = Table.ReplaceValue(
Input_Table,
each ColumnToChange,
each if ColumnToChange = null then "-"
else ColumnToChange,
Replacer.ReplaceValue,
{ColumnToChange}
)

in NewTable

but also in this case I have no errors but it is not doing what I want.

Additionally, how to do this with all the columns?

Sorry if this is a silly question for you, but I am on the learning curve.

Thanks.

[PwerQueryKees · Accepted solution]

(Input_Table as table, ColumnToChange as text) =>
let
NewTable = Table.ReplaceValue(
Input_Table,
null,
each if _ = null then "-"
else _,
Replacer.ReplaceValue,
Table.Columns(Input_Table)
)

in NewTable

[PwerQueryKees · Accepted solution]

From the top of my head... I don't have my laptop here now...

[Ahmedx · Accepted solution]

pls thy this code

(Input_Table as table) =>
let
NewTable = Table.ReplaceValue(
Input_Table, null,(x)=>x, (x,y,z)=> if x = null then "-" else x, Table.ColumnNames(Input_Table)) 

in NewTable
---------or------------
(Input_Table as table) =>
let
NewTable = Table.ReplaceValue(
Input_Table, (x)=>x,(x)=>x, (x,y,z)=> if x = null then "-" else x, Table.ColumnNames(Input_Table)) 

in NewTable

[Omid_Motamedise]

For this task, you do not need to write a custom function, sleect all the columns, and use the simple replace task to apply this change

[ronrsnfld]

If you want to replace all the nulls in the table, there is no need for a column argument in your function:

(tbl as table)=>

Table.ReplaceValue(
    tbl,
    null,
    "-",
    Replacer.ReplaceValue,
    Table.ColumnNames(tbl))

[Ahmedx · Accepted solution]

pls thy this code

(Input_Table as table) =>
let
NewTable = Table.ReplaceValue(
Input_Table, null,(x)=>x, (x,y,z)=> if x = null then "-" else x, Table.ColumnNames(Input_Table)) 

in NewTable
---------or------------
(Input_Table as table) =>
let
NewTable = Table.ReplaceValue(
Input_Table, (x)=>x,(x)=>x, (x,y,z)=> if x = null then "-" else x, Table.ColumnNames(Input_Table)) 

in NewTable

[Mic1979]

Thanks a lot for this code.

In the online helper I found 

Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table

I would need some explanation:

1. you have null as second parameter. Could you explain why?
2. you put (x)=>x. Could you explain why?
3. you have (x,y,z)=> if x = null then "-" else x as replacer. I see in the syntax you need to put a function here as replacer, so I understand the sense to have a function here. However the syntax of this function is not clear to me. Could you explain? 

Thanks.

[Ahmedx]

1. Table.ReplaceValue — this function replaces values in a table according to a specified rule. It takes several arguments:
   • The first part is the table in which the replacement occurs, in this case, Input_Table.
   • The second and third parts are the values to search for and replace. Here, both are defined as (x) => x, meaning it doesn’t search for a specific value but operates on all values.
   • The fourth part defines the logic for the replacement: (x, y, z) => if x = null then "-" else x, where:
     • x is the current value in the table cell.
     • If the value is null, it is replaced with the string "-". Otherwise, the value remains unchanged.
   • The last argument is a list of columns where the replacement should happen. Here, Table.ColumnNames(Input_Table) is used, meaning the replacement is applied to all columns of the table.

Conclusion: This code checks all cells in the Input_Table. If a cell’s value is null, it replaces it with the string "-". All other values remain unchanged, and this replacement happens across all columns of the table.

[Mic1979]

Ok many thanks.

one last question:

The second and third parts are the values to search for and replace. Here, both are defined as (x) => x, meaning it doesn’t search for a specific value but operates on all values.

In your code the second is null, not (x) => x. 

Did I miss something?

Thanks again. 

[Ahmedx · Accepted solution]

1The second and third parts are the values to search for and replace. Here, both are defined as (x) => x, meaning it doesn’t search for a specific value but operates on all values.

yes, "if" defines what we are looking for and what we are replacing it with
2) In your code the second is null, not (x) => x. 
and here I just rigidly fixed what we are looking for
that is, we are looking for null

[Mic1979]

Thanks a lot

[AlienSx]

(tbl as table, optional columns as list) =>
    Table.TransformColumns(
        tbl, 
        if columns is null then {} else List.Transform(columns, (x) => {x, (w) => w ?? "-"}),
        if columns is null then (w) => w ?? "-" else null
    )

[PwerQueryKees · Accepted solution]

From the top of my head... I don't have my laptop here now...

[PwerQueryKees · Accepted solution]

(Input_Table as table, ColumnToChange as text) =>
let
NewTable = Table.ReplaceValue(
Input_Table,
null,
each if _ = null then "-"
else _,
Replacer.ReplaceValue,
Table.Columns(Input_Table)
)

in NewTable