I am new to Microsoft Query, so my terminology may not be standard.
I am setting up an Excel Document to act a s a dashboard. The Document will sit on a shared folder across multiple users.
I have created a sheet in Excel, in which I have a cell that reads the path of the Excel Document.
I plan to use a Microsoft Query to use the path located in lets say "Sheet1(A1)" to find all the files in this directory and combine them.
On running the query, I can set it up so that it does this. I however come into issues when trying to combine the files in this directory. It works on my local machine, however when a user tries on another machine, using their credentials, the Path used to find the input is hard-coded to my path.
How can I carry the "Sheet1(A1)" value into the query / transformation, to allow the path to remain dynamic?
Below is a brief outline of my workflow:
The "Source" for the first part of the import reads. Where "path" refers to a named cell located in Sheet1(A1):
= Folder.Files(Excel.CurrentWorkbook(){[Name="path"]}[Content]{0}[Column1])
I thin out the source by filtering only for a specific term in the filename (there are multiple).
= Table.SelectRows(Source, each Text.StartsWith([Name], "AS") or Text.StartsWith([Name], "As"))
This is where the hard coding begins, I then take the filtered the files and expand and combine the sheets into one.
= Table.AddColumn(#"Filtered Rows", "Transform File from Query1 (2)", each #"Transform File from Query1 (2)"([Content]))
This is where the path is hard coded, and I want to use the value in cell A1.
= #"Filtered Rows"{[#"Folder Path"="**C:\Users\User\Path\**",Name="As_Test.xlsx"]}[Content]
As I said the Source of the query works, but I cannot carry that Dynamic text through, or at least am not versed in the query syntax to carry it through.
With current versions of Excel, Power Query (called Get and Transform in Excel 2016) will do a much better job than Microsoft Query, but judging from your code samples, you are not using Microsoft Query at all, but you are indeed using Power Query.
You can use a cell in the worksheet that stores the current folder path from the perspective of the user who opens the file. You can then feed that path as a parameter to the Power Query.
The formula
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)
will give you the path to the current file. (if it doesn't make sure the workbook is saved, not a new file)
There are a few different ways how you can use the cell value as a parameter in Power Query. One option is to have this formula in a parameter table, as described by Ken Puls in his article about parameter tables for Power Query, just your scenario.