Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 8
Phil_Seamark
Employee
Employee

Hi @Anonymous

 

This worked for me

 

= Table.ReplaceValue(#"Renamed Columns",null,0,Replacer.ReplaceValue,{"Col 1", "Col 2", "Col 3", "Col 4"})

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Anonymous

The same way as @Phil_Seamark but using the UI. Press "Ctrl" to select multiple columns and replace the values.

 

Capture.PNG

 

Capture1.PNG

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
    Name

 

Query 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
    Name

 

Query 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"

 

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

@MarcelBeug, this is much closer to what I had envisioned with some of the capabilities. I came across something similar to what I was thinking, but this was only to change a column header. It's just a step you add anywhere in your power query to transform the headers to a clean name, regardless of what's there. I think something like this is possible with the data types, but it might be a lot longer given the work needed for figuring out the Kind.

 

 

#"Alter Column Names" = Table.TransformColumnNames( #"<<<Previous Change Name>>>", ( columnName as text ) as text => Text.Combine( List.Transform( Text.Split( columnName, "_" ), each if Text.Length( _ ) >= 4 then Text.Proper( _ ) else _ ), " ") ),

Thanks for the thumbs up, but I'm really confused by your previous post.

Your question was about replacing null values, so what have column headers to do with that??

Is your initial question answered?

Is your previous post just a remark or a new question?

In the latter case you'd better mark this topic as answered and raise a new topic.

Specializing in Power Query Formula Language (M)

Motivation from solution above ... as I needed to replace null values in the whole table without specifying columns - modified a bit suggested formula:
= Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,Table.ColumnNames(Source))

 

where Source - a reference to the data table.

Anonymous
Not applicable

many thanks for this. It helped me solve the exact same issue I was having.

parry2k
Super User
Super User

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.