Matching Columns that contain the same text

[Syndicate_Admin]

I am using Power Query to consolidate web assets. I have a ist fo 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?

[Syndicate_Admin]

Hi! 

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

Find attached the file for your reference 🙂 

[Syndicate_Admin]

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 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 hnest 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 rmeove any unwanted space.

if (Text.Contains([sample_url], [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 contined in the URL

Apologies if I wasnt clear on the requirements.

[Syndicate_Admin]

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.

[Syndicate_Admin]

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.