Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

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....
  • PwerQueryKees's avatar
    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

  • PwerQueryKees's avatar
    1 year ago

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

  • Ahmedx's avatar
    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

     

  • Ahmedx's avatar
    Ahmedx
    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