Forum Discussion

Helper I

1 year ago

Solved

Filtering Rows by List - no results with named list

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: ...

Need Help

OwenAuger
1 year ago
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

Helper I

1 year ago

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.