Hi, I am new to power query. I hope this makes sense.
I would like to create a power query function that looks up a value in another table (lets call it table2) and then brings that into the source table (lets call it table1) and performs some calculations. I am thinking joining tables might be the best way to handle this.
I would like to be able to use the function on different tables that I import. The Source tables could be different file types, xls, csv, etc and have different names. The problem I am having is defining the Source which I think is needed to use the Table.NestedJoin function?
Is there a way to define the Source as the Active Table? I guess what I am wanting to know is if there is anyway to omit z in "(col1 as any, col2 as any, z as table)=>let " I found a way to lookup values in another table using the List function without having to specify the Source table name, but the performance is really slow when I load it to the data model. I don't know why. Thank you.
Hi Jason,
Welcome to our forum! You're on the right track, you need to create a join between the two tables, which is the equivalent of a VLOOKUP in Excel terms. This is easily done using the Merge tables icon. From there you can perform the other steps like adding functions and cleaning the data. Here are some tutorials to check out:
https://www.myonlinetraininghub.com/excel-power-query-vlookup
https://www.myonlinetraininghub.com/vlookup-using-dates-in-power-query-using-list-functions
https://www.myonlinetraininghub.com/power-query-approximate-match-vlookup
Hope that points you in the right direction.
Mynda
Thank you for the links. I tried creating a custom function from the second link but it is really slow or doesn't work if I have a large data set (millions of records) so I think joining might be faster. I tried adding table.buffer and list.buffer but it didn't help.
I periodically get files that I need to process. Each file is different but all have the same two variables of interest in col1 and col2.
When creating a custom function, is there a way I can avoid having to put the "Source" when doing the Join or set it to the table that is currently active/open in power query.
I don't want this:
"(col1 as any, col2 as any, z as table)=>let " I want something like this: "(col1 as any, col2 as any)=>let " ActiveTable=Table that I have active in PQ MyJoin=Table.NestedJoin(ActiveTable, key1 as any, table2 as any, key2 as any, newColumnName as text, optional joinKind) I want to be able to create a Custom Column and be able to select col1 and col2 on any data that I get and import into power query.
