Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ianhan13
Helper III
Helper III

Daily additions to historic data file

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

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @ianhan13 ,

 

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

combine files.gif

combine.gif

 

 

Best regards

Icey

 

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

View solution in original post

8 REPLIES 8
ianhan13
Helper III
Helper III

@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
Helper I
Helper I

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

Icey
Community Support
Community Support

Hi @ianhan13 ,

 

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

combine files.gif

combine.gif

 

 

Best regards

Icey

 

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

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

ianhan13
Helper III
Helper III

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
Super User
Super User

@ianhan13 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?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

ianhan13
Helper III
Helper III

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 Name30/09/202031/10/202030/11/202031/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 Name30/09/202031/10/202030/11/202031/12/202001/01/202102/01/202103/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
Super User
Super User

@ianhan13 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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.