An OR filter on multiple columns

[Anonymous]

Hi, i have a table that i wish to check for missing data in multiple columns, and if any of those columns are empty, return the row.

I think i need to create a column and use an OR statement. However, an OR can only have two logic checks and i may need more than 10. From my experience with Excel i have grown an aversion to deeply nested formulae as they are a PITA to debug or edit after they have been built. 

Is it possible to check if any of these 10 columns are blank without a nested OR?

What is considered to be the best practice way to do such a check?

PS, some column checks may not just be a check for null, they may perform another check such as is a date in the past month etc.

[Anonymous]

I'm thinking there has to be a better way than doing 10 OR statements. Any chance you can upload a sample of your data and I can take a look?

[Anonymous]

---

@Anonymous wrote:

I'm thinking there has to be a better way than doing 10 OR statements. Any chance you can upload a sample of your data and I can take a look?

---

Link to table here

As a simple start, how would i filter the table where

Description OR Exposure OR Impact are blank, OR ModifiedDate is older than 28 days, except where CreatedDate is in the last 7 days. I caould then expand on the code to add in the other columns as needed.

Note that in the source data, the only obligitary  field is title, so ther need not be any logic as to whaat fileds are entered and what are blank. There are also some system generated fields such as ModifiedByResource and ModifiedDate. 

[Nolock]

Hi @Anonymous,

there is a way how to select columns of a table and then test every row if all defined columns contain a value.

I have prepared a commented example with some sample data for you. The first bold part is a selection of columns and the second bold part is then a condition.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoC4mQgTgHiVBA/sUgpVidaCUkKikCiiQguVDAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Foo = _t]),
    // columns which I want to test // in this case all columns starting with "Column"
    ColumnsForCheck = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Column")),
    // select only rows which contains at least one value which is empty string
    Result = Table.SelectRows(
        Source,
        (row) => List.AnyTrue(
            List.Transform(ColumnsForCheck, each Record.Field(row, _) = "")
        )
    )
in
    Result

[Anonymous]

---

@Nolock wrote:

Hi @Anonymous,

there is a way how to select columns of a table and then test every row if all defined columns contain a value.

I have prepared a commented example with some sample data for you. The first bold part is a selection of columns and the second bold part is then a condition.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoC4mQgTgHiVBA/sUgpVidaCUkKikCiiQguVDAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Foo = _t]),
    // columns which I want to test // in this case all columns starting with "Column"
    ColumnsForCheck = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Column")),
    // select only rows which contains at least one value which is empty string
    Result = Table.SelectRows(
        Source,
        (row) => List.AnyTrue(
            List.Transform(ColumnsForCheck, each Record.Field(row, _) = "")
        )
    )
in
    Result

---

Hi @Nolock,
Many thanks for this. A couple of questions

1. How would i combine this with a test on a date field.

E.g. return row, where

field A is Null

OR

field B is Null

OR

field C is Null

OR

Field D is not in the past 28 days.

2. How would i manually add the columns? There is no 'smart' way that i can see to select the columns, I just need to list them.

[Nolock · Accepted solution]

Hi @Anonymous,

here we go:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoC4mQgTgHiVCA2NtAzMNMzMjC0VIrViVZCUgFBRqjyiXAJKIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Field A" = _t, #"Field B" = _t, #"Field C" = _t, Column4 = _t, Column5 = _t, #"Field D" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Field D", type datetime}}),
    // columns which I want to test as a list
    ColumnsNamesForNullTest = {"Field A", "Field B", "Field C"},
    // select only rows which contains at least one value which is not null
    Result = Table.SelectRows(
        #"Changed Type",
        (row) =>  
            List.AnyTrue(
                List.Transform(ColumnsNamesForNullTest, each Record.Field(row, _) = null)
            ) 
            or
            Duration.TotalDays(DateTime.LocalNow() - row[Field D]) > 28
    )
in
    Result