Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

An OR filter on multiple columns

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.

Need Help
  • Nolock's avatar
    Nolock
    7 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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?

    • Nolock's avatar
      Nolock
      Resident Rockstar
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago
        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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      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.