Replace Blank or Nulls based on Column Type

[nancyvangrrr]

I need to replace blank or null values in a table based on the column data type. For example, if a column is type text, replace the value with "N/A" and if the column is an integer, replace the value with 0.

I came across this solution from @Nolock which gets me part of the way there: https://community.powerbi.com/t5/Power-Query/Replace-null-with-0/td-p/718149

let
    // my test data
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiI6VYnWgQwxjEj40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    // get all column names as list
    allColumnNames = Table.ColumnNames(Source),
    // transform the column name list into a list of list, where every inner list contains column name and a function for replacing null value
    allTranformations = List.Transform(allColumnNames, each {_, each if _ = null or _ = "" then 0 else _}),
    // apply the transformations
    tranformColumns = Table.TransformColumns(Source, allTranformations)
in
    tranformColumns

However, I need help with adding a condition for whether the column data type is an integer or text column. I have over 21 queries, each with multiple columns, so doing this manually is not ideal.

Thanks!

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWJVgIyjMCMJCDLGMxKBrJgcnmlOTlgdgqQYwJmpQJZpmBWGpBlBlMJptOBDHMwKwPIslCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Invocation Fn_Replace" = Fn_Replace(#"Changed Type")
in
    #"Invocation Fn_Replace"

[ronrsnfld · Accepted solution]

Create a list of Transforms depending on the column types, then apply that.

For example, if you wanted to apply your above transformations to every column in a table of those data types, you could use:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Ncw7EoAgFEPRvaSmUfGzlzevEBEsGDr2b8yM1T1pYoYTARM8GIhZSNQiXZTQR2u08u3MEaWbWqVCbf+TWoldeqgD7i8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"Column1"}),
    #"Previous Step" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1", type text}, {"Column2", Int64.Type}}),

//Use this part below in your code
//  changing #"Previous Step" to whatever your previous step is
xFormList = 
    List.Transform(
        Table.SelectRows(Table.Schema(#"Previous Step"), 
            each [TypeName]="Text.Type")[Name], (L)=>{L, each if _ = "" or _ = null then "NA" else _, type text}) & 
    List.Transform(
        Table.SelectRows(Table.Schema(#"Previous Step"), 
            each [TypeName]="Int64.Type")[Name],(L)=>{L, each if _ = "" or _ = null then 0 else _, Int64.Type}),

    replaceM = Table.TransformColumns(#"Previous Step", xFormList)
in
    replaceM

[nancyvangrrr]

Thank you! This worked perfectly, but when I try to apply the changes, I'm getting this error message. I've confirmed that all the data types match what was in the previous step so this is weird.

[ronrsnfld]

Can you possibly upload a file that demonstrates the problem?

[nancyvangrrr]

Never mind. I found out what my issue was. The data types in Service weren't matching what was in Desktop. After matching the data types, it worked! Thank you!