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.
