Forum Discussion
Custom function to replace null in all the table
- 1 year ago
(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
- 1 year ago
From the top of my head... I don't have my laptop here now...
- 1 year ago
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 - 1 year ago
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
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))