Matching Columns that contain the same text

[IanDavies]

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?

[ray_aramburo]

Hi! 

You can add a custom column that evaluates that logic. See example:

Find attached the file for your reference 🙂 

[IanDavies]

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.

[Anonymous · Accepted solution]

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 Forum

Best Regards,
Gao
Community Support Team

If 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

[IanDavies]

I have marked this as the solution although it has not resolved my issue. Nonetheless if I  follow your example with manually entered data rather than in my more complex merged data it works perfectly. 

I am guessing that there is something in the merged data that is not visible or immediatey apparent that causes the comparison to fail. I'll continue to troubleshoot using yout formula now I know that it works on clean data.

Unfortunatley I dont think I can post the real data that isnt working, it would identify the business I am working with along with some other information about their online presence.

Thank you.

[ray_aramburo]

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.