What is a better method (iterate a list?) than a PQ conditional column using "contains" ???

[Purplehazed]

I import banking data into Power Query (PQ).

One column shows the vendor in a messy string,
example 1) 0000123456 HomeDepot xyzHD store 1234.
example 2) 1234000000 Lowes xyzstoreHBG12

I've been adding a conditional column in PQ that says, if column x contains HomeDept then insert "Home Improvement" in the new column.
If column x contains Lowes then insert "Home Improvement" in the new column.

The conditional column list is has gotten very long... There has to be a way to tell PQ to iterate an external source (an imported table) and search for words in a list and create/add to a conditional column
with having to go through the clunky method I described above...

What is the better way?

Thanks!

 

[etaf]

you could create a list to lookup
BUT will the name ALWAYS be after the 1st SPACE and the end of the name be the 2nd SPACE
=VLOOKUP(TRIM(MID(A2,FIND(" ",A2,1),FIND(" ",A2,FIND(" ",A2)+1) - FIND(" ",A2,1) )),H:I,2,FALSE)

Column B,C,& D are there just to show the formula in Action, all you need is column E and the lookup table in column H & I

Book3
	A	B	C	D	E	F	G	H	I
1		First space	secondspace	Lookup	vlookup Table			TABLE
2	0000123456 HomeDepot xyzHD store 1234.	11	21	HomeDepot	Home Improvement			HomeDepot	Home Improvement
3	1234000000 Lowes xyzstoreHBG12	11	17	Lowes	Home Improvement			Lowes	Home Improvement
4
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=FIND(" ",A2,1)
C2:C3	C2	=FIND(" ",A2,FIND(" ",A2)+1)
D2:D3	D2	=TRIM(MID(A2,FIND(" ",A2,1),FIND(" ",A2,FIND(" ",A2)+1) - FIND(" ",A2,1) ))
E2:E3	E2	=VLOOKUP(TRIM(MID(A2,FIND(" ",A2,1),FIND(" ",A2,FIND(" ",A2)+1) - FIND(" ",A2,1) )),H:I,2,FALSE)

 

[Purplehazed]

Thanks etaf!!!

I will give your suggestion a try and it may be a solution but the point you raised about exact spacing will likely cause a problem...

The snippet below is literally the PQ applied step adding a conditional column....

Take a look at the BP#24 Value. BP#24 is the bank export from buying gas at a British Petroleum station.

If I go to the BP station three times I may get three different exports from the bank, examples below;
- BP#24
- BritPetro Smith St.
- BPetro#24pump3

I want all three to output "Auto Gas" in the output column.

I have had to create a PQ add clause step three times using three different (unique identifies from above in Blue) all outputting "Auto Gas" in the output column.

Even if I have to keep adding unique identifies because the exported data changes (see blue above) I would like to do so with a better method that creating 1,000 add clauses (see snippet below)

Is there a way to create a PQ applied step that in human words says, "go iterate an excel list located <here> and if you find x in the list, insert y in the output, if you find a in the list, insert b in the output...

I want to bypass the PQ Add Clause steps.

I know there is a way, just don't know what it is yet

Thanks for the input!

 

[etaf]

Sorry I dont know how to improve that without a list of possible outputs - I guess if you know *BP* will cover all the Auto Gas , BUT not also be some other debit, thats the issue when it gets down to being just a couple of characters.
I have the same issue doing my home accounts , with a pivot table off the bank statement CSV output

 

[alansidman]

You might be able to modify this to do what you want

Bulk Find And Replace In Power Query | How To Excel

www.howtoexcel.org