Page 1 of 1

Excel Connection String

Posted: Wed Jun 17, 2020 1:30 am
by RuthBallinger
Using Get data from excel will generate a string like

Provider=MSOLAP.8;Integrated Security=ClaimsToken;Persist Security Info=True;Initial Catalog=sobe_wowvirtualserver- [ ####################] ;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, [ #########################] ;Update Isolation Level=2

By comparing strings I can see that the first string is to the dataset and the second on is the userid.

But I cannot find the dataset string anywhere up in BI service (so I cannot change the string eg to the same dataset in a different workspace). Matt, if you see this, on the recent Supercharge course you mentioned this briefly but did not actually find where we could identify the string of a dataset. The only way I can find it at the moment is by creating a connection and seeing what turns up. But it must be visible somewhere.

Re: Excel Connection String

Posted: Sun Jul 19, 2020 11:46 am
by ozeroth
Hi Ruth,
You might have already solved this yourself :)

You're right - the first hex-string within the connection string is a unique ID of the dataset on your tenant, and you should just need to change that string to point to a different dataset.

To find a dataset's ID, go to the Workspace containing the dataset at app.powerbi.com, click the ellipsis next to the dataset name, then click Settings.

This will take you to the Settings page for the dataset and the address in your browser will look like:
https://app.powerbi.com/groups/[### WORKSPACE ID ###]/settings/datasets/[### DATASET ID ###]

You should just need to grab the [### DATASET ID ###] and replace it in the Excel connection string.

Regards,
Owen