Excel FILTER function not working with Table transformed and loaded in Power Query

Question

Hi Everybody,

I have operated a series of transformations and queries in Power Query (from original table in Excel), and then loaded the result in an Excel worksheet. The result is a table (t_Values) with two columns (Number, Title). The [Title] column contains text.

In the same worksheet, on the right side of the Table, but outside of the Table, I want to Filter some values of the [Title] column. In the cell L2 I wrote the value to Filter for. Following some tutorials, I applied the following formula:

=FILTER(t_Values, t_Values[TITLE]=L2)

No matter how many times I try, the result is always #CALC!

I even tried to transform the Table to Range, but no positive outcome.
I've verified that the content of the Table is text with =ISTEXT --> TRUE

What am I doing wrong?
Are Excel FILTER function and Power Query loaded Tables compatible?

Thank you in advance.

Frank

Answer 1

Try this:

=FILTER(t_Values[TITLE],ISNUMBER(SEARCH(L2,t_Values[TITLE])))

Answer 2

Filter records with "Roblox" with Power Query.

https://www.mediafire.com/file_premium/yfuw1b6i07wi1ym/07_24_25.xlsx/file

https://www.mediafire.com/file_premium/6h6eogh1dbsp7cl/07_24_25.pdf/file

Answer 3

Hi,

In cell F2, enter this formula

=FILTER(A2:A5,REGEXTEST(A2:A5,D2,1))

Hope this helps.

Answer 4

I summarize my mistake and the solution:
I was trying to filter a column containing a string (sentence), with only a part of the string (incomplete string or sentence). The FILTER function returns TRUE only if the exact text is found inside the filtered column, otherwise it returns FALSE. I cannot filter "SURVIVE" if the cell contains "SURVIVE ...".
The solutions are as suggested above. Another alternative solution would be to split the column and trim each word, then FILTER by the column containing the desired word.