Forum Discussion
Replace Blank or Nulls based on Column Type
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!
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
5 Replies
ronrsnfldSuper User
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 replaceMThank 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?
CNENFRNLCommunity Champion
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"
