Forum Discussion
Custom function to replace null in all the table
(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
From the top of my head... I don't have my laptop here now...
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 NewTable1The 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
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:xis 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.
- The first part is the table in which the replacement occurs, in this case,
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.
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.
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 nullThanks a lot