Forum Discussion
Null value error
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
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
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.
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?
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.
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)
