Analyze in Excel

[TheCreepster1]

Hello, I have a colleague who is having an issue refreshing data in excel that has been built using the 'Analyze in Excel' option from Power BI. Unfortunately, it's not a dataset I recognise but I'd like to help my colleague identify it. Is there a way of telling which Power BI dataset the excel file is linked to? In the connections, all I get is a link like:

pbiazure://api.powerbi.com xxxxxxxx-xxxx-xxxx-etc

[v-henryk-mstf]

Hi @TheCreepster1 ,

Whether the advice given by @lbendlin  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[v-henryk-mstf]

Hi @TheCreepster1 ,

All shared data source information in the tenant is located as follows, you can find the corresponding dataset and check its administrator and ask for the source of its excel data.

Or you can view similar solutions.

Solved: How to use an Analyze in Excel pbiazure in a separ... - Microsoft Power BI Community

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your feedback.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TheCreepster1]

Hi Henry, thanks for your reply. Unfortunately this doesn't help solve what I am after. One of my colleagues has given me an excel workbook that has a connection in it to a Power BI dataset. What I am trying to do is identify which dataset this has come from so I can then go into the construction of this dataset in the back end. All I have in the excel workbook is the connection name and connection string in the Connection Properties screen, and from this information I can't easily identify which Power BI dataset it links to.

In the connection name I have (masked with x):

pbiazure://api.powerbi.com xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx Model

And in the Connection String (masked with x):

Provider=MSOLAP.8;Integrated Security=ClaimsToken;Persist Security Info=True;Initial Catalog=sobe_wowvirtualserver-xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx;Data Source=pbiazure://api.powerbi.com;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Identity Provider=https://login.microsoftonline.com/common, https://analysis.windows.net/powerbi/api, xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx;Update Isolation Level=2

From this information, is there a way I can then go identify which Power BI dataset this has come from?

[lbendlin]

ask your tenant admin to help you with an inventory of the tenant datasets.  (Note: this won't work if the daset resides in the personal workspace of the previous user)

[TheCreepster1]

Hi, thanks for your reply. I have access to the tenant admin, but it's not something I've used very frequently. Where would I find an inventory of the tenant datasets and how would it help me identify the source in excel?

[lbendlin]

Ask your tenant admin to run the inventory API calls.

 $workspaceBatch = Invoke-PowerBIRestMethod -Method Get  -Url "https://api.powerbi.com/v1.0/myorg/admin/groups?`$skip=$skip&`$top=$batchSize&`$expand=datasets" | ConvertFrom-Json        

               $datasets 	+= $workspaceBatch.value | Where-Object {$_.datasets.Count -gt 0} | Select-Object @{Name = "workspaceId"; Expression={ $_.Id} }, @{Name = "datasets"; Expression={ $_.datasets | Select-Object * -ExcludeProperty WorkspaceId } } | Select-Object * -ExpandProperty datasets -ExcludeProperty datasets

That will allow you to look up which workspace the dataset is in, and who the owner is.