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. Howeve...
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
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.

    • Nolock's avatar
      Nolock
      Resident Rockstar
      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