Hi Craig,

I have so many questions! First, I think there's some confusion with regards to putting the file containing the query on Teams vs the source data the query is connecting to being on Teams. 

The error implies that you built the query using the Get Data > From File connector which is only suitable for files saved on your own hard drive. When you say you've synced the file to a OneDrive location, I presume you mean the local version of the file on your PC.

Are you requiring the users to refresh the query themselves or is this something you can do? Note: you cannot refresh queries via Teams. Users must open the workbook in Desktop Excel and then refresh.

If you want to share the file with users on Teams and they be able to refresh (by opening in the Desktop Excel) then you need to connect to the file saved on OneDrive using the OneDrive web file path and the From Web connector? See this tutorial under the heading: From the Web connector using a file URL or if it's saved on SharePoint see the instructions for SharePoint further down the page.

Then it should be a case of giving the other users permission to your OneDrive/SharePoint file.

Mynda

Hi Mynda,

Thanks for your reply, and sorry for the lack of clarity in my question - of course, it seemed perfectly clear to me haha!

You are correct - I developed the tool on my own hard drive using the Get Data > From Text/CSV connector, and I'm seeking to port the whole shebang (query and source data) to a Teams site. I often sync Teams sites so that I can get to the files via File Explorer, without the added layer of the Teams interface.

The refresh is something I do want my users to do, as they can change a date in the interface which will change the data that the query returns from the data source. At the moment, I've told than that they're "stuck" with the date I set when I refresh the data (which I don't think will be a huge issue initially).

Thanks for the link to the tutorial - I'll check it out, and let you know if I have success!

Regards,

Craig

Hi Mynda,

I had an idea, I tried it, and it worked. I've separated the two "refresh" stages into one I can do, and one that my users can do.

My initial "load", which I'll do, will import the CSV into a table with no transformation whatsoever - the data will be completely "raw". I've converted my original load query to do its work based on that table, this separating the "user refresh" from the data import by virtue of the table that is now in the Excel file.

I tested that it works by renaming the original load file and running my "user" query. The "import" query, of course, did not. Problem (at least for now) solved!

Any input on caveats for this kind of strategy will be gratefully received, however :)!

Regards,

Craig

Hi Craig,

Glad you've figured out a workaround. Your users will need to be very specific about which query they refresh as opposed to Refresh All so they don't refresh your query, but should't be too difficult to explain.

The only other issue is the file might get big if the data is sitting in the worksheet, but depends how much data you expect to have.

Mynda