Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi guys
Every month i receive an excel file, with sales data for that month. The file (in excel) does'nt have a date column, therefore i've had to add a column [month] manually every time i've received the file. I was wondering if i somehow could extraxt the date from the file name with M-Code, so that i only have to add the month name to the file name? then load the file into PowerBI and execute the function for this file..
Thanks in advance!
Solved! Go to Solution.
You can @Anonymous . You would need to use the Combine feature for files. That will load all files within a filter (you could filter to a specific folder and only pull in .xlsx files for example) and the filename is available there.
Here I connected to a folder and there are 3 files:
I then hit the COMBINE button I'm pointing to.
Once you do the combine, look at your Applied Steps. You may need to edit the "Removed Other Columns" step.
Click the gear and make sure the Source Name column is visible. This is the file name.
At that point, your final query will have the file name imported, and you can manipulate it like any other column.
This method will let you just keep dropping new files in with the month name in it and it will get pulled in on the next refresh.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks for the help - i've used your approach and figured it out
Hello, I know it is a very old post but I hope you will get the notification.
I understand your step by step approach , my question is, as the next step, how do you promote headers?
In your exemple if you want to have your first column called source name (and not "Sales 2013.csv"), your second called region and your third called country?
Thanks for the help - i've used your approach and figured it out 👍
You can @Anonymous . You would need to use the Combine feature for files. That will load all files within a filter (you could filter to a specific folder and only pull in .xlsx files for example) and the filename is available there.
Here I connected to a folder and there are 3 files:
I then hit the COMBINE button I'm pointing to.
Once you do the combine, look at your Applied Steps. You may need to edit the "Removed Other Columns" step.
Click the gear and make sure the Source Name column is visible. This is the file name.
At that point, your final query will have the file name imported, and you can manipulate it like any other column.
This method will let you just keep dropping new files in with the month name in it and it will get pulled in on the next refresh.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks for the help - i've used your approach and figured it out
Great! Glad I was able to assist.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou definately did.. Appreciate the simple approach and screenshots made it easier to follow😀👌
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
42 | |
25 | |
14 | |
14 | |
12 |