Forum Discussion
Replace multiple nulls at once in Power Query
I've got a table with a few columns of numerical data, and a few with text data. For all of the numerical columns, where the value is null, I want to replace those with a 0. For columns where it is text, I'd like to replace it with something along the lines of "Undefined" or the like.
Looking at functions like Table.TransformColumns and Table.ColumnsOfType, I feel like I should be able to add a step that goes in and replaces all the nulls at once, rather than having to click and replace each value one at a time. Is this possible?
Are there any good resources for picking up more complex M? I'm having a hard time figuring out when I just missed a casing or letter, or when I've completely missed how the entire concept works.
8 Replies
Hi Anonymous
This worked for me
= Table.ReplaceValue(#"Renamed Columns",null,0,Replacer.ReplaceValue,{"Col 1", "Col 2", "Col 3", "Col 4"})Anonymous
The same way as Phil_Seamark but using the UI. Press "Ctrl" to select multiple columns and replace the values.
A more dynamic approach below. I splitted the solution in separate steps just for clarity.
Input is in Table1
Query TableShema:
Table.Schema(Table1)
Query NumberColumns returns a list of number columns in Table1:
let Source = TableSchema, #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "number")), Name = #"Filtered Rows"[Name] in NameQuery TextColumns returns a list of text columns in Table1:
let Source = TableSchema, #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "text")), Name = #"Filtered Rows"[Name] in NameQuery Result replaces the values in Table1:
let Source = Table1, #"Replaced Value" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,NumberColumns), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"Undefined",Replacer.ReplaceValue,TextColumns) in #"Replaced Value1"
I believe you already looked at this link https://msdn.microsoft.com/en-us/library/mt779182.aspx
You can use replace function query editor and it will add step for you.
