cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vandergledison
Helper III
Helper III

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 

2 REPLIES 2
KT_Bsmart2gethe
Super User
Super User

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

ImkeF
Super User
Super User

Hi @Vandergledison ,
not sure I understand your request, but maybe you're looking for incremental refresh: Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Docs

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors