Forum Discussion

yerdaman's avatar
yerdaman
Advocate I
7 years ago
Solved

Custom Date Table trimming using desktop Power Que

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

Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    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.

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    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's avatar
      yerdaman
      Advocate I
      7 years ago

      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's avatar
        parry2k
        Super User
        7 years ago

        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.