Power Query PAth

**leukep** · 06-01-2017, 06:12 AM

Hi all!

I'm having problems with the path of an excel query and I'm afraid there is no solution. maybe you can help me.

Our team works with dropbox, and not with a local server which causes every different user to have a different file location.
for example :
C:\Users\Robbe\Dropbox (xxx)\General\01 Operations\19 General File
C:\Users\Thomas\Dropbox (xxx)\General\01 Operations\19 General File

Is there any way I could bypass this problem and make my query refresh on a different users computer ?

**CK76** · 06-01-2017, 07:50 AM

I'm not familiar with dropbox, but you can pass variable from sheet to PowerQuery.

Lets say, you set up named range for single cell in Sheet1!A1, and named it "vUser".

Go into advanced editor in query editor and add below line right after "let"

Please Login or Register  to view this content.

Then you'd edit the source by concatenating path string with User variable.

Please Login or Register  to view this content.

EDIT: This example is for querying Excel File, but same principle applies if you query folder etc.

In addition you can combine it with VBA to automatically update value in the named range.

Please Login or Register  to view this content.

**leukep** · 06-01-2017, 08:35 AM

Thanks for the reply.
Unfortunately i'm no that great with this!
If I'd name column "T" in the source file "vUser" what should I write?
User = Excel.CurrentWorkbook(){[Name="vUser"]}[Content]{0}[Column17], ?

If I do this it cant find a column named "vUser"

**CK76** · 06-01-2017, 08:47 AM

Couple of things.

1. Named Range should refer to single cell. (You can have dropdown list in this cell, but only single variable should be passed to PowerQuery)
2. [Column1] should not change since named range in this set up should have only single column.