Filtering Rows by List - no results with named list

[TuqueLogic]

Trying to select rows by using List.Contains by a named list, CreationAnswers.

The code using the list's name takes a long time to run the query and returns an empty table.
IE this doens't work:
= Table.SelectRows(#"Changed Type", each List.Contains( CreationAnswers, [AnswerID]) )

When I subsitute the list values (copy/paste them from the list in the query and add commas between) it returns the table filtered and works pretty much instantly.
This works:
= Table.SelectRows(#"Changed Type", each List.Contains( {5221, 5200, . ..  , 2964 }, [AnswerID]) )

As the explicit way works fine, I figure I have something wrong with the list. But I don't know what.
The list was multi row table loaded from a CSV, the columns were stacked by
= List.RemoveItems(List.Combine(Table.ToColumns(Source)), {""} )
And then the non integer values (blanks, previous column headers) removed.

I dont' know if it's a data type issue as I can't find how to verify the data type of the list or set the data type of the list.
Options are greyed out when the list is selected in the PowerBI menu and I can't find a code example for setting a list's type.
Only found ways for a "list" that is a column as part of a table. (I'm new to Power Bi/Power Query)

But this may not be the issue. idk. I

I want to be able to update the filter list in the source file not in the query code.
Any advice on why it works when explicitly typing the list vs using the named list?

Help is appreciated.

[OwenAuger · Accepted solution]

Hi @TuqueLogic 

From your description, it does look to be a data type issue. The list contains text while the AnswerID column is a number.

There are a few ways to deal with this, but I would probably handle it by changing this:

= List.RemoveItems(List.Combine(Table.ToColumns(Source)), {""} )

to this:

= List.Transform(List.RemoveItems(List.Combine(Table.ToColumns(Source)), {""} ), Int64.From)

This transforms each list item to an integer. You could also likely use Number.From rather than Int64.From.

If you need to handle errors, you could convert invalid integers to nulss with something like:

= List.Transform(List.RemoveItems(List.Combine(Table.ToColumns(Source)), {""} ), each try Int64.From(_) otherwise null)

Does this work for you?

[TuqueLogic]

Thanks so much.

Both worked. I used the error handlign options as it simplified the cleaning to List.RemoveNulls()

Is there are good resource for the symantics/syntax for power query?
The MS data types page makes no mention of what suffixes are available or when to use which ones.
Running into these types of issues a lot in the documentation.
I want to understand the how/why of the code as much as the what to use. The docs are a specific 'what' much of the time.

[OwenAuger]

You're welcome. Glad to hear you got it working! 🙂

I would highly recommend Ben Gribaudo's Power Query M Primer:

https://bengribaudo.com/power-query-m-primer

Otherwise, parts of the official documentation may be useful:

https://learn.microsoft.com/en-us/powerquery-m/power-query-m-language-specification