Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

check if columns are blank/null

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), evalu...
Need Help
Show and Tell
Tips and Tricks
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    3 years ago

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

    List.RemoveNulls(Record.ToList(Record.SelectFields(_, Query1))) <> {}
Rickmaurinus's avatar
Rickmaurinus
Helper V
3 years ago

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.