Need to create a time series from a snapshot excel file posted to sharepoint

[Virtual_Ames]

Hello community!

I have a need to be able to be able to analyze the performance of certain metrics over time. The data is fed into PowerBI from an Excel file that is posted to Sharepoint. Every so often, the Excel file is updated - but the file name remains the same (this allows us to maintain the query in PowerBI).

The question is - what is the best approach to be able to create a table in the data model that retains historic data using the created date of the excel file (or I can pull this from a date reference inside the excel file).  This would be a sort of "retain current data and append new data" routine.

Is this a solved issue?

[v-sihou-msft · Accepted solution]

@Virtual_Ames

In this scenario, you only have one source file which is always updated. Even you can add a date column in Excel to identify new and old data, we still can't have the merged dataset stored in Power BI desktop. Because we are always merging same "dynamic changing" dataset into initial dataset, but once we refresh data, the appended dataset will be updated into latest one.

So for your reqiurement, I suggest you insert the records in Excel file into a SQL table (by using either SSIS, bulk insert or bcp). Then just use that tabe as data source to generate report.

Regards,

[PowerDAX · Accepted solution]

Power BI data is in memory - not stored - so it cannot keep data in memory and append more data w/o having the original data in a data source of some type.

Can you not export the data with a timestamp in the file name?  You can configure Power BI to query a folder for all files within that folder dynamically and append the data into a single table.  In other words, today, a file "Beacon_05252016.xlsx" is exported to the directory and tomorrow, a file "Beacon_05262016.xlsx" is exported.  Power BI can query the folder for all Beacon* files and append all subsequent files to the original file.

https://www.youtube.com/watch?v=a7E29H5ZUmE

[samdthompson]

Slide a now() column into tthe excel file. Have done a similar thing and it workd a treat.

[Virtual_Ames]

Sam,

Yes - that will do it for step one - getting the time info from the excel file. 

The next steps are where I am having the problem - in PowerBI - how to tell the system to keep the current data, and append the new data to a table. 

I believe that if the source is updated, but has the same file name, PowerBI will attempt to overwrite all the data that I have. So I need the logic that says "SourceX.xls is the file, but when I hit refresh, keep the old data from SourceX as of Date1, and then read the new SourceX file and append the data you find in the table as of Date2".

The new table will now have Date1 and Date2 information in columns, and I can create the time visualizations from that.

Thoughts?

Brian

[v-sihou-msft · Accepted solution]

@Virtual_Ames

In this scenario, you only have one source file which is always updated. Even you can add a date column in Excel to identify new and old data, we still can't have the merged dataset stored in Power BI desktop. Because we are always merging same "dynamic changing" dataset into initial dataset, but once we refresh data, the appended dataset will be updated into latest one.

So for your reqiurement, I suggest you insert the records in Excel file into a SQL table (by using either SSIS, bulk insert or bcp). Then just use that tabe as data source to generate report.

Regards,

[Virtual_Ames]

Simon,

Thanks - I believe your recommendation may be the way to go - however, I want to clarify before I insert other steps into the workflow.

I believe that PowerBI should be able to handle a data beacon in some way. If not, I will post this as a suggestion.

Here is the clarification.

Imagine I have a data beacon - sending out data every few days - file name is always the same "Beacon.xlsx".  What I want PowerBI to do is simply look at the Beacon.xlxs file, and append (not merge, just append) the new infomration to a table. As if the system is not hitting "refresh" every time, but rather is an open connection, and is just receiving and appending new data.

In PowerBI, Beacon.xlxs would be populating a table called "Item Movement".  The table would be three columns:

"Item number", "Item position", "Date"

In my ideal solution, this "Item Movement" table will grow every time the beacon sends out the new data and it is received in PowerBI. 

The question are:

1) Is this related to "Enable Load" feature in some way? Can I tell PowerBI to not load the "Item Movement" table, and then have that act as my master table - this would then look at the new information from Beacon.xlxs and append the new data to "Item Movement"?

2) How does PowerBI handle real time feeds?

Thanks,

Brian

[PowerDAX · Accepted solution]

Power BI data is in memory - not stored - so it cannot keep data in memory and append more data w/o having the original data in a data source of some type.

Can you not export the data with a timestamp in the file name?  You can configure Power BI to query a folder for all files within that folder dynamically and append the data into a single table.  In other words, today, a file "Beacon_05252016.xlsx" is exported to the directory and tomorrow, a file "Beacon_05262016.xlsx" is exported.  Power BI can query the folder for all Beacon* files and append all subsequent files to the original file.

https://www.youtube.com/watch?v=a7E29H5ZUmE

[Virtual_Ames · Accepted solution]

PowerDAX,

This is an excellent response. Your approach works. 

The video explains the key elements - adding custom columns with Excel.Workbook([Content]) and Table.PromoteHeaders([GetExcelData.Data]).

The resulting combined table results in what amounts to an append. 

Greatly appreciated and keep up the good work.