cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
McJasem
Frequent Visitor

Get current file name within PowerQuery Editor

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!

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

You can @McJasem . 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:

edhans_0-1625151587291.png

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.

edhans_1-1625151658599.png

Click the gear and make sure the Source Name column is visible. This is the file name.

edhans_2-1625151705950.png

At that point, your final query will have the file name imported, and you can manipulate it like any other column.

edhans_3-1625151743171.png

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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Thanks for the help - i've used your approach and figured it out

View solution in original post

6 REPLIES 6
p-a-p
New Member

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?

McJasem
Frequent Visitor

Thanks for the help - i've used your approach and figured it out 👍

edhans
Super User
Super User

You can @McJasem . 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:

edhans_0-1625151587291.png

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.

edhans_1-1625151658599.png

Click the gear and make sure the Source Name column is visible. This is the file name.

edhans_2-1625151705950.png

At that point, your final query will have the file name imported, and you can manipulate it like any other column.

edhans_3-1625151743171.png

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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks for the help - i've used your approach and figured it out

Great! Glad I was able to assist. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

You definately did.. Appreciate the simple approach and screenshots made it easier to follow😀👌

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors