Forum Discussion
Post PatronExclude 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
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
| A | 32 | ABC | 65 | |
| A | 87 | DEF | ||
| B | 55 | 76 | ||
| B | 76 | 23 | GHI | 45 |
| B | IKL | 98 |
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}))
3 Replies
jgordon11Resolver II
= Table.ReplaceValue(#"Changed Type", null, 0, Replacer.ReplaceValue, List.RemoveItems(Table.ColumnNames(#"Changed Type"), {"ColumnName4"}))
MedericHello 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 againRegards
- jennratten
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}))