Using a field from another query in a custom column


An issue I encounter often in Power Query is how to use a field in another query in a calculation.

My ‘go to’ is to merge the two queries to get the field I want and then create the custom column.

In the attached sample I want to calculate the age of the customer at the time they start a pledge. The Pledge Started Date is in the Pledges query and the customer date of birth is in the Customers query.

How can I create a custom column in the Customer query called Age without having to merge the two queries?

Sample File 24-02-2023.pbix (513.4 KB)
SampleData 24-02-2023.xlsx (135.3 KB)

Hi Jonathan,

You can use a custom column with a formula like this:

= Pledge[Pledge Started Date]{ List.PositionOf( Pledge[egCustomerNumber], [egCustomerNumber] ) }

I haven’t checked if your queries include a Customer Number. If yes, then your good, if not you would need another unique identifier. [That’s a benefit of merging where you can easily use several columns to create a sure match.]


1 Like

Hi Matthias

Thanks for the formula. It worked.

Interestingly, however, is that using the formula makes the report 5 times slower than the merging of the queries to obtain a field (I measured the report refresh time).

Maybe merging to get a field into a query isn’t such a poor option after all?

Hi Jonathan,
That is a very good feedback!
Actually I wondered why you asked for an alternative to merging.
Merging had the fame of slowing things down, but I read somewhere that it’s now quicker than in the past.
So I did answer your question, but from the performance point of view I’d recommend that you stick to merging.


Hi Matthias

Merging seemed clumsy, hence my enquiry.

Thanks for your feedback.

Jonathan, just wanted to let you know that your solution selection surprised me.