Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
manjirit
Helper I
Helper I

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
ImkeF
Super User
Super User

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

 

 

 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

9 REPLIES 9
ImkeF
Super User
Super User

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

 

 

 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

Hi,

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

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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?

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.

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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. 

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 

 

Files from the folderFiles 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?

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

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

MalS
Resolver III
Resolver 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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.