PowerQuery breaks in Teams?

[Lurkily]

I have a sheet that basically formats raw data dumps into meeting-ready presentations. I have a couple of questions, though.

The way it works, is that a user will download one of these data dumps, giving it a specific name, and open both sheets. The formatter will recognize the file name, and update fields in a large table, using cell references. This table is the source for several powerQueries that construct multiple tables that are presentation-ready.

First question:
PowerQuery cannot seem to pull from a range of cells. From what I can glean, it seems to absolutely require a table as a source. This is why I use cell references to update a table that's larger than the source data should ever be. It copies the source data to a table that can be used as a source. Is there a way to directly query the source data?

I don't want to instruct them to copy and paste data into a table because there's room for error. The users have a varying degree of familiarity with excel, and if they paste a short table over a long table, data from the last report could be left behind when it shouldn't. Thus I want to reference a filename, and just have them save over and replace the file when they download new data.

Second question:
I'm trying to make it work in a Teams environment. In Microsoft Teams, if I click Data>Queries and Connections>Queries, it shows me "Download Failed" on all queries, even though the workbook queries a table from within the same book.

I could ask users to make sure they use desktop excel - this requires I ask them to have both files open in desktop excel at once, because desktop excel requires both files to be open to read cell references. But Excel in Teams can read cell references without that. I could instruct them to just put the file in the right place, open the formatter, and hit refresh, if PowerQuery only worked in Teams.

Is this a hard limit of Teams that I just have to live with, or am I doing something wrong that makes Teams think it has to download the query from an external source?

I'd google all of this, but all the terms I've tried have been very obscured by different problems. I appreciate any insight that anybody can provide.

 

[GraH]

First question:
PowerQuery cannot seem to pull from a range of cells. From what I can glean, it seems to absolutely require a table as a source. This is why I use cell references to update a table that's larger than the source data should ever be. It copies the source data to a table that can be used as a source. Is there a way to directly query the source data?

PQ can certainly fetch data from a range. When the data is stored in an external file there is no issue. When it is in the same file, you need to reference the range dynamically with a name. Google "dynamic name range."


Second question:
I'm trying to make it work in a Teams environment.

Teams means SharePoint and you can connect to SharePoint files. When you are in the Files tab, select open in SharePoint and get the link to the file over there. That should work.

 

[Lurkily]

PQ can certainly fetch data from a range. When the data is stored in an external file there is no issue. When it is in the same file, you need to reference the range dynamically with a name. Google "dynamic name range."

Thanks; I'll follow up on this and see what I can dig up. It sounds like I can simplify this a good deal, which aside from speeding up processing and eliminating potential complications later, can make this a little more user-friendly.

Teams means SharePoint and you can connect to SharePoint files. When you are in the Files tab, select open in SharePoint and get the link to the file over there. That should work.

Thanks; I'm not entirely sure what this means, but I think, looking at my query, I had a mistaken assumption. My query says "There are no data sources for this query," though in desktop excel, it succeeds.

I never attempted to select a source via 'new query,' because I thought making a query "from table" was the same as making that table (and by extension, the workbook) a 'source'. It looks like I may have confused some of the background mechanics.

I'll attempt to rebuild this by selecting a specific source from 'new query' and see if that helps.

 

[Lurkily]

So I can certainly get the link to the file; I'm not sure how to use it in this context.

If I try to open a workbook using this link, it gives me access options for web content, such as 'access as anonymous.' I tried that, but since this is on our organizations sharepoint network, I tried also "organizational account". Though I can certainly access this team and file, it universally says it cannot authenticate the credentials provided.

If I select a file normally, I do have the sharepoint network listed in the file selector, and can select the raw data to import -- but same problem.

When opened in the teams interface, the query does not update. "The following connection failed to refresh." When opened in a desktop app, it refreshes fine, though, which is progress. It's still a big step forward in crafting something less prone to input error, since using this method never requires them to modify input.

 

[Lurkily]

Update; this may be an access issue in our network. Because the link from teams looks nothing like the path in cell references, when I used another sheet on teams as a cell reference, I made a cell reference, and got the file path from that.
It looks like:
='https ://org.network.com/filepath/filepath/[filename.xlsx]Sheet'!C5
I made a query referencing that file as mirrored locally, and in the query's "Advanced Editor", and using that as a template, replaced the file path with:
'//org.network.com/filepath/filepath/filename.xlsx

I got this error: (Error details are just the file path, which I cut off)

I think I might just need to live with the fact that Teams can't process queries from sources off my own HD, since I don't have access to Excel's Trust Center, and there are zero entries in the list.