I have a csv file which I load at the end of the month. This file contains all transactions from 2018 to the end of December 2019. In this file, some tickets may be re-open or closed let’s say at the middle of any month. We want to be able to load the changes when we do the next upload.
Now we want to load January 2020 data. What we have done is download all the data again from 2018 to 31st of January 2020, this will enable us capture all the changes that has happened within the period and replace the initial file from 2018 to December 2019. Then refresh the file in Power BI.
I am wondering if there is a better way of capturing just what has changed in the old file and just add any changes that has occurred in Power BI instead of replacing the file from old to new each month.
So is it possible for you to share few sample with all the columns that you have in your data.
Please delete any PI data if you’ve in the sheet but just attach the format of your data.
Don’t worry if you can’t but if you’re working only on excel then I think the only option you’ve in excel is being already followed by you.
But if you’re data goes into any database and then you fetch data from there, then you can ask your tech team to follow the approach of incremental refresh or SCD (Slowly Changing Dimensions) which helps you to make changes to your older data, if you’ve any unique ids or dates column for those accounts such as (last_updated_date) of that record.
Hi,
Depending on your requirements, i.e. whether you need to keep the first instance of an event or not, an append in power query and remove duplicates to keep the latest record, or a merge with some
logic to keep the records with a change should do the trick.
For one of my customers I set up to add
data on a weekly basis to his PBI model.
Sometimes a job from the previous week had not been invoiced and is repeated in the new data with the invoiced amount.
After append, sorted the query by date, (in the formula bar wrap the sort formula in Table.Buffer, so the latest instance will be kept) and remove duplicates.
Hi @Chukliz we’ve noticed that no response has been received from you since the 6th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
