Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
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
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |