Forum Discussion
Calculate column handling Null values
select the column => Transform tab => Replace Values => Value To Find: null Replace With: 0
You may have do this step "Replaced Value" before the "Added Custom" where you add the 2 columns
select the column => Transform tab => Replace Values => Value To Find: null Replace With: 0
You may have do this step "Replaced Value" before the "Added Custom" where you add the 2 columns
Wherever possible I avoid changing data.
Try this instead:
each List.Sum({[ColA], - [ColB], [ColC]})to perform ColA - ColB + ColC
My full line being:
= Table.AddColumn(#"Previous Step Name", "Sum", each List.Sum({[ColA], - [ColB], [ColC]}), Int64.Type)Hello,
I have tried this and it worked for the sum column, thank you.
However, I also need to add a custom column that concatenates those same number type columns (plus others that are text type), so for that I need to replace nulls with "" rather than "0", otherwise the custom column captures the "0" as a text, so it doesn't 'ignore' it. I have tried to sort this out (the type mix type problem) by using Text.From, but this function doesn't accept null values, since it returns null as long as one column contains null in that row... unless one of you guys come up with an easy solution I couldn't figure out I think I will up and turn to Excel (after the query) or DAX when I load to the model. Do you know where I could find a guide or in which language use for each case and in which order should the transformations be applied?
Many thanks in advance
Javier
