Attempting to use relative paths in a DataQuery

AdamBramley

New Member
Joined
Jul 8, 2013
Messages
17
Hi everyone,

I have created a Python backend that downloads a series of .csv files to an output directory. I wish to use Excel to create a 'single-pane-of-glass' monitoring solution for these .csv files, so that users who do not wish to use a monitoring solution can simply open and use the provided visualisations and tools.

Most things seem to be working as expected, but I'm running into an issue whereby my PowerQueries use absolute paths for the .csv files, which will obviously only work for my own setup. I am unable to implement relative paths to my PowerQueries. I seem to have a correct relative path with the following PowerQuery:

Power Query:
let
  Filepath = Excel.CurrentWorkbook(){[Name="outputDir"]}[Content]{0}[Column1],
  Source = Csv.Document(File.Contents(Filepath & "eventLogs.csv"), [Delimiter = ",", Columns = 12, QuoteStyle = QuoteStyle.None]),
  #"Promoted headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"object", type text}, {"type", Int64.Type}, {"itemId", type text}, {"collectionId", type text}, {"groupId", type text}, {"policyId", type text}, {"memberId", type text}, {"actingUserId", type text}, {"installationId", type text}, {"date", type datetime}, {"device", Int64.Type}, {"ipAddress", type text}})
in
  #"Changed column type"

where the named cell
Code:
outputDir
contains the filepath obtained from the
Code:
=Cell(filename)
formula. However, this then gives an 'An on-premises data gateway is required to connect' error, and the GUI provides no useful forward path.

Googling this error takes me towards servers and remote connections - none of which seems to apply since I'm only trying to query local files.


I'd love any assistance that the forum can give, and also any 'don't do that, do this' suggestions - this use-case is something I've not tried before so I may well just trying to be do the wrong thing.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Excel Formula:
=Cell("filename")
returns the path, Excel-style workbook name and sheet name of the current workbook: C:\path\to\[Workbook name.xlsx]Sheet1

Your PQ formula looks like it only needs the path, so this cell formula:

Excel Formula:
=SUBSTITUTE(CELL("filename"), RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("[",CELL("filename"))+1),"")

should make the PQ read "eventLogs.csv" from the same folder as the workbook.
 
Upvote 0
Thank you - that does tidy the formula.

However, the issue I was facing was the following:

However, this then gives an 'An on-premises data gateway is required to connect' error, and the GUI provides no useful forward path.

If I input the wrong string for the powerquery, this error does not appear. It looks like the powerquery is:

- hitting the right file
- failing to open the file only when relative paths are used in the query string

Would appreciate any insight into this that anyone has!
 
Upvote 0
Having the same issue: trying to access a local file (same path as the workbook) and I get the "An on-premises data gateway is required to connect" message.

As Adam was suggesting, I might be doing the wrong thing too.

Using Excel for Mac...
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

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