Forum Discussion
Helper IVlookup with powerquery from another source
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 😞
New column in table 1
new column = maxx(filter(table2,left(table1[Name],len(table2[naming]))=table2[naming]),Table2[value])
5 Replies
- amitchandak
Super User
New column in table 1
new column = maxx(filter(table2,left(table1[Name],len(table2[naming]))=table2[naming]),Table2[value])
RudyiHi ,
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
Community Support
Hi,
If my answer has solved your solution, please mark it as a solution for others to see.
Thanks!
Best Regards,
Giotto Zhi
- 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:
Hope this helps.
Best Regards,
Giotto Zhi
- 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.
