Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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:
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
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:
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
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?
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
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
93 | |
89 | |
35 | |
35 |
User | Count |
---|---|
154 | |
102 | |
82 | |
64 | |
54 |