How to make the data source the same path as the parent

[Mr_Phil]

Hi. Very new to Power Query on Office 365 primarily Excel. So I moved a copy of my workbook to a safe location. I put in copies of all the files I import. I began building simple queries to import and clean the data before depositing it in my workbook. I noticed that the imports were explicitly linked to the path. That won't work as this workbook is given to my counterpart on the other half of the company. I need the queries to find the named files in the same path as the workbook. I know how to do that in VBA but I'm too new to PQ to've figured that out yet. Can someone point me to a tutorial or something so I can build that in? Many thanks.

 

[JGordon11]

One option is to put =TEXTBEFORE(CELL("filename"),"[") in a cell in the workbook (in any sheet hidden or visible). Name the cell MyPath (or whatever you want but I'm assuming that's the name for this example).

Then create a blank query and in the advanced editor replace everything with

Excel.CurrentWorkbook(){[Name = "MyPath"]}[Content][Column1]{0}

(no let or in needed)

Name the query MyPath.

Then in your other queries wherever the path is hard coded replace it with MyPath.

For example if your path is "C:\Documents\Excel Files\" and in a query you have hard-coded "C:\Documents\Excel Files\File1.xlsx" you would change it to

MyPath & "File1.xlsx"

 

[Mr_Phil]

One option is to put =TEXTBEFORE(CELL("filename"),"[") in a cell in the workbook (in any sheet hidden or visible). Name the cell MyPath (or whatever you want but I'm assuming that's the name for this example).

Then create a blank query and in the advanced editor replace everything with

Excel.CurrentWorkbook(){[Name = "MyPath"]}[Content][Column1]{0}

(no let or in needed)

Name the query MyPath.

Then in your other queries wherever the path is hard coded replace it with MyPath.

For example if your path is "C:\Documents\Excel Files\" and in a query you have hard-coded "C:\Documents\Excel Files\File1.xlsx" you would change it to

MyPath & "File1.xlsx"

I did it. But Maybe I did it wrong. I set up the named range with the formula in it and it shows the current path. That's awesome. I already can think of a couple of places that will be handy. So thanks for that. I created a new query and instead of loading it I saved it as connection only. The name is MyPath and when I click on it I can see that it evaluates to the current path. Then I opened the advanced editor to one query and changed it.

But when I finished this is what I saw in the "preview (is that right?) pane" I will get this figured out. I see what you were trying to teach I just need to study up on it. Thank you for your help.

 

[jdellasala]

You first need to name the cell that the formula is in. That name doesn't matter as it will be brought into PQ as "FromArray_1". You could also leave it unnamed, but then PQ will turn it into a table and it will come in as Table1.
The query itself does need to be named MyPath.
Either way, you need to drill down into the single cell of the table. Just right click on the cell - NOT the column header, and select Drill Down.

let
    Source = Excel.CurrentWorkbook(){[Name="FromArray_1"]}[Content],
    Column1 = Source{0}[Column1]
in
    Column1

You'll end up with something that looks like this:

Now the Summary Query should work.

BTW, bringing the Named Cell into PQ and PQ calling it "FromArray_1" may be a recent change, and may not even show up that way in your version. I'm on the Insider Beta edition.