I built a query that joins together all the visible sheets in the workbook
It works fine overall and saves me a lot of time and mistakes
The reference of the file name and the path is done by a hidden sheet named filepath
The problem is that sometimes I want to run the file from a path in Google Drive or OneDrive or another network path in this case the reference from the workbook to the query is not created and an error is received.
Is it possible to do this in a general way so that the query will work in any path
where the file will be saved?
Is it possible to refresh the query without having to save the workbook after each
change?
Another general question - How to make in the query that a decimal number will be in the format of 2 digits after the point?
To connect to a file (or folder), you need at least two things: a connector and a location. Contrary to what you might expect, both are part of the problem. Connecting to a cloud-stored file requires a different connector than connecting to a locally stored file. Additionally, the web address of a cloud-stored file is usually not the URL required to access its contents.
I can be brief abou that, no.
That depends on if you want to change the value or just the formatting. Changing the value means you apply one of the number rounding functions in M. However formatting is something you do in the application that receives the data from Power Query, like the table on the excel worksheet. Select your column and choose the appropriate format.
I understand that need a connection to the path where the file is located and also to the file name
My question is whether it is possible to use a trick
i mean to upload any empty table to the query and then take from this table the required information on the file name and the path for the other tables that I want to upload
something like
let path= emptytable.folder
emptytable.file=filename
To be more precise, a connector and a file location.
Sure. Assuming your filepath has the valid reference to access contents. Simply use try-otherwise on the different connectors… As illustrated below with Excel.Workbook and Web.BrowserContents that each take the Filepath input.
let
Source = try Excel.Workbook(File.Contents(Filepath&FileName), null, true)
otherwise Html.Table( Web.BrowserContents(Filepath ?? "https://forum.enterprisedna.co/t/query-to-join-sheets-when-the-path-to-the-file-is-on-a-network-drive/59607/2"), {{"title", "head"}})
in
Source
Unfortunately it doesn’t work for me
Apparently I’m not using the code you wrote me correctly
But I probably still don’t understand how it works
When I type as source only Excel.CurrentWorkbook()
I get all the information that is in the workbook into the query
But I don’t have the properties for item, kind and hidden.
Since the data did go up from the workbook to the query, I conclude that a connection was created
Now the question is how to use the connection details that have already exist in the query to add these properties (item, kind, hidden) without the additional need to define a path and file name.
If this cannot be done, I would appreciate help on how to use the code you sent me
My code sample from the last message was only meant for illustration purposes. Just leverage the UI to connect to the file - once for each location, that means you will create at least two separate queries.
Next step, duplicate your query, this way you can always go back, and parameterize it by retrieving the location from the worksheet or a parameter.
If that all works out - you can reference those “dynamic” queries (let’s say they are named: LocalFileQuery and GoogleDriveQuery) as Source by returning the one that does not produce an error.
let
Source = try LocalFileQuery otherwise GoogleDriveQuery
in
Source
Maybe this is what you’re looking for: To extract the data from a single sheet in an excel workbook is described in the part after the Excel.Workbook() formula, so the syntax would be something like this:
let
Source = try Excel.Workbook(File.Contents(Filepath&FileName), null, true)
{[Item="Sheet or Table name"]}[Data]
otherwise Html.Table( Web.BrowserContents(Filepath ??
"https://forum.enterprisedna.co/t/query-to-join-sheets-when-the-path-to-the-file-is-on-a-network-drive/59607/2"), {{"title", "head"}})
in
Source