Forum Discussion
Mic1979
Post Partisan
Post PartisanCustom 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....
(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
AlienSx
Super User
Super User(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
)