Direct Excel Pivot Table Access to Semantic Model In Another Tenant As Guest User

[DavidRogers13s]

Is it possible to use the "Pivot Table -> From Power BI" button under the "Insert" tab in excel to connect to a Power BI Semantic model hosted in another tenant if I'm a guest user in that tenant and I've been granted access to that model and workspace?

I've gotten very close to this, I've created a Semantic Model in Tenant A(User A), shared it with User B in Tenant B, it shows up under their workspaces(In Power BI in web browser):

I can see the model as User B logged into a browser:

I can access it in the brower, "Analyze in Excel", download a copy of that(it creates a "OLAP Query" connection), browse the copy, no issue at all, the only thing I can't do, is login to excel as user B, go to the "Insert" tab, "Pivot Table -> From Power BI (Tenent B)" and use the menu to locate my shared model, I get this:

(I am able to access the model logged in as User A without issue)

Why is this? I read through a series of articles on this?

https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-dataset-external-org-share-abou...

https://learn.microsoft.com/en-us/power-bi/enterprise/service-admin-azure-ad-b2b

(this article states that "Guest users can't use Analyze in Excel" but I have at least from the browser)

Attempted to enable the settings mentioned above to no affect...

Doesn't seem to shead much light on this issue, I do notice that the button in excel says "Power BI (Tenant Name)" does that mean that models must be "under that tenant" to be selectable from that menu structure? Is this supported at all?

[GilbertQ]

Hi @DavidRogers13s 

If you are an external user trying to connect to a semantic model via Excel, unfortunately this will not work using the automated way within Excel because it uses your logged in credentials Fortunately there is another way to do this and here is my bog post explaining this. Did you know external (Guest) users can connect to your Semantic Model using Excel? - FourMoo | Fabr...

[DavidRogers13s]

Thanks for the reply @GilbertQ,

Yes, I figured as much, you can see the same behavior if you navigate to PowerBI in the browser as User B (Guest User) and click on the externally shared semantic data model, then "Analyze in Excel", instead of taking you to a excel online window (as it will do with User A), it instead downloads a excel file with a OLAP connection (what you describe above). This indicates to me that the lack of direct Power BI connect support is a intentional decisions made by Microsoft.

We've actually been using the method (+with the assistance of a excel plugin) above for our users, however that requires the users to install a plugin (or undergo more extensive training). The benefit of the Power BI direct connect route is that it's easy for a user to navigate to their cube and download it without training, external information. or installing a plugin.

Further more our users would like to use Excel online, as they often use non-windows machines, and don't have a desktop version of excel easily accessible.

What would be optimal to see here is a more official source of documentation on this topic, you say it doesn't work because "it uses your logged in credentials" but in various other locations (Power BI, web browser) it does exactly the same thing (uses the same credentials) and yet I'm able to access the data model (because it shared to that user, that user has explicit access to it), browse it, and interact with it, normally, etc..

The limitation of this behavior is specific to excel, and I believe (please prove me wrong) the reason it doesn't work is becuase Microsoft only implemented a single button under the "Pivot Table" option in excel, tightly scoped to a single organization, and unlike with power BI desktop or the browser, never implemented the ability to connect to externally shared datasets.

Has Microsoft anywhere officially documented this restriction? Was that a intentional decision, or simply the first phase in a series of additional changes Microsoft plans to release? Do they, some time in the future, plan to add support for external datasets in excel (direct connect)? Is there a user voice forum where this has already been brough up (and voted upon) or am I the first to raise this publically?

[lbendlin]

If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com

[DavidRogers13s]

Ok I've cross posted it here:

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=39a93f57-7a91-ef11-95f6-000d3a7afc9d