Attempting to use relative paths in a DataQuery

[AdamBramley]

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:

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

outputDir

contains the filepath obtained from the

=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.

 

[John_w]

=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:

=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.

 

[AdamBramley]

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!

 

[71_FIL]

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