Vlookup with powerquery from another source

[Rudyi]

Hi all,

I'm quite new to power query and I'm trying to do I query out of my league. 

I have one table with column of names

Table1

Name

AABC

BBCD

CCDE

and second table with naming conventions

Table 2

naming	value
AA	1
BB	2
CC	3

The result should be like

Name	Value
AABC	1
BBBCD	2
CCDE	3

So for each entry in table 1 I want to go through all rows in table 2 and get the value field if Table1.Name starts with Table2.Naming and "not mapped" if nothing found . Let's assume that values in Table 2 are unique. 

Any help appreciated! Can't even google this task correctly 😞

[Ashish_Mathur]

Hi,

While on Table1, go to the Query Editor and split the column by position to extract the first 2 characters in another column.  Then merge this table with Table2 based on the new column created in Table1.  From Table2, bring over the value column to Table1.

Hope this helps.

[v-gizhi-msft]

Hi,

Please try this measure:

Measure = 
var a = IF(LEFT(MAX('Table 1'[Name]),LEN(MAX('Table 2'[naming]))) in FILTERS('Table 2'[naming]),LEFT(MAX('Table 1'[Name]),LEN(MAX('Table 2'[naming]))),0)
return
CALCULATE(SUM('Table 2'[value]),FILTER('Table 2','Table 2'[naming]=a))

The result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[amitchandak · Accepted solution]

New column in table 1

new column = maxx(filter(table2,left(table1[Name],len(table2[naming]))=table2[naming]),Table2[value])

[Rudyi]

Hi ,

@amitchandak is it possible to do the same on the query level somehow? Same function gives me an error in query editor 😞

@v-gizhi-msft thanks, your solution works as well!

[v-gizhi-msft]

Hi,

If my answer has solved your solution, please mark it as a solution for others to see.

Thanks!

Best Regards,

Giotto Zhi