Custom Date Table trimming using desktop Power Que

[yerdaman]

Hi all

I have created a custom date table that has been uploaded to the Dataflow service that reflects our Company Accounting Periods. The table runs from 2009 to 2029 to ensue it covers most date-range requirements from the many Power BI apps that will source this date table.

My objective is to locally trim this large Dataflow table based on the transational requirements of the 'calling' Power BI. 

i.e. Start and end dates to reflect the transactional requirements of the particular model to aid date intelligence comparisons etc.

Desktop Power Query filters allow me to specify a hard-coded date yo delete unwanted rows but ideally I want to dynamically trim the start/end dates on each refresh based on the min/max date of another (transactional) table.

Any feedback would be really appreciated.

Thanks

[parry2k · Accepted solution]

@yerdaman  group your transaction table to get min and max date, covert it to list and then filter your date table from start and end date from the list (min and max), it should be pretty straight forward. let me know if you need further help.

[yerdaman]

Hi P2k

Thanks for your suggestion.

I know how to establish the MIN/MAX dates from the Transactional Data but not sure how to how to configure PQ to pickup these dates. As both the custom date table and the txns table are being sourced from Dataflow, my understanding is that this is refreshed in parallell .

I've viewed the advanced filtering in the date column but only managed to get this working by hard-coding the 'end date' (see below)

hard-coding highlighted

Any advice most welcome.

Thanks

[parry2k]

@yerdaman hi i put together quick solution for your reference, in attached I created a calendar and transaction table and calendar is filterd on min and max date from transaction. take a look at steps of calendar on how it is filtered.

hope it is helpful, you can make changes as per your need.

[yerdaman]

Fantasic - thank you for your swift solution P2K - much appreciated.

WIll now implement your logic into the model.

Cheers