Hi,
I have code that works great in excel, but it does not work in power query and I would like to do the work in power query.
The excel code is:
=IFERROR(TEXTJOIN(", ",1,FILTER(Products!$A$2:$A$316,ISNUMBER(SEARCH(Products!$A$2:$A$316,@[Products])))),"")
The code adds a new column that extracts data from another product column in the same table based on a list of products.
For instance:
1x KlimeWallz, 1x Travel fee will be Klime Wallz, Travel Fee in the new column. The words "Klime Wallz" and "Travel Fee" are separate rows in ProdList.
I have tried a few things in Power Query, but I am missing something.
I have attached an excel file with example data.
Thank you so much!
Cindy
The attached file contains two possible PQ solutions. One is rather crude but works just fine and is easy to follow. The other is a bit more advanced, though will not work if you have search words that are included in other search words. For example, if you have Tent and Tent Pole in the search list and your order table contains "1x Tent" both Tent and Tent Pole will be returned. Your product list doesn't seem to have that problem though.
By the way, I removed all your queries as they connected to local files and disturbed the refreshing process. So, see which method suits you best and apply it to your own file.
Dear Riny, Thank you! I should have cleaned up the file, so thank you for doing that. I have to remember cleaning up with Power Query going forward. And the solutions are both great and give me the opportunity to learn more!
Glad I could help!
