Forum Discussion
Matching Columns that contain the same text
I am using Power Query to consolidate web assets. I have a list of domains and a list of target URLs they go to and I want to mark them of they match. For example taking two columns
if URL = https://www.mydomain.com contains Domain = mydomain.com
would be considered a match because URL contains Domain
if URL = https://www.someotherdomain/mydomain contains Domain = mydomain.com
would not be considered a match because URL contains only part of the domain
and maybe a more complex variant if its possible:
if URL = https://www.mydomain.com contains Domain = mydomain.com and Status = 200
Is it possible to match in this way using M in Power Query?
Hi IanDavies ,
Please try:= if Text.Contains(Text.Upper([sample_url]),Text.Upper(Text.Trim([domain])),Comparer.OrdinalIgnoreCase) and [Final Status Code] = 200 then "yes" else "no"If that still doesn't work, consider providing sample data that is not privacy related.
How to provide sample data in the Power BI ForumBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
5 Replies
Hi!
You can add a custom column that evaluates that logic. See example:
Find attached the file for your reference 🙂
Thanks for the reply, but this means that I have to manually type the comparison, the question I thought I'd asked is if I could check if the contents of column URL contained the contents of column Domain dynamically not just by typing the text in the formula
I need to do this across 900 domains, that's a lot of "if" statements if I have to type everyone manually. To be honest it would be quicker to do the check manually in the spreadsheet.
I tried this:
if (Text.Contains([sample_url], [domain])) and [status code] = 200 then "yes" else "no"It is accpeted but returns all negatives - I can vsiaully see there are some that match so I added a comparer just in case it was case causing an issue and trimmed the domain column to remove any unwanted space.
if (Text.Contains([sample_url], (Text.Trim([domain])),Comparer.OrdinalIgnoreCase)) and [Final Status Code] = 200 then "yes" else "no"But every attempt just returns all negatives (no) even where the text is clearly visible. The only thing I've noticed is that I am checking for text in the middle of a string, not checking for a complete string. The URL looks like this, I am trying to match only the highlighted section. Would the / at then end casue any issues?
https://www.mydomain.com/
Apologies if I wasnt clear on the requirements.
No problem! Agree, you should not do it manually. Your second approach is what I would've thought so I'm not sure why it didn't work :/.
Try uppercasing both columns to see if there's a difference or leverage from the Add Column From Examples to see if Power Query can give a new formula.
