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
Mederic
Helper V
Helper V

Exclude a particular column in the code "if is Null then_"

Hello everyone 😊,
I have a table with more than 10 columns,
I have the M code below which allows me to replace Null values with 0,
However, I would like not to apply on a particular column, in this case "ColumnName4"

How to correct the code ?

Thanks in advance

Regards

 

If_is Null then_.jpg

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColumnName1", type text}, {"ColumnName2", Int64.Type}, {"ColumnName3", Int64.Type}, {"ColumnName4", type text}, {"ColumnName5", Int64.Type}}),
    Custom1 = Table.TransformColumns(#"Changed Type", {}, each if _ is null then 0 else _)
in
    Custom1

 

 

 

 

ColumnName1ColumnName2ColumnName3ColumnName4ColumnName5

A32 ABC65
A 87DEF 
B5576  
B7623GHI45
B  IKL98
1 ACCEPTED SOLUTION
jennratten
Super User
Super User

Hello - @jgordon11 's solution is good if you'd like to specify columns by name. Here is another option since it looks like you are trying to replace nulls with zeros for columns with numeric data.  Here is how you can do that.

= Table.ReplaceValue(#"Changed Type", null, 0, Replacer.ReplaceValue, Table.ColumnsOfType(#"Changed Type", {type nullable number}))

 

jennratten_0-1679156215024.png

 

View solution in original post

3 REPLIES 3
Mederic
Helper V
Helper V

Hello @jgordon11 , @jennratten ,

Thanks both for your solutions,
In my real file, both codes work perfectly,
The replacement of the Null values according to the type of the columns is in my case more appropriate
Thank you again

Regards

 

 

jennratten
Super User
Super User

Hello - @jgordon11 's solution is good if you'd like to specify columns by name. Here is another option since it looks like you are trying to replace nulls with zeros for columns with numeric data.  Here is how you can do that.

= Table.ReplaceValue(#"Changed Type", null, 0, Replacer.ReplaceValue, Table.ColumnsOfType(#"Changed Type", {type nullable number}))

 

jennratten_0-1679156215024.png

 

jgordon11
Resolver II
Resolver II

= Table.ReplaceValue(#"Changed Type", null, 0, Replacer.ReplaceValue, List.RemoveItems(Table.ColumnNames(#"Changed Type"), {"ColumnName4"}))

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.

Top Solution Authors
Top Kudoed Authors