cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

How to collect data from a spreadsheet everytime it is updated

Hi, can anyone help.

 

I have a spreadsheet in Excel which records the status and if there are any faults in some of the rooms I look after at work.

 

I then have that connected to Power BI to produce charts analytical visuals.

 

What I now want to do is keep a history, so everytime I update the spreadsheet the data is added to the table so if I want to go back to a particular date in the past I can.

 

I am not sure if the best way to do this,

 

any help would be appreciated!

 

PS. I am open to doing this in google sheets if easier.  I am not able to use an VBA as it needs to work on a mobile device. 

1 ACCEPTED SOLUTION
Solution Specialist
Solution Specialist

How does the data end up in the Excel sheet in the first place? Do you expect Power BI to somehow be involved in keeping track of the history, or is it perhaps easier to adjust whatever mechanism updates the Excel sheet so that it appends information rather than replaces it? If it is not too complicated that's what I would try.

 

Power BI does not currently have a mechanism for "appending" data to keep track of history. There is a workaround however. I have successfully used the suggestions in this post, however there is some work involved in setting it up: http://community.powerbi.com/t5/Desktop/Storing-and-using-information-from-a-dynamic-data-source-usi...

 

 

 

 

View solution in original post

3 REPLIES 3
Solution Specialist
Solution Specialist

How does the data end up in the Excel sheet in the first place? Do you expect Power BI to somehow be involved in keeping track of the history, or is it perhaps easier to adjust whatever mechanism updates the Excel sheet so that it appends information rather than replaces it? If it is not too complicated that's what I would try.

 

Power BI does not currently have a mechanism for "appending" data to keep track of history. There is a workaround however. I have successfully used the suggestions in this post, however there is some work involved in setting it up: http://community.powerbi.com/t5/Desktop/Storing-and-using-information-from-a-dynamic-data-source-usi...

 

 

 

 

View solution in original post

Ok, so I ended up using Google sheets with a script with triggers to run every day.

 

published to the web as an xlsx

 

A few simple queries to make sure the data did what I needed it to do and I got the result I needed.

 

Thanks Erik for pointing me in the right direction! 

Thanks Erik, think I will take a look at the sheet to do it in that case.

 

Will have a look at the suggestions too.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors