Multiple column lookup OR "phrase" lookup in PBI

[IoannisT]

Hi Gurus

Could you please help me with the following?

I would like to, essentially, h_lookup on a PBI from one table to another table.

Reason being is that some drivers manually type the client's name diffently and this breaks our reporting as currently someone has to manually edit line by line every entry so our quertelry graphs dispaly the correct information.

I appreciate that data should be edited as upstream as possible and we have discussed the posibility of changing the free text to a drop down but for operational reasons we cannot at the moment.

What could happen is, on a weekly basis, someone from the office goes through the entries and manually adds different alliases to a unique Client name. My only problem so far is the PBI only accepts a column and even if I pre-program i,.e. 40 columns, in the future there might be more added so I dont want to add on PBI a new line 

I am open to any recommendation you have, excel, shrepoint list.... 

NameShown	alias1	alias2	alias3	alias4	alias5	alias6	…..aliasN
AlphaBravo	Alpha bravo	aplha bravo	AB	A-B	Alpha-Bravo	Alpha-bravo	
MickeyMouse	MC	MickeyM	MkeM	mouse			
Contonso	Con Tonso	contonso	Contoso Europe	Contonso Americas	C-EU	C-AM	Ctnco

CaseID	Company	InvoiceID	Total (VAT included)
asc2344	Alpha-Bravo	a1	100
djkldsn645	AB	a2	250
vnkldsfn754	C-AM	a3	7000
dhswte2	Ctnco	a4	800
5j3nrekb	MkeM	a5	150
56y7456jkl	MC	a6	500

I have tried multiple time to unite all drivers and agree 

[NaveenGandhi · Accepted solution]

Hi @IoannisT 

If you maintain the lookup table(Excel list you maintain) then the below solution will work. Have a look at my Power query steps and try to replicate it. What is does basically is search for the current in the lookup table (i.e searches for "MKeM" in lookup table and gives back the Nameshown "MickeyMouse")

Let me know if this will work or any questions in implementing this.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!

[IoannisT]

Thank you so much Naveen

Works like a charm.

Kudos and accept solution!