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

0
votes

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

2
excelpowerquery
Don't use names_Values = names_Source{1}, instead use names_Value = Record.FromList(names_Source[Column1], names_Source[Column1]). You got the FieldSelector error because #"Filtered Rows1" refers to the wrong table: you want to filter on #"Filtered Rows", not on #"Changed Type" – Alejandro Lopez-Lago - MSFT
@AlejandroLopez-Lago-MSFT Ah, good catch on the field selector. I've made the changes you requested, now I'm receiving the 2nd error, We cannot convert the value null to type Text.. Is there a way to debug the query to see the exact point or field of failure? Again, thanks for the help. – Douglas Gaskell

2 Answers

1
votes

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.

0
votes

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