Forum Discussion
Multiple monthly excel files
Anonymous,
If you put all the Excel files in OneDrive for business folder, you can enter the following URL in SharePoint Folder connector of Power BI Desktop to get data from all the excel files .
https://mydomain-my.sharepoint.com/personal/user_mydomain_com
In Power BI Query Editor, filter the folder path to your folder(the folder is named "test" in my scenario ), then expand the content column. You can see that all the data are prefixed with excel name in second screenshot.
filter folder path and expand content columndata are expanded from all the excel files
After that, you can create report in Power BI Desktop, publish the report to Power BI Service and set schedule refresh for the dataset. This way, when you add new excel files to onedrive for business folder, new data will be automatically added to Power BI Report based on refresh schedule .
Regards,
Lydia
Anonymous,
If you put all the Excel files in OneDrive for business folder, you can enter the following URL in SharePoint Folder connector of Power BI Desktop to get data from all the excel files .
https://mydomain-my.sharepoint.com/personal/user_mydomain_com
In Power BI Query Editor, filter the folder path to your folder(the folder is named "test" in my scenario ), then expand the content column. You can see that all the data are prefixed with excel name in second screenshot.
filter folder path and expand content columndata are expanded from all the excel files
After that, you can create report in Power BI Desktop, publish the report to Power BI Service and set schedule refresh for the dataset. This way, when you add new excel files to onedrive for business folder, new data will be automatically added to Power BI Report based on refresh schedule .
Regards,
Lydia
My situation is similar however the data is hosted locally on my computer. My issue however is that each month I get a new excel workbook with the "new month's data" but I don't want to go through the cleaning process.
So for example spreadsheet 1 comes with data which I clean in PowerBI, import, update field types etc. - now I create my report with various visualisations for this month.
In month 2 I have a new spreadsheet with the exact same data layout (spreadsheet 2). What is the most effecient way to import this into PowerBI (and avoid redoing the cleaning, etc) and update my report with the fields in the new report without having to manually edit each visualisation and drag the relevant field to each visualisation?