Forum Discussion
Add column with the path&filename
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_1into 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.
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...
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?
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.
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
