check if columns are blank/null

[Anonymous]

I need to find records where a group of columns all contain "null" values. If the specified columns only contain null values, then evaluate as false. If columns contain any value (ie <> blank), evaluate as true.

A	B	C	D	result
sunny	null	rainy	null	true
null	null	null	null	false

Surely there must be a better way to determine this without merging cell values?

I would like to do something like:

= Table.AddColumn(#PreviousStep, "result", each if column in list <> null then true else false)

Where "list" is another referenced query with a list of the column names I want to apply the function to.

[Rickmaurinus]

Hi brendo,

You can paste below formula into the custom column formula box: 

if List.NonNullCount (Record.FieldValues ( Record.SelectFields ( _, YourList ) ) ) = 0 then false else true

What this statement does is this: 

• First Record.SelectFields retrieves the record fields (row values in your table) of all columns specified in 'YourList'. In this case 'YourList' should be a reference to the other query
•  Record.FieldValues then returns the values from the record fields as a list
• List.NonNullCount then counts how many items are not null. If none of them are, you can return false like said in your requirements.

Just make sure to change the 'yourlist' value to the list of columnnames you had in mind. 

Do you need any additional help? 

Rick

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

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

[Vijay_A_Verma · Accepted solution]

Use below in your custom column where Query1 holds the list of columns

List.RemoveNulls(Record.ToList(Record.SelectFields(_, Query1))) <> {}

[Anonymous]

I attempted this, but I am getting the incorrect number of blank rows (did a function in R to check for blank rows and R is showing me double the number of rows than in power query, and I am analyzing the same dataset in R). Could you explain step by step what your query does? 

[Vijay_A_Verma]

Query1 should be a list.

What it would do is to perform row wise operation.

For a row, it will select only those fields which are present in Query1. 

It will convert this record into list. Hence, the list will contain only those values whose columns were present in Query1.

It will remove all nulls from this list and it will be compared to check whether the list is blank or not.

If list is blank it means all values were nulls. Hence, it will give false.

If there is even a single value, it will give true.

[ToddChitt]

Not sure I understand your goal. You have a Reference list of data, say it contains rows with values "A" and "B" (rows not columns) Is that right?

And you want to manipulate the rows in this table, say, remove "A" and add "C", and have the results of the main query change to only look at columns B and C in the Power Query?

[Anonymous]

I have a list of 25 column names. 

I have a dataset that has 100 columns. I want to check, for every row, whether there is data in any of those 25 columns. If there is a value in one of those 25 columns, return TRUE in new column. If that record only contains null values in those 25 columns, return FALSE.