Forum Discussion
Aggregate Multiple Excels and Utilize Write-back
Hello,
I am rather new and plan to struggle a little but that’s part of the fun. In the description below, I don’t know how complete each step but the purpose of this post is for some general direction as to how to set this up.
My company has multiple ERPs that don’t talk to each other. Each division will send me inventory reports that are in an Excel format from their ERP at certain intervals. I must build a Power BI that merges the different reports using the most recent report. I learned how to put Excel files into a folder and using only the latest version of that file by following these steps in the following link: (https://wkrzywiec.medium.com/getting-data-from-the-latest-file-in-a-folder-using-power-query-51dfa4bff711)
I also want to keep notes about items using Write-back functionality.
- Where is the best place to store the Excel files? I have OneDrive and Sharepoint available.
- Can you tell me if there is a way to utilize Write-back functionality to keep notes on certain items? Items may fall off the aggregated list and be re-added again as inventory fluctuates. If an item does fall off, the comments do not need to be saved.
Here is a visual to help with this. Is this even possible? Is there a better approach? The table below is simplified and I plan to create metrics and dashboards from the data. Thank you all!!!
Hi,
for my experience best way to have refresh data is to use Sharepoint.
With sharepoint folder you can manage the data inside and select the file that you want take data and hour and delete the other from the import.
With sharepoint you have also the refresh scheduled in power bi service.
For your data the best way is to import all excel file into a sql azure database throught azure pipeline.
So you'll have a db with all your information.
To have the write back is not so easy but you need power app (another microsoft app) and you'll do the write on excel or better on sql. (find some video on youtube).
To use the power app for writeback sql i think you have to import data in direct query.
If you need a specific request tell me.
I hope I have been helpful.Ciao!
1 Reply
Hi,
for my experience best way to have refresh data is to use Sharepoint.
With sharepoint folder you can manage the data inside and select the file that you want take data and hour and delete the other from the import.
With sharepoint you have also the refresh scheduled in power bi service.
For your data the best way is to import all excel file into a sql azure database throught azure pipeline.
So you'll have a db with all your information.
To have the write back is not so easy but you need power app (another microsoft app) and you'll do the write on excel or better on sql. (find some video on youtube).
To use the power app for writeback sql i think you have to import data in direct query.
If you need a specific request tell me.
I hope I have been helpful.Ciao!