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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi cmncp,

        Are you able to use on-premises gateway to refresh your dataset?

        Thanks,
        Lydia Zhang