Forum Discussion

Vandergledison's avatar
Vandergledison
Helper III
4 years ago

automaticly append 2 tables

Dear all,

i have a table that gets an update every year.

i already appended both of them. In the old i filter the years 2017-2021 (e.g) and in the new one i filter 2022-2028 (the new one always come with 5 years or more in the future (forecasting).

After appending them, i get a final version containing the data from 2017 till 2028.

This new one should now be the one that i would use as an "old one" for the next year.

Is there a way so i could automate the whole process? like when i add the new query data to gater the data untill previous year and add only the current and future years?

thanks a lot

the link contain an extract of how the table looks like (they are always the same format)

Table example 

Need Help

2 Replies

  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    Impactful Individual
    4 years ago

    Hi Vandergledison,

     

    My understanding is you have 2 tables, both applied filter and mutually exclusive. You want to automate them so you don't have to change the filter.

     

    Questions:

    1. Are you using Power BI or Excel?

     

    Solution:

    1. If you're using Power BI, then you can use the incremental load as per ImkeF

    2. If you're using Excel, you can achieve it with an Input table.

    3. Add a formula to each query prior appending them.

        e.g. Table 1: Table.SelectRows(#"Previous Step", each [Column - Year]<Date.Year(DateTime.LocalNow()) )

               Table 2: Table.SelectRows(#"Previous Step", each [Column - Year]>=Date.Year(DateTime.LocalNow()) )

     

    Regards

    KT