Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Analyze in Excel Via Get Data From Power BI Dataset

I'm trying to analyzie power bi datasets in excel but I'd like to make the connection from Get Data -> From Power BI Dataset as shown here:

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-in-excel#other-ways-to-a...

 

I used to have this open available but no longer see it an option in the drop down. Just curious to see if something has changed, is no longer supported, or maybe I need to update something on my end. I did just reinsall the analyze for excel component but not seeing any changes in excel from that update. 

 

I have an existing excel file that I'd like to bring some data from a dataset into and was hoping to bring it in this way. On a side now I am able to download the .odc file and open that. Is there another way to create that connection in an existing excel file so I can bring the data in? 

 

Thoughts? I am a pro user. 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

The .ODC file creates an XMLA endpoint client connection.

 

If you want an alternative you can connect from Excel to the "Analysis Services" instance of your Power BI dataset.  There are some subtle differences, but mostly you'll get the same result as with the .odc

View solution in original post

10 REPLIES 10
Cecile_Quillard
New Member

Hi,

I am used of connecting to Power bi from Excel and realized that since the May 2020 release of Power BI desktop I cannot connect to data using the power bi tab, and do not see the connect to power bi dataset option. Have those options been removed?

Please let me know

lbendlin
Super User
Super User

The .ODC file creates an XMLA endpoint client connection.

 

If you want an alternative you can connect from Excel to the "Analysis Services" instance of your Power BI dataset.  There are some subtle differences, but mostly you'll get the same result as with the .odc

Anonymous
Not applicable

How do I create that connection in excel? From the Get Data standpoint which option do you select? I can see the connection info in the properties of the odc file that is download, just unsure of how to take those connection properties and create a similar connection in an existing excel file. 

 

I did just do this and it works, maybe this is what you were refering to and this should sufice.

Data (Tab) - > Existing Connections -> Browse for More... -> Select your .odc file

2020-06-05_12-43-16.png2020-06-05_12-43-38.png

 

However, I would still like to know why the option under Get Data is missing the Power BI Dataset. 

2020-06-05_12-03-06.png

 

Anonymous
Not applicable

I have the same problem - In Excel: I can see how to create a Pivot based on a Power BI Dataset but can;t see how to create a query based on a Power BI Dataset?

 

As I indicated you connect to the Analysis Services data source (in import mode) and specify your own DAX query to be run.  In this case I chose EVALUATE DATES

 

lbendlin_0-1639745062440.png

 

Anonymous
Not applicable

Thanks Ibendlin.  If I click Analysis Services then I'm prompted for a Server and Database.  I don't know how I would find out what those are.  Do you know how I would find that out? 

The server and database fields need the connection string from your dataset properties

 

lbendlin_0-1639758741306.png

 

 

server:  powerbi://api.powerbi.com/v1.0/myorg/<workspace name>

database: <dataset name>

 

Good luck when you have situations with multiple workspaces or datasets with the same name...

Anonymous
Not applicable

Thanks Ibendlin,  I'll have a go next week 🙂

No, that's not what I mean.

Untitled.png

 

Hi,

I have the same problem with O365 E3 and PowerBi Pro license. Any suggest?

Thanks a lot

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors