Files in PBI Workspace as datasource for Desktop model?

[MeadowDec]

On the following webpage =  https://learn.microsoft.com/en-us/power-bi/connect-data/service-desktop-files

Article talks about getting an excel spreadsheet into a PBI service workspace and the benefits of it being a linked file for automatically synched refreshes.  However I cannot see anyway to then use that spreadsheet as a datasource for a new model being created within Desktop?!  I saw a post in here where somebody mentioned could set up a dataflow to access that file, however I have had no success in an attempt to do as such?  Trying to select the file with an excel datasource in a dataflow leads to a source is html error.

I have connected with the dataverse in a dataflow, can workspace contents (this excel file) be found in there somewhere?

Thank you for any help!

[Idrissshatila]

Hello @MeadowDec ,

You could try this solution https://youtu.be/CKGyf3NWAd8?si=J8-_2WWmlPIWLuUx

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡

[MeadowDec]

Hi Idrissshatila thanks for replying, but that does not help, in that tutorial she is connecting to onedrive it looks like to create a dataset from within the service.

Whereas I want to connect to a file uploaded to the service from within Desktop.

[lkalawski]

Hi @MeadowDec ,

I don't know if I understand your problem correctly - correct me if I'm wrong.
Would you like to connect to your Excel in Power BI Service - build a model and a report based on its data, and then be able to automatically synchronize it if something changes in Excel. Correct?

If so, if you have a basic account, it would be best to place the Excel file somewhere in the cloud (Onedrive) and then connect to it from Power BI Service. After creating the model and report, you can set a schedule refresh to refresh the report from time to time. Unfortunately, the connection to the Excel file cannot be a direct query, so the data in the report will not be live.
You can also use Power Automate, which will detect when someone changes something in the Excel file (on OneDrive), and then run a report refresh in Power BI Service.

Refresh your Power BI dataset using Microsoft Flow | Microsoft Power BI Blog | Microsoft Power BI

[MeadowDec]

Hi lkalawski thanks for replying, I guess what you say is going to have to be probably the implemented workaround I shall have to employ here, but I am wondering why MS have specifically mentioned uploading files to a workspace in the 'Streamlined upload to Power BI' section, seems ludicrous that feature is available only to not be able to then use it as a datasource for a model?  There must be a way surely, so I was hoping somebody could tell me how to connect to that excel file uploaded to the PBI service workspace, through even a dataflow configured for it maybe, step by step?

[christinepayton · Accepted solution]

I'm not entirely sure where it stores the files for workspaces, but ASSUMING it's stored in the SharePoint site that is associated with the M365 Group that was created with the workspace, you could connect to it in the SharePoint site. If you're a member of the group, it should show up as a site in your SharePoint site feed (you can click the word "SharePoint" in the top bar of SharePoint to see the sites you're following or are a member of). If it's a single file, I find it easier to download the file, connect to it locally, then change the path reference in Power Query to SharePoint (here's a video on how to do that: https://youtu.be/2HZ8l23RXnc?si=i0eQc7aAzx9a8oi_). 

Again, this is assuming it's using SharePoint to store the thing, I never use this feature and always just throw the Excel files in SharePoint so ymmv. 🙂

[MeadowDec]

Thank you Christine for the handy tip, I am accepting your suggestion as the solution to progress forwards with.

I was hoping to get to the bottom of the PBI service direct file connection issue, but with handy tips as useful as that to employ instead, who cares?  Job done, just follow in line and get onto the next objective…!