Query to join sheets when the path to the file is on a network drive


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.

  1. 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?
  2. Is it possible to refresh the query without having to save the workbook after each
  3. Another general question - How to make in the query that a decimal number will be in the format of 2 digits after the point?

mrg ws.xlsx (31.6 KB)

Thanks in advance

Hi @y70852846,

That should be straight forward, right? Or not…

  1. 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.
  2. I can be brief abou that, no.
  3. 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 hope this is helpful.

Thank you

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

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.

    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"}})

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

Thanks in advance

Hi @y70852846,

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.

    Source = try LocalFileQuery otherwise GoogleDriveQuery

I did it
now i have copy of LocalFileQuery
How do I reference the copy of LocalFileQuery to get the properties (item, kind, hidden)?

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:

    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"}})