Real time data with excel files in sharepoint

[PowerBIExplorer]

Hi, 

My team uploads a new monthly excel file into sharepoint which needs to be imported into Power BI as a regular matrix/table as a report.

I need to find a way to automate the following process:

1) Excel report is uploaded into a Sharepoint folder

2) Power BI connects to the sharepoint folder and is able to recognize the newly uploaded monthly excel file. Power BI gets the newly uploaded excel report data and imports the data 

3) Power BI recognizes columns and automatically creates a full table/matrix with all fields/columns/attributes

4) PowerBI will schedule a refresh on a monthly basis and create a new tab to the same existing report everytime a new excel file is uploaded to the sharepoint folder

The real data has over 60 columns with over 100,000+ rows so it would be quicker to automate this process. I believe Logic apps or microsoft flow might play a role but I could be wrong since Power BI might already have this capability 

[opticshrew · Accepted solution]

Hi @PowerBIExplorer

So quick answer is no.... this isn't something that has been built into Power Bi yet however there are work arounds which could help.

Data Lake Store is an Azure offering where you can upload multiple documents (CSV) and then combine them using Azure Data Lake Analytics. ADLA uses U-SQL to combine your docs to present a single CSV which can then be imported into Power Bi.

You could then use the query editor to manipulate this data to only show the last month.

Process

1. Create Azure Data Lake Store Account
2. Setup Azure Data Lake Analytics
3. Upload your CSV/Excel
4. Create ADLA job to automaticaly combine your documents (every day/month/year... you get the idea)
5. Set the output to be a folder in Azure Data Lake Store
6. Connect to ADLS using Power BI
7. Using the query editor only pull data for a specific month
8. Visualise

If you don't want to do it this way, the only other feasable method would be to update a single excel doc each month.

Also your title suggested you wanted to do something with real time, once reading through this no longer seems the case. If you did want to go down this route you'd be looking at streaming analytics or possible using Direct Query to lets say a SQL database/table.

Hope this helps,

J

[Anonymous]

@PowerBIExplorer,

Power BI can't automatically create new report page for you every time a new excel file is uploaded to sharepoint folder.

In your scenario, you can publish report from Desktop to Power BI Service, and set schedule refresh to the dataset. This way, once a new excel file is uploaded to sharepoint folder, the new rows of the excel file will be automatically added to the existing Matrix/Table visual based on refresh schedule.

Regards,
Lydia