Using a field from another query in a custom column

Power Query
1

Hi

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?

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

2

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.]

Regards,
Matthias

1 Like
3

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?

4

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.

Regards,
Matthias

5

Hi Matthias

Merging seemed clumsy, hence my enquiry.

Thanks for your feedback.

6

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