cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ovetteabejuela Impactful Individual
Impactful Individual

Power Query | A little help on Comparing Nulls

So, I want to see if multiple columns are nulls and if they are filter them out.

 

My first attempt was something like

 

if [column1] = null and [column2] = null and [column3] = null and [column4] = null then "Yes" else "No" - doesn't work.

 

then I saw Value.Compare option and even just the List.Sum method - doesn't work!

 

Funny thing is that why is it that it doesn't shout at me in the designer(editor) but when I refresh the table it gives me this:

 comparingnulls.PNG

 

 So how do i correctly do this? Thanks in advance

5 REPLIES 5
Joorge_C Resolver I
Resolver I

Re: Power Query | A little help on Comparing Nulls

Can you try to change the column format to number,etc.. and get errors, then convert errors into something?

 

Or replace values on columns from null into something you can filter out.

Super User IV
Super User IV

Re: Power Query | A little help on Comparing Nulls

@ovetteabejuela not sure why this will not work,

 

if [column1] = null and [column2] = null and [column3] = null and [column4] = null then "Yes" else "No"

 

in query editor it loads first 999 rows and when you refresh, it goes thru whole data set and I guess there is some issue when whole dataset get loaded.

 

Quick question, if you don't add this custom column, do you still get error? May be it is not this column but other error.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





ovetteabejuela Impactful Individual
Impactful Individual

Re: Power Query | A little help on Comparing Nulls

"in query editor it loads first 999 rows and when you refresh, it goes thru whole data set and I guess there is some issue when whole dataset get loaded."

 

Yeah, i think this might be it, I will need to investigate further.

Microsoft
Microsoft

Re: Power Query | A little help on Comparing Nulls

Hi @ovetteabejuela,

Could you have tried with the 'Add conditonal column' function in query editor?

1.PNG

Reference:https://excelkingdom.blogspot.com/2017/12/how-to-add-conditional-column-to-table.html

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft
Microsoft

Re: Power Query | A little help on Comparing Nulls

Hi @ovetteabejuela,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors