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

Mr_Phil

Board Regular
Joined
May 28, 2018
Messages
147
Office Version
  1. 365
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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"
 
Upvote 0
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.

1681864106091.png


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.

1681863878732.png
 
Upvote 0
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.
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="FromArray_1"]}[Content],
    Column1 = Source{0}[Column1]
in
    Column1
You'll end up with something that looks like this:
1681905042634.png

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.
 
Upvote 0

Forum statistics

Threads
1,223,360
Messages
6,171,630
Members
452,411
Latest member
sprichwort

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top