Power Query Filter column on other column

[Anonymous]

Hi all,

is there a way to filter a query on a column, based on the value of another column in PowerQuery?

In my case, I would like to remove a series if it contains any Nan.

Here's an example of the input:

and the desired result:

I have tried with workaround, like filtering a custom column that sums up all the elements according to their value in column 1 (like a sumif in excel), but the dataset is too heavy, and this results to be very very slow...

#"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Rimosse colonne", "Custom Column", each let _item = [Column 1] in
        List.Sum(
            Table.SelectRows(#"Rimosse colonne", each [Column 1] = _item)[Column 2])),
    #"Filtrate righe" = Table.SelectRows(#"Aggiunta colonna personalizzata2", each (not Number.IsNaN([Custom Column])))

Is there a simpler way?

thanks!

[parry2k]

@Anonymous glad it worked out. Yes, I was not sure about the actual data so just used Null.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[parry2k]

@Anonymous did the provided solution work?

[Anonymous]

yes!

Thanks a lot

[parry2k · Accepted solution]

@Anonymous you can try this, use group by and then call a function to flag

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTKxVIrVgTCNzOFMMCMJyDA1hTPNzOFMCwgzGaYShWEGMTEFZLgJnAk1HMQ0U4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"ALL", each _, type table [Column1=nullable text, Column2=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fnFlag([ALL])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column2", "Exclude"}, {"Column2", "Exclude"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Exclude] = "No")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"ALL", "Exclude"})
in
    #"Removed Columns"

code for the fnFlag function:

(tbl as table) =>
let
    Source = tbl,
    FilterNull = Table.SelectRows(Source, each [Column2] = null),
    RowsCount = Table.RowCount(FilterNull),
    AddFlag = Table.AddColumn(Source, "Exclude", each if RowsCount >= 1 then "Yes" else "No")
    
in
    AddFlag

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

Great!

it worked perfectly, thanks a lot!

I just had to change the condition in the fnFlag function

FilterNull = Table.SelectRows(Source, each (Number.IsNaN([value])))

[parry2k]

@Anonymous what is the logic to filter? if any id has nan then filter that id?

[Anonymous]

yes, that was exactly the logic, thanks!