Notifications
Clear all
Power Query
4
Posts
2
Users
0
Reactions
217
Views
Topic starter
I need to fill in some holes in a query, but only a few so I don't want to use merge. I'd prefer to use lists with replace values function.
I have watched "VLOOKUP in Power Query Using List Functions" video but that is related to add columns.
I have two queries, and I just need to fill in nulls in one column (email) based on an ID that is in both queries. I am looking for a one-step solution 

Table B
ID Email
15 null
Desired Result
This is simplified, there are several more columns in Table B (and Table A).
Thanks.
TM
Posted : 26/07/2022 10:59 am
Hi Tanya,
Here is the one step you wanted:
Custom1 = Table.FromRecords(Table.TransformRows(#"Changed Type",(rec)=>Record.TransformFields(rec,{"Email", each try if rec[Email]=null then Emails{List.PositionOf(IDList,rec[ID])} else rec[Email] otherwise null })))
Posted : 28/07/2022 12:54 am
Topic starter
Thank you! I will reuse this a lot!
Should I also have a list buffer if there is a high row count?
Posted : 28/07/2022 10:12 am
Sure, you can use List.Buffer on IDList and Emails lists, it should speed up processing.
Posted : 29/07/2022 4:09 am