Forum

Replace Values in P...
 
Notifications
Clear all

Replace Values in Power Query Using List Functions

4 Posts
2 Users
0 Reactions
217 Views
(@tanyamc)
Posts: 11
Active Member
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 A
ID        Email
15        [email protected]
Table B
ID        Email
15        null
Desired Result
Table B
ID         Email
15         me@gmail.com
 
This is simplified, there are several more columns in Table B (and Table A).

Thanks.
TM

 
Posted : 26/07/2022 10:59 am
(@catalinb)
Posts: 1937
Member Admin
 

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
(@tanyamc)
Posts: 11
Active Member
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
(@catalinb)
Posts: 1937
Member Admin
 

Sure, you can use List.Buffer on IDList and Emails lists, it should speed up processing.

 
Posted : 29/07/2022 4:09 am
Share: