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:
where the named cell
contains the filepath obtained from the
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.
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
Code:
=Cell(filename)
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.