Hi, I am trying to filter in Get & transform, using Text contains and text does not contain and I would like to base it on a table in Excel. How can I do this?
Thanks.
Hi, I am trying to filter in Get & transform, using Text contains and text does not contain and I would like to base it on a table in Excel. How can I do this?
Thanks.
standard procedure:
- load table into PQ
- make columns which you want to use as text
- add conditional column(s)
Upload sample workbook with enough data to demonstrate your need (and desired output).
Text.Contains([Column], "string") is probably useful for your need.
It will return True when column row contains "string". False if not. You can then set filter on True/False for the calculated column.
Alternately, you can set up parameter to check against, or set up Text Filter on the column.
0.JPG
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Capture.JPG
I don't know why but I am unable to upload a file...
To attach an Excel file to your post,
- desensitize data
- remeber that your example should reflect structure and type of data and contain the result what you want to achieve (manually created if necessary)
- click Go Advanced,
- scroll down until you see Manage Attachments,
- click that and select Browse,
- select your file and click Open,
- click Upload and you will see your attachment below Upload Files from a website
- click Close this window,
- click Submit reply
After that you should see attachment in your post
Attachment 573600
Thanks
Last edited by SHI.NL; 05-14-2018 at 03:53 PM.
So with your sample... you want only items that match both condition of matching [Text contains] AND [Text does not contain] ?
I.E. Return only "goodbye" & "hallo"
Here's how I would do it.
1. Load both tables as connection only.
2. In Table1 (Greetings) add custom column
=Table2
3. Add another custom column with following calculation.
This will return...Please Login or Register to view this content.
0.JPG
4. Filter on [Custom.1] = True and then remove [Custom] & [Custom.1], load result to sheet.
See attached.
Amazing! Thanks so much!!!!! Truly awesome!!!!
You are welcome and thanks for reps
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks