How to deal with null value and blank value in power Query ?

[Clarafang]

Hi guys, 

I am a green hand to use power BI, I am quite confuse about how to deal with null values or missing value in different type of value now , it will destory the data by filling down or fieltering out/deleting the null / blank row in some situations...

for example those rating, status and department coloums, how should I handle them ?

is there any post that include the situations about dealing different types of coloumn with  null / blank value I can learn from it ?

Many Thanks!

[Syndicate_Admin]

Hi, but when you replace the null value with "1" that would not affect your average? In case you have numerical values that you want to evaluate

[amitchandak]

@Clarafang , You can use = null in formula

in replace you can use all small-cap null

in filter, you have an option for an empty value

https://www.edureka.co/community/40467/replace-null-values-custom-values-power-power-query-editor

http://excel-inside.pro/blog/2018/05/17/comparing-null-values-in-power-query/

[Clarafang]

Thanks for the replay! But what kind of way I should use for handle the rate coloum then ? replace with Blank, and use isblank()function to calculate? 

By the way, what means" all small-cap null"?

[Anonymous · Accepted solution]

Hi @Clarafang ,

As far as I know, Power BI will show empty data in number format by null and show show empty data in text format by nothing.

If you want to replace null value or use null value in M code, you need to use find null.

If you want to replace text nothing value , you can write nothing to find it. 

And you need to use "" to find it in M Code.

If this reply still couldn't help you solve your problem, please share a sample file with me and show me the result you want.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.