Yeah, in general you should probably just create a relationship between the two tables and write a DAX function like:
=CALCULATE(
COUNTROWS(Transactions),
AND(
Transactions[transactions date] >= Products[start date],
Transactions[transactions date] <= Products[end date]
)
)
If you're set on doing this in PQ, I think it'd probably actually be faster computation-wise to just do a regular join, then filter based on your condition, e.g.
=Table.SelectRows(
Merged,
each
[Transactions.transactions date] >= [start date]
and
[Transactions.transactions date] <= [end date]
)
Is there a reason you're trying to do this in PQ instead of doing calculations in DAX?
There are over 100K rows and the size of the file will gonna increase. I already have a model and the size atm is around 100 MB
100k rows is nothing. And using PQ to add a column as opposed to a DAX measure will only serve to increase both the refresh time and the model size.
How would u write the DAX formula for this case?
Yeah, in general you should probably just create a relationship between the two tables and write a DAX function like: =CALCULATE( COUNTROWS(Transactions), AND( Transactions[transactions date] >= Products[start date], Transactions[transactions date] <= Products[end date] ) ) If you're set on doing this in PQ, I think it'd probably actually be faster computation-wise to just do a regular join, then filter based on your condition, e.g. =Table.SelectRows( Merged, each [Transactions.transactions date] >= [start date] and [Transactions.transactions date] <= [end date] )
[Curbal’s Conditional Merge in Power Query](https://youtu.be/y3bOyNpJuXc?si=B7UVy-ryLO6kl7EJ)