Hello everyone!
I am new to PowerQuery but i am excited about the possibilities. But now I have a problem and I do not find a solution. Maybe I did not used the correct keywords 🙂 Sorry if a solution exists and I did not find it.
My datasource is quite big and consists of multiple Excel files. Every file has thousands of rows and I have 30-60 excel files (and raising). However, on the first step I would like filter every excel file to get hundert and not thousands of rows. The filter is not always the same. User have to change the filter without knowing how PowerQuery works. So another sheet in my excel file exist with one column. The column-header-name is "filter" and down below in every cell is one search term.
How can I use this "list" to filter my excel files so that the user can adapt the filter if needed? Thanks a lot for your help because a solution can push the efficiency of my PowerQuery Project a lot!
Hi Peter,
You can add a new column:
= Table.AddColumn(Source, "Custom", each List.Count(Splitter.SplitTextByAnyDelimiter(Table2[Keywords])([ColumnToSearchIn])) > 1)
Table2[Keywords] will obviously be your list of search terms, each search term will act as a delimiter, if one of the search terms (delimiters) is found in ColumnToSearchIn text, the text will simply be splitted. If there is no match, List.Count will return 1, if there is any match, List.Count will be higher than 1, this is the reason for >1 comparison, the result will be True or False.
In this version, it's case sensitive, so if you need case insensitive search, you need to convert both lists (with text and search terms) to lower case.
