cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ezadler Frequent Visitor
Frequent Visitor

Use Power BI as Data Source for Excel and Utilize Data Gateway

GOAL: Refresh Excel data in SharePoint Online through Excel in web part or Excel Services web page (not opening in Excel client) connected to on-premises data source.

 

I have set up a Data Gateway and the data refresh is working great.

 

I am trying to use this data source in the Data Gateway as the data source for an Excel file.

 

I have used the "Analyze in Excel" to connect to the data source, but this does not allow for refresh and I have found it to be very limiting.

 

According to this article: https://support.office.com/en-us/article/Use-external-data-in-workbooks-in-SharePoint-Online-8d7f5dc...

 

This should be possible. I just don't understand how to connect to the Data Gateway data source in Excel without using the Analyze feature.

 

eRic
11 REPLIES 11
Moderator v-yuezhe-msft
Moderator

Re: Use Power BI as Data Source for Excel and Utilize Data Gateway

Hi @ezadler

Could you please describe more details about your scenario? Based on your description, it seems that you want to connect to on-Premise data source from SharePoint Online and fail to refresh the data. If that is the case, I would recommend you post the question in the Microsoft Online: SharePoint Online forum at https://social.technet.microsoft.com/Forums/msonline/en-US/home?forum=onlineservicessharepoint . It is appropriate and more experts will assist you.

In addition, as far as I know, besides using “Analyze in Excel” feature, there is no other method that can be used to connect to the Data Gateway data source in Excel.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ezadler Frequent Visitor
Frequent Visitor

Re: Use Power BI as Data Source for Excel and Utilize Data Gateway

I can defintely post there. The reason that I posted here is that it appears this ability is directly related to the Data Gateway.

 

As seen on this post: https://support.office.com/en-us/article/Use-external-data-in-workbooks-in-SharePoint-Online-8d7f5dc...

 

The last line is what I am trying to achieve:

 

  • SQL Server data that is available in the Power BI Admin Center (this requires a subscription to Power BI for Office 365 and an administrator to configure the connection)

 

As it uses Power BI I was why I started here.

eRic
nicklosier New Member
New Member

Re: Use Power BI as Data Source for Excel and Utilize Data Gateway

Eric,

Were you able to acheive your goal?  I'm trying to accomplish the exact same thing.  We are migrating from SharePoint 2010 to online and don't want to have to remake Excel Services reports that query SSAS cubes.

Nick

Chip_Chipperson Frequent Visitor
Frequent Visitor

Re: Use Power BI as Data Source for Excel and Utilize Data Gateway

Did anything come from this? 

We are trying to use the data gateway in our local Excel workbooks, and have the same workbooks seamlessly use the data gateway after being published to Power BI. Is this possible? I think our question/problem is similar. 

Hikmer Regular Visitor
Regular Visitor

Re: Use Power BI as Data Source for Excel and Utilize Data Gateway

I have the same question, it works with PowerPivot but so far not with a driect conenction in Excel.  I'll open a ticket to verify.

ezadler Frequent Visitor
Frequent Visitor

Re: Use Power BI as Data Source for Excel and Utilize Data Gateway

According to the TechNet article https://technet.microsoft.com/en-us/library/dn879362.aspx the new Data Gateway should support the overall data access needs for Excel and Office 365.

 

I will be trying to implement this durning the next 2 weeks.

eRic
Chip_Chipperson Frequent Visitor
Frequent Visitor

Re: Use Power BI as Data Source for Excel and Utilize Data Gateway

Tried the latest gateway software and had the same issue. Anyone know when this will be made available and could elaborate on what the following means for the gateway

 

  • SQL Server data that is available in the Power BI Admin Center (this requires a subscription to Power BI for Office 365 and an administrator to configure the connection)
Hikmer Regular Visitor
Regular Visitor

Re: Use Power BI as Data Source for Excel and Utilize Data Gateway

From my ticket with Microsoft, the only two ways to connect an Excel file to live data using the gateway is via PowerPivot and PowerQuery (now called GetData in excel).  However, the PowerQuery option is limited to a 10MB file...so both these options are useless to me.

jjuelke
Advisor

Re: Use Power BI as Data Source for Excel and Utilize Data Gateway

Hi Hikmer,

may you be able to share the MSticket number to me? 

You have also no update on this? So the clarify my understanding: the statement from MS is that it's NOT possible to publish a excel workbook to 0365 which uses a live-connection to onPrem SSAS Cube, right? I have the same scenario, neither creating the connection directly to onPrem SSAS nor creating a connection to PowerBI.com ("Analyse in Excel") worked for me, the "OnPrem Data Gateway" logs show no activitiy, so i assume the gateway is not involved in the "Live" Request...

I will now try with an AzureAS Cube instead of onPrem SSAS datasource, but i'm afraid that will also not work Smiley Sad

Greetings,

Jochen

 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 86 members 1,480 guests
Please welcome our newest community members: