Text.Contains "Vlookup" in Power Query

[jaliu1290]

Hello Power BI community,

I am provided with a list WAREHOUSE that contains products with multiple SKU codes associated with each product.

WAREHOUSE

Name	Warehouse SKU	Supplier SKUs
Product 1	ABCDE	12345, 23456, 34567
Product 2	VWXYZ	45678, 56789

I then have a table BROCHURE where the product naming is different, but they are identified by one SKU number for each product. For each product, that SKU number will be one of the many codes in the preceding WAREHOUSE table.

BROCHURE

Name	SKU
Product A	12345
Product B	56789

My desired outcome would be to have a custom column in BROCHURE that returns "ABCDE" for SKU "12345" and "VWXYZ" for SKU "56789".

Splitting the columns in WAREHOUSE with delimiters is not ideal, I would prefer a solution within Power Query using text.Contains, sort of like a Vlookup with Excel using "*"&12345&"*".

Thank you in advance!

[VahidDM · Accepted solution]

Hi @jaliu1290 

You can achieve this in Power Query by adding a custom column to your BROCHURE table that looks up the corresponding Warehouse SKU from the WAREHOUSE table without splitting the Supplier SKUs column. Here's how:

Ensure both tables are loaded into Power Query:

• WAREHOUSE
  BROCHURE

In the BROCHURE table:

• Go to the Add Column tab.
  Click on Custom Column.

Create the Custom Column:

Column Name: Enter Warehouse SKU (or your preferred name).

Formula: Paste the following code:

= let
    MatchedRow = Table.SelectRows(
        WAREHOUSE,
        (WarehouseRow) => Text.Contains(
            WarehouseRow[Supplier SKUs],
            Text.From([SKU]),
            Comparer.OrdinalIgnoreCase
        )
    )
in
    if Table.RowCount(MatchedRow) > 0 then
        MatchedRow{0}[Warehouse SKU]
    else
        null

Explanation:

• Table.SelectRows filters the WAREHOUSE table to find rows where Supplier SKUs contains the SKU from the BROCHURE table.
• Text.Contains checks if the Supplier SKUs string includes the SKU value.
• MatchedRow{0}[Warehouse SKU] retrieves the Warehouse SKU from the first matching row.
  If there's no match, it returns null.

Finalize:

• Click OK to create the custom column.
• The new column will display the corresponding Warehouse SKU for each SKU in your BROCHURE table.

This method allows you to perform the lookup without splitting the Supplier SKUs column and effectively replicates the VLOOKUP functionality you're familiar with in Excel.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[ryan_mayu]

what's the expected output if ABCDE also have SKU 56789?

Name	Warehouse SKU	Supplier SKUs
Product 1	ABCDE	12345, 23456, 34567,56789
Product 2	VWXYZ	45678, 56789