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))) <> {}
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
3 years ago

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

List.RemoveNulls(Record.ToList(Record.SelectFields(_, Query1))) <> {}
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    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.