How do I replace nulls with errors in Power Query?

Question

Hi there,

I need to replace all nulls in all the columns of my table with errors so that I can then use Table.RemoveRowsWithErrors. (I don't want to replace errors with nulls, nor to replace nulls with the string "errors".)

Thanks in advance.

Answer 1

Hi Jesus

How do I replace nulls with errors in Power Query?

If you succeed in doing this let me know I'm more than interested (curiosity). Don't get me wrong, I'm not saying this is mission impossible, they are very creative people...

(+ doing such replacement might be inefficient on a large table)

I need to replace all nulls in all the columns of my table with errors so that I can then use Table.RemoveRowsWithErrors

As I understand this - ultimately - means filtering out rows containing >/1 null, something not doable with Power Query UI option Remove Blank Rows:

.

FilteredRows = Table.SelectRows(<Previous Step Name>,

(rec) => not List.Contains(Record.ToList(rec), null) 

),

Or, if this talks better to you:

FilterRowsTwo = Table.SelectRows(<Previous Step Name>,

each not List.Contains(Record.ToList(\_), null) 

),

Corresponding sample available here

EDIT: Sample updated. Includes version with the UI only. See query RemovedRowsWithAnyNull_UI

Answer 2

Hi Jesus

No new for a week :( Good/Bad news? Whatever, any feedback will help people who Search

Thanks in advance

Answer 3

Hi,

Your question is not clear. Why can you simply not filter out the rows which have nulls? Could you share some data and show the expected result.