Add column with the path&filename

[DanFromMontreal]

Good day,

I'm trying to add a column containing the folder & filename for each row of the dataset being imported .

I've tried this but does not return want I want....

= Table.AddColumn(#"Sales", "PathAndFilename", each Excel.workbook(file.contents([Folder.path]&[Name])))

Faily new at this.

Any suggestion?

[Anonymous]

Thank you

[ImkeF]

Hi @Anonymous ,
When you do the from-folder method, there will be some steps being auto-created. One of them should be "Remove other columns". To the right you will find a gearwheel. Click on it and you get a menu of columns to keep. Choose the name-column:

[Anonymous]

@DanFromMontreal Hi, Today i am in a same situation once you are in I also want a column with M code which can get automatically excel file name and I can display it in Bi dashboard.

So if you find solution of your problem please share with me.

Thank you

[diathermy]

I guess I can help. 

In Power Query Editor, you probably combine your files by pressing a button with 2 arrows in Content column.

If so, go to the step  "Removed Other Columns" (Applied steps, right side), add "Folder Path" to the code and press enter.

Like this:

= Table.SelectColumns(#"Renamed Columns1", {"Folder Path", "Source.Name", "Transform File"})

It worked for me 🙂 

[v-stephen-msft]

Hi @DanFromMontreal ,

Check this out:  Solved: adding the file name to a column in power query - Microsoft Power BI Community

This similar post is using the Sharepoint Folder Connector to connect to a OneDrive for Business site and use combine files feature after getting a file list in the folder you want to connect to. But edit or add some steps in the process.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF]

Hi @DanFromMontreal , 
so you are not speaking of one specific query here that you want to change, but to create multiple different queries?
Then I am with @lbendlin here: Approach it with the FromFolder import instead of single file import: This not only allows you to extract the desired additional information, but also enables you to use one shared function allowing similar transformation of data - if that is desired.

@lbendlin : I don't know why that comes back empty - but Value.Metadata is the only function I know that could do this job.

[ImkeF]

If my understanding is correct, I would use a parameter for the full file/path-spec.
Let's name it "FullPathSpec" and set it to: "C:\Users\DM2088\Downloads\Megalist (20220107).csv".
Then you reference this parameter twice: 

First to import the data: 
Csv.Document(File.Contents( FullPathSpec ),[Delimiter=" ", Encoding=1200, QuoteStyle=QuoteStyle.None])

(Note that I've removed the Columns parameter here, as this will often cause trouble)

Second to add itself in a column:

Table.AddColumn(#"Sales", "PathAndFilename", each FullPathSpec )

[DanFromMontreal]

The path and filename were given as an example.

I do NOT know it.

From start....

I'm in Excel and want to import a File.

I go:  Data>Obtain Data>From text/CSV and I select the file I want from any folder I want.

Once selected, I click "Transform Data" which opens up the Power Query Editor.

I do my transformation (remove column, move them around, change formatting...) and then, I would like to ADD a column that specify the Filename and Path of the file I'm working on.

Once I close and load the data, it returns to Excel with my transformation and the newly added column that specifies the file and path of that dataset.

Could be ANY file or ANY folder.  It is dynamic.

Again, the information of the selected file and path is in the APPLIED STEP > SOURCE but I do not know how to extract it.

Thank you for your support

[lbendlin]

@ImkeF I had experimented with Value.Metadata(Source) but the Path attribute comes back empty, and I don't know if that is even supported in Excel.

@DanFromMontreal Does Power Query for Excel give you the option to connect to a folder rather than a file?

[ImkeF]

Hi @DanFromMontreal ,
I think I don't understand the requirement yet:

From your original post:
= Table.AddColumn(#"Sales", "PathAndFilename", each Excel.workbook(file.contents([Folder.path]&[Name])))

are [Folder.path] and [Name] actual columns in your table yet or is that just some M-code that you've tried to return the desired result?

[DanFromMontreal]

ImkeF,

That something I've tried but obviously, did not return what I was expecting.  I could be 100% in the left field with what I wrote.  Totally beginner.

What I want:   The file that I'm importing and transforming with Power Query, I would like my M-code to be able the ADD a column with its filename and path (I.e. C:\Users\DM2088\Downloads\Megalist (20220107).csv) for each of the rows of the dataset.

[AlexisOlson]

I'd suggest refactoring a query like this

let
    Source = Excel.Workbook(File.Contents("C:\Users\aolson\Downloads\MockUpData.xlsx"), null, true),
    Table_1 = Source{[Item="Table_1",Kind="Table"]}[Data]
in
    Table_1

into this:

let
    FilePath = "C:\Users\aolson\Downloads\MockUpData.xlsx",
    Source = Excel.Workbook(File.Contents(FilePath), null, true),
    Table_1 = Source{[Item="Table_1",Kind="Table"]}[Data],
    #"Added Custom" = Table.AddColumn(Table_1, "Custom", each FilePath)
in
    #"Added Custom"

This way you can be sure that the file path custom column always matches the source file.

[DanFromMontreal]

Alexis,

Tried it and return Error.

Your FilePath is static, meaning if is good only for the filename "MockUpData.xlsx" and the path "C:\Users\aolson\Download".

What I want is to be dynamic.  If I change the file and/or path, I would not have to change the programming.

In the Applied Steps, the first Step is "Source" and that tells you the file & path (see below) of the dataset being worked on.  

Csv.Document(File.Contents("C:\Users\DM2088\Downloads\Megalist (20220107).csv"),[Delimiter=" ", Columns=93, Encoding=1200, QuoteStyle=QuoteStyle.None])

Is there a way to extract that information and use it to write the code required to suit my needs????

Hope this is clearer now.  English is not my native language...

[AlexisOlson]

Your file path is technically just as static. If you change the source, you really are changing the M code just as much as changing the file path text in what I suggested. It's just that the GUI generates the text for you in the background rather than you typing/pasting it into the Advanced Editor. This is is just pedantic semantics though.

I do understand your requirement but I can't think of a way to do exactly what you ask. Pasting in a new file path instead of navigating to a file via the GUI doesn't seem like a lot of additional effort to me but I do agree that's often more convenient.

As a workaround, if you don't know what the file path is to paste in, you can create a new query to connect to the file, and copy & paste the file path generated into the existing query, and then delete the new query since you don't need it anymore. This clearly isn't ideal, but I don't think it's possible to reference a literal argument value from a previous step.

I'd be happy to be proven wrong though. Perhaps @lbendlin, @edhans, @ImkeF, or @mahoneypat know "one weird trick" for such a thing?

[DanFromMontreal]

Guys, 

Thank you so much for the generous help provided by I guess I will let go.

I've read all you post, tried the Folder approach suggested by @lbendlin and, to my experiences done this morning, all the files within this folder have to have the same header structure.

Placed 4 different CSV files having different header in a folder and what it does, it takes the header structure of the first file and wants to combined them with the others.  Needless to say that since that don't have the same header, it did not return what I was expecting.

Again, I tried to better understand the M-code generated but since I'm a beginner, it was out of my league...fast!

If and easy solution was not evident to you, it is surely not for me either.

But I'm still hopefull that there is a solution to it.

After all, the folder approach with all the same files structure in it return the filename and path for the combine files.

Perhaps that someday, I will be able to make it work.  But before that, I will need to learn to crawl, then walk and finally run with M-Code 

Thank you all and as we say in french, au revoir

ps:  If you do find a solution - please let me know

@lbendlin, @edhans, @ImkeF, @mahoneypat 

[lbendlin]

Generally you don't want to specify an individual file as a source.  Instead, specify a folder, and then in your M code apply filters to the content of that folder, like "all files starting with M and ending in .xlsx".  Then add the custom column with the file name(s) as demonstrated above.  That way when the folder contents changes you don't need to modify the M code.

[smpa01]

@DanFromMontreal 

let
    Source = SharePoint.Files("https://xyz.sharepoint.com/teams/Analytics", [ApiVersion = 15]),
    sales = Table.SelectRows(Source, each [Name] = "sales.xlsx"),
    #"Added Custom" = Table.AddColumn(sales, "filePathName, each [Folder Path]&[Name])
in
    #"Added Custom"

[DanFromMontreal]

Thank you for the fast response but unfortunately, I probably did not provide enough info on what I wanted.

Let me rephrase....

I'm importing data from a .CSV file located on my computer and/or a server using the Data>From a CSV file from Excel.

This opens up the Power Query Editor.

I transform the data as required (still good) but to know the path&filename of the file when I return to Excel, I would like to add a column on the far right containing this information.  If there is 267 rows of data, I will have 267 of the same information in the newly added column.

This way, I will know for sure in Excel from where the data is coming from.

If the change the source file or location, it will be obvious.

Hope this is more clear.

The file and location can be found, as you sure known, in the APPLIED STEPS > Source but I cannot figure out how to extract it

Regards