Partial Vlookup in Power Query

[Syndicate_Admin]

Hi All,

I would like to write a step or steps in Power Query that will do the following;

1. Identify if the values in the [Originating Master Name] column contain a vendor name from my "Vendors" query.

2. If the value from the [Originating Master Name] column matches a vendor name, I'd like to create a new column in my "TB Expenses - Current Month" query to pull in the vendor name from the "Vendors" query.

The file I attached includes a sample of my data. The goal is to change "EPAM SYSTEMS - 0000" to "EPAM SYSTEMS" by referencing the vendor list included in the "Vendor Query".

[Syndicate_Admin]

Is there a way that we can modify the code so that the step only pulls exact matches as opposed to using the Text.Contians function? Thanks again, this is very helpful.

[Syndicate_Admin]

Hi All, 

Following up on my last message. Is there a way that we can modify the code so that the step only pulls exact matches as opposed to using the Text.Contains function? Thanks again, this is very helpful.

[wdx223_Daniel]

try this code

= let a=List.Buffer(Table.ToRows(Vendors)) in Table.TransformColumns(#"Changed Type",{"Originating Master Name",each List.Skip(a,(x)=>Text.Upper(_)<>Text.Upper(x{1})){0}?{2}? ??_})

[Syndicate_Admin]

Is there a way that we can modify the code you wrote so that the "Final Vendor" column will be pulled based on the "Vendor (All Caps)" value that is contained in the "Originating Master Name"?

NOTE: I added this "Final Vendor" column manually to the Vendors tab. You won't see it in the attachment I included.

[wdx223_Daniel]

if the final vendor is the 3rd column then

= let a=List.Buffer(Table.ToRows(Vendors)) in Table.TransformColumns(#"Changed Type",{"Originating Master Name",each List.Skip(a,(x)=>not Text.Contains(_,x{1},Comparer.OrdinalIgnoreCase)){0}?{2}? ??_})

[wdx223_Daniel]

no need the upper text step

= let a=List.Buffer(Vendors[#"Vendor (All Caps)"]) in Table.TransformColumns(#"Changed Type",{"Originating Master Name",each List.Skip(a,(x)=>not Text.Contains(_,x,Comparer.OrdinalIgnoreCase)){0}? ??_})