Forum Discussion
Custom function to replace null in all the table
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.
(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
11 Replies
(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 NewTableThanks 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:
- you have null as second parameter. Could you explain why?
- you put (x)=>x. Could you explain why?
- 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.
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 isnull, it replaces it with the string"-". All other values remain unchanged, and this replacement happens across all columns of the table.
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))(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 )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
