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)
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.
1. Are you using Power BI or Excel?
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()) )
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
Click here to read more about the November 2022 updates!
Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.