Forum Discussion

cmncp's avatar
cmncp
Helper III
9 years ago
Solved

Excel Sharepoint Data Refresh - Can't refresh your data

I have an excel file in Sharepoint Online, which has a simple table of data.  Using Power BI Desktop, I can connect to it and create a report.  After I publish the report to Power BI Service, I can s...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi cmncp,

    Firstly, for excel located in on-premises SharePoint site, we need to use Windows authenticaion. For Excel located in SharePoint Online, we need to use OAuth2 authentication.

    Secondly, to access on-premises data source, gateway is required when refreshing dataset. And both personal gateway and on-premises gateway require Pro.

    Thanks,
    Lydia Zhang

cmncp's avatar
cmncp
Helper III
9 years ago

I discovered that my issue was that the Sharepoint installation was actually on premise, not online.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi cmncp,

Please follow the guide in this similar thread to connect to Excel file located at on-premises SharePoint from Power BI Desktop. After you publish Power BI Desktop to Power BI Service, you would need to add the excel data source under on-premises gateway in order to refresh the dataset.

Thanks,
Lydia Zhang

  • cmncp's avatar
    cmncp
    Helper III
    9 years ago

    Sorry for the late reply.  When I use this method, I am still unable to refresh the data.  See the error message below.  The only options in authentication method are Anonymous and Windows.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi cmncp,

      In Power BI Desktop, you should use Windows authentication to connect to Excel file located at on-premises SharePoint Site. And when you refresh dataset in Power BI Service, you should use Windows authentication as well.

      Thanks,
      Lydia Zhang

      • cmncp's avatar
        cmncp
        Helper III
        9 years ago

        Hi Anonymous

         

        This is what I am doing.  I get the error message shown above when I am in the Power BI web service, trying to edit the connection details.  I choose Windows Auth, click sign in, and get that error.

         

        Chris