NULL vs Blank

[TheFred]

Hello all

I'm sorry I'm posting such as trivial question but I just cannot get my queries to work. I am comparing 2 data sets - no problem. I have cleaned and trimmed both but the merge query is showing differences even though the comparison in Excel shows no differences.

I have identified the problem being 'blank' cells in one data set and 'null' in the other. I have checked the cells in Excel: if a cell ISBLANK = TRUE, it loads as null in Power Query; but if a cell is ISBLANK = FALSE, it loads as 'blank' in Power Query. I need to make sure the data is nothing but cannot find a way to do it in Power Query. I can clean the data in Excel: if I press delete in the cell that has ISBLANK = FALSE or click in it, and press the back arrow (not the delete or back space), the cell changes to ISBLANK = TRUE. I cannot find a formula to make a bulk change and I don't want to have to do this manually (I have tried many variations re setting the cell to "" but it has not worked).

So I have a few questions:

1. what is the difference between 'blank' and 'null' in Power Query? In one data set, I can see both of these options in the column drop down (blank actually shows nothing in the cell and null shows the word null) but what information is different?

2. how can I set the data to be nothing in Excel (prior to load)?

3. how can I set the data to be nothing in Power Query (once loaded)?

Thanks

 

[Pecoflyer]

Hi and welcome
if you enter somewhere in your Excel sheet =CODE("reference of your ISBLANK=FALSE cell") what do you get ?
In your excel sheet,
have you tried Ctrl+H
Find what : ""
Replace with( leave blank)
Replace All

This link might also help

(as for PQ I can't help you there)


BTW don't forget to acknowledge the answers you get, as you did last time. If you make a habit out of it you might find members are not willing to help anymore.

 

[AliGW]

You can also replace null values with nothing in PQ.