CODEWITHSUNDEEP
excelpowerquery
Douglas Gaskell
Douglas Gaskell

Reputation: 10080

How to filter a column by a range with Power Query?

I want to filter a Power Query column by a range of values. I see that I can manually select or deselect values from the column, but I need to do this automatically absed on a range of values already in the sheet.

Let's say the table I'm querying contains 100 unique names. I only want to import rows that match a list of 20 names I have in my sheet.

How can I do this with Power Query?

Edit: I can make an excel function to concatenate a list of names into the format needed for the query like this: (= Table.SelectRows(#"Changed Type", each ([Ticket Assignee] ="Name 1" or [Ticket Assignee] ="Name 2"))). But still need a way to reference this from the query.

Edit2:

My query:

This errors out with:

Expression.Error: We expected a FieldsSelector value.

let
    names_Source = Excel.CurrentWorkbook(){[Name="namesTable"]}[Content],
    names_Values = names_Source{1},


    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Ticket Assignee", type text}, {"# Solved", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Ticket Assignee] <> null)),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each Record.HasFields(names_Values, [Ticket Assignee]))
in
    #"Filtered Rows1"

Edit3: Modified Query:

Expression.Error: We cannot convert the value null to type Record. Details: Value= Type=Type

let
    names_Source = Excel.CurrentWorkbook(){[Name="namesTable"]}[Content],
    names_Values = Record.FromList(names_Source[Zendesk Name], names_Source[Zendesk Name]),


    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Ticket Assignee", type text}, {"# Solved", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Ticket Assignee] <> null)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Record.HasFields(names_Values, [Ticket Assignee]))
in
    #"Filtered Rows1"

Edit4: My table has blank values that were erroring out as null. I added a line to filter out the null values from my names source

Upvotes: 1

Views: 5627

Answers (2)

Max Zelensky
Max Zelensky

Reputation: 171

Why don't to use join operations? It seems like it works at least not slower, but more clear, in my mind:

let
    names_Source = Excel.CurrentWorkbook(){[Name="namesTable"]}[Content],
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Joind = Table.NestedJoin(Source,{"Ticket Assignee"},names_Source,{"Zendesk Name"},"NewColumn",JoinKind.RightOuter),
    Filtered = Table.RemoveColumns(Joind,{"NewColumn"})
in
    Filtered

Upvotes: 1

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4144

If the list of items you want to check against is in an Excel worksheet, then you can import the table using From Table and use List.Contains to see if your values are in that table. If the worksheet table is in WorksheetQuery and the names are in the column Names, then your step would look like:

= Table.SelectRows(PreviousStep, each List.Contains(WorksheetQuery[Names], [Ticket Assignee]))

If that ends up being too slow, you can try to convert the column into a record and then using Record.HasFields instead.

Upvotes: 2

Related Questions