Daily additions to historic data file

[Anonymous]

Hi

I have a file with historic value of accounts by date column

I also run a daily update of the accounts which it contains, contained in two separate Excel files.

As such, I would like to add each day to the historic data file by adding a new date column with the updates from the two daily files.

This would then need to be exported / stored so the next run would include the prior day updates.

How would I implement this update/export facility within teh desktop version. I understand teh web version may have more powerfull tools for this but am using the Desktop licnce at the moment.

As always any halp is very gratefully received

Kind regards

[Anonymous]

@Icey

Hi

Sorry it took so long to come back but I was sorting out other data input issues.

If I understand takes the Historic file and adds values from new account files for all additional dates.

Over time this will involve the merge of a great number of files i.e. after a year 365 files woudl need to be merged with Historic.

What I would like to happen is for a automated run at the end of each day to:

 1 Merge Historic with the values from the account files which have been created that day

 2 Save/Export this updated historic file including the new column with the days updates

 3 Next day use this updated Historic file so only the next days newly created Account files need to be merged

I should be able to manage the file Merge portion pulling files from a folder with days date.

It is Exporting the file with the updates and setting it up to autmatically run at 23:50 each day that I am struggling with. 

I understand this is more straight forward not using the web tool but I only have experience of the Desktop so far. 

Really appreciate your support on this.

Kind regrds

Ian

[Kucrapok]

@Anonymous The way your excel file is created with dates in the columns, is it how your table in Power BI will look like?

[Icey · Accepted solution]

Hi @Anonymous ,

Please check the attached .pbix. It gives you what you want.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

wow thank you so very much Icey for not only taking all that time to devise a solution but them to build a working model and animation. It is really greatly appreciated. I have downloaded and will get my head around it over the weekend.

Many many thanks

Ian

[Anonymous]

Yep that is correct.

The two daily Excel files will be newly created each day with the date in their name.

Each day the historic excel file will need to create a new column for that day and populate with data from the two day files. If no new day file exists it just populates with prior days values.

Many thanks

Ian

[parry2k]

@Anonymous so basically what you are saying is the excel file will have an extra column every day and you want to make sure in power bi it keep all the column, let's starts with 4 column and next day there is 5th and then you see all 5 columns, correct?

[Anonymous]

yikes, ok let me try and explain thsi better having read the 'how to get help' blog ...

I have an Excel file with 2 years of historic monthly account balances, similar to this:

Account Name	30/09/2020	31/10/2020	30/11/2020	31/12/2020
A	58	4,058	58	58
B	110,695	15,544	9,469	10,209
C	1,018	1,018	1,018	1,913
D	154,912	154,912	154,912	154,912
E	2,172	2,172	2,172	5,195

At the end of each day, two separate excel files are created by other programs with the end of day balances.

One for example conatins the values for A /B and the other C/D/E.

I would like to update the historic balance file in Power BI each day, by adding a new column and populating it with the data from the daily files.  It would then look similar to this:

Account Name	30/09/2020	31/10/2020	30/11/2020	31/12/2020	01/01/2021	02/01/2021	03/01/2021
A	58	4,058	58	58	58	58	58
B	110,695	15,544	9,469	10,209	10,209	10,209	10,209
C	1,018	1,018	1,018	1,913	1,913	1,913	1,913
D	154,912	154,912	154,912	154,912	154,912	154,912	154,912
E	2,172	2,172	2,172	5,195	5,195	5,195	5,195

I would like to export/save this updated file with the latest date column. Each subsequent day it would be updated with a further date column and the latest account values. If no daily file is created for whatever reasons, then that days value would simply be set to the previous day. 

I hope this makes things a little clearer

Many thanks

Ian

[parry2k]

@Anonymous no idea what you are trying to achieve. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490