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