Forum Discussion
Helper IImporting 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:
Community ChampionThat'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.
Helper IAh! 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
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 folder
got the files. Removed the unwanted columns. And trying to combine the binaries, this was working before but today I see the error :
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?
- ImkeF
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