Forum Discussion
Helper IImporting Excel file every week from a folder and refreshing the report for that week.
I am a total newbee to PowerBI and trying to do the following at work.
I need to create a weekly report for some readings by faculty. I get the exel file every week that contains the reading data.
Here is the structure (columns) of the weekly excel files with example data that I get :
| Type | Org | Faculty | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Total |
| CR | HM | Tom Jones | 0 | 38 | 0 | 0 | 0 | 17 | 0 | 55 |
As you can see, there is no date coming into the file contents. But the file names are
"July 18th.xlsx"
"July 11th.xlsx"
etc. No year in the file name.
I am creating weekly reports like "Total by Faculty", "Total by Type" or "Total by Org" etc. (So actually I don't need daily numbers for reading. Might as well delete the Days columns.)
Each week, as a the new file comes in, I need to import that file (automated) and my report should be refreshed and show the new report for that week. But whenever wanted I need to see the report for the period chosen.
So as I see it, while importing Excel files into Power BI, I need to add a column into the file, with date filled in from the Title of the file.
For this part, I checked the follwing post, but couldn't go any further with that.
Can someone tell me step by step process to achieve the above process?
~Sam
There is a new From-Folder technique that MalS is referring to, and as a beginner with xls-as a source, I recommend to use that: http://www.excelguru.ca/blog/2016/12/21/new-combine-binaries-experience/
This is not easy for a beginner, but once you've gotten your head around it, you will have gained a much better understanding of how the query editor works in general and this will help you for your future work ;-)
There is no feature for incremental load in PBI, so you will always need to import all files and then select the most recent one. In order to do this, you need to reference a field/column which contains suitable data. This is not your name-column, as it contains no sort order. So you'd better keep one of the date columns as well. So you modify step"Removed other Columns1" by checking one of the date-columns:
9 Replies
MalSResolver III
The most recent version of Power Bi Desktop creates a Source.Name field (i.e. the file name of the data source). You can use that field to filter your reports.
Perhaps try updating your Power BI installation, then use the Folder datasource again as the source of your data.
- ImkeF
Community Champion
There is a new From-Folder technique that MalS is referring to, and as a beginner with xls-as a source, I recommend to use that: http://www.excelguru.ca/blog/2016/12/21/new-combine-binaries-experience/
This is not easy for a beginner, but once you've gotten your head around it, you will have gained a much better understanding of how the query editor works in general and this will help you for your future work ;-)
There is no feature for incremental load in PBI, so you will always need to import all files and then select the most recent one. In order to do this, you need to reference a field/column which contains suitable data. This is not your name-column, as it contains no sort order. So you'd better keep one of the date columns as well. So you modify step"Removed other Columns1" by checking one of the date-columns:
manjiritThanks! Both of you. I am going to try Excel 2016 feature described on the mentioned blog. Will update here once I get my work done successfully.
- ImkeF
Hi,
no need to take Excel 2016. The same functionality is included in the latest version of Power BI Desktop as well.