Null value error

[hudsonvj]

Good morning. I'm having a problem with null values ​​in my data. I am performing a merge query to return dates, and in fact it will happen that there are dates that were not returned (empty). Bringing as null result. After that, I subtract dates to return the days, and then create a new conditional column to return values ​​from 1 to 4, which will represent deadlines for the calculated days. The problem is that when creating the conditional it is giving me the following error.

I've tried a few other ways, but without success.

1ºReturn from Merge Queries

2ºSubtraction of days

3ºConditional column, which generates the error at the beginning of the message

[dufoq3 · Accepted solution]

Start your custom column with if [Days] = null then null else if ..../your code/... [Days] < 2...

You can find how to use coalesce operator here.

[hudsonvj · Accepted solution]

So, I was unable to replace values, as it asks to insert a date value. Even with my column categorized as ABC123 he didn't allow it.

My intention is to leave the result blank or empty if it is null.

Another way I tried to do it using se:

At first it works.

However, when I categorize the column for date, it becomes null again

[trebgatte · Accepted solution]

@hudsonvj You said you were doing a merge with another table. So, parent A table merged with child B table, yes?

Why not do these transformations in the detail table first, then merge the tables together? 

If you need a value from parent A table to do this child B table transformation, you can always create a reference version of parent A table, call it Clone table. The Clone table would only contain the columns needed to merge with child B table and whatever columns you need to do this transformation.

So merge child B table with Clone and do your date math. There shouldn't be any null dates then hopefully. 

Once completed, then you merge parent A table with the now transformed child B table as you were doing before,. Now when you expand, your transformations were already done. No need to worry with any nulls.

You can also deactivate the Clone table since it should never be used but it will be executed when merged with child B table. 

Hopefully this makes sense. I've had to do this before and it was simpler to do this and maintain it over time than  it is to figure out a really convoluted if then nesting logic. 

--Treb

[ronrsnfld]

What do you want to happen if [Days] = null?

You can use a nested IF construct or the coalesce operator, depending on what you want for a result.

[hudsonvj]

If the value is null, I would like it to return blank. I tried using quotation marks like in Excel "" , but it kept returning null. And I'm also unable to replace null with blank Could you show me an example with coalesce?

[ronrsnfld]

Using a nested if statement will be simpler than using the coalesce operator.

In the Advanced Editor, replace the line where you add the Custom Column (the English version is shown below), with:

(Replace #"Previous Step" with the name of the previous step as seen in the line above where you insert this code line)

    #"Added Custom" = Table.AddColumn(#"Previous Step", "Request Priority Code", each 
        if [Days] = null then "" 
            else if [Days] < 2 then 1 
                else if [Days] < 8 then 2 
                    else if [Days] < 21 then 3 
                        else 4, 
        Int64.Type)

[dufoq3 · Accepted solution]

Start your custom column with if [Days] = null then null else if ..../your code/... [Days] < 2...

You can find how to use coalesce operator here.

[AbhinavJoshi]

Hi @hudsonvj. You can replace null with empty strings in Power Query Editor. Right Click on your desired column and click replace values. Enter null in the dialog 1 and replace it with emtpy string.

[AbhinavJoshi]

Hello @hudsonvj. You cannot perform logical operations when you have null values. One option is to remove the null values and then do conditional column. Or, modify the condition in conditonal column to only peroform the logic when the values are not null. You can use nested if elseif else to do it.

[hudsonvj · Accepted solution]

So, I was unable to replace values, as it asks to insert a date value. Even with my column categorized as ABC123 he didn't allow it.

My intention is to leave the result blank or empty if it is null.

Another way I tried to do it using se:

At first it works.

However, when I categorize the column for date, it becomes null again