cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manjirit Regular Visitor
Regular Visitor

Importing 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 :

 

TypeOrgFacultySunMonTueWedThuFriSatTotal
CR        HM        Tom Jones03800017055

 

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. 

https://community.powerbi.com/t5/Desktop/Retain-file-name-column-when-using-Folder-as-a-data-source-...

 

Can someone tell me step by step process  to achieve the above process? 

 

~Sam

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Importing Excel file every week from a folder and refreshing the report for that week.

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:

 

PBI_ImportFromFolder.jpg

 

 

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

9 REPLIES 9
MalS Member
Member

Re: Importing Excel file every week from a folder and refreshing the report for that week.

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. 

Super User III
Super User III

Re: Importing Excel file every week from a folder and refreshing the report for that week.

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:

 

PBI_ImportFromFolder.jpg

 

 

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

manjirit Regular Visitor
Regular Visitor

Re: Importing Excel file every week from a folder and refreshing the report for that week.

Thanks! 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.

Super User III
Super User III

Re: Importing Excel file every week from a folder and refreshing the report for that week.

Hi,

no need to take Excel 2016. The same functionality is included in the latest version of Power BI Desktop as well.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




manjirit Regular Visitor
Regular Visitor

Re: Importing Excel file every week from a folder and refreshing the report for that week.

Thanks!

 

I see that the newest Power BI is indeed retaining the source file name as one of the columns while importing from a folder. My problem now is : the file names are in the following format : 

 

"July 18th.xlsx"

"July 11th.xlsx"

 

When retaining the SourceName, it creates that column as Text. If I try to convert the column type from Text to Date, it gives an error, 

"DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
July 18th"

 

Anyway to convert the dates in the text format to Date field?

Super User III
Super User III

Re: Importing Excel file every week from a folder and refreshing the report for that week.

That's why I've suggested that you include one of the availabe Date-fields as well.

 

Otherwise: There is no command that will do this transformation straight away for you. And you need to find a way to include the year, which is currently missing. So pls reconsider.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




manjirit Regular Visitor
Regular Visitor

Re: Importing Excel file every week from a folder and refreshing the report for that week.

Ah! Yes! Got that. 

 

I can use Date Created column or I just did it another way by using the FileNames. (As the date created were not the actual File Dates somehow)

 

Here is what I did "

 

1.  I split the column SourceName on delimiter "." to separate "18th July" and ".xlsx". 

2. Deleted the column with "xlsx"

3. Again split the column with data "18th July" on delimiter " " to separate 18th and July. 

4. Again split the column with data  "18th" to remove last two characters and kept only 18. 

5. So now I have two differnt columns - one with number 18 - Named this as Date  and the other with July - named this as Month.

6. Added a custom column with the following formula to concatenate all parts of date and add year to that. 

 

= Date.FromText([Month] &  " " & Number.ToText([Date]) & " " & Number.ToText(Date.Year(DateTime.LocalNow())))

 

I have a column with date in Date type now. 

manjirit Regular Visitor
Regular Visitor

Re: Importing Excel file every week from a folder and refreshing the report for that week.

So this query seemed to work when I created. Today I returned to it and started getting errors, when I refreshed to add recent files added in the folder. 

Since I couldn't  understand the errors, I deleted all the previous queries and started builing a new one. I connect to the folder and 

 

Faculty.PNGFiles from the folder

got the files. Removed the unwanted columns.  And trying to combine the binaries, this was working before but today I see the error : 

 

error.PNG

 

Any idea, whats going on?

 

The error reads as  : 

 

Details: "Query 'Sample File Parameter2 (2)' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

 

There is no Sample File Parameter2 (2) that Source is refering to. I am so confused - where is this  "Sample File Parameter2 (2)" coming from?

Super User III
Super User III

Re: Importing Excel file every week from a folder and refreshing the report for that week.

As you only have 1 query there at the moment, the error-message looks pretty illogical. Try the following:

1) Clear the cache

2) Save the file under a new name, close & reopen

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors