How to make a column that only shows data if the cell value is contained in the referenced column?

Question

Hello, I am looking to use Power Query to do the following:

I have two columns I'm comparing: Column A and Column B

I want it to be so that IF the cell value for a given row in Column A is contained anywhere within Column B, then display the row. ELSE, do not display the row. How would I do this. If it is not possible using Power query, then is there an alternate solution in Excel?

Answer 1

Excel 365 Pro Plus with Power Query.

Compare columns A/B

https://www.mediafire.com/file_premium/sk3gy0txg764x1p/02_25_22.xlsx/file

https://www.mediafire.com/file_premium/2nzs0tdeneflfvw/02_25_22.pdf/file

Answer 2

Hi @KING, Dylan

Welcome to Q&A forum~

As Power Query is not supported on Q&A at current time, it's best post a new thread on Microsoft Power BI community.

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

For an alternate solution in Excel, I would suggest you try formulas.

I will make a simple sample as following image.

Then please add a helper column to find out cells of A column contained witin B column.

=IFNA(VLOOKUP(A2,B$2:B$7,1,),"")

Then please enter INDEX($A$1:$B$7,SMALL(IF($D$1:$D$7<>"",ROW(D$1:D$7)),ROW(A1:A1)),COLUMN(A$1)) formula, drag it right and down until you find the "#NUM!", this action can get the rows that meet your requirement.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 3

Hi,

This M code in Power Query works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Custom1 = Table.FromList(List.Select(Source[Name],each List.ContainsAny(Source[Name1],{_})))
in
    Custom1