Excel Connection String

RuthBallinger
Posts: 2
Joined: Thu Apr 09, 2020 5:36 pm

Excel Connection String

Postby RuthBallinger » Wed Jun 17, 2020 1:30 am

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.

ozeroth
Posts: 27
Joined: Mon Apr 06, 2015 6:02 am
Location: Auckland, New Zealand

Re: Excel Connection String

Postby ozeroth » Sun Jul 19, 2020 11:46 am

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


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest

cron