Forum Discussion
Power Query Filter column on other column
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!
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.
7 Replies
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 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.
Anonymous what is the logic to filter? if any id has nan then filter that id?
Anonymous did the provided solution work?