How to collect data from a spreadsheet everytime it is updated

[LouisLouis84]

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. 

[erik_tarnvik · Accepted solution]

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...

[LouisLouis84]

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! 

[LouisLouis84]

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.