Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Connect Sharepoint/OneDrive (Excel) to PowerBI

Hi,

Environment: PowerBI Online, Sharepoint Online, OneDrive Business, O365.

 

I'm trying to create a gateway to connect PowerBi Online to a Excel file in Sharepoint Online.

My report connects to SQL and an excel file. In PowerBI desktop I managed to connect by Web or Sharepoint Folder to the excel file.

 

My problem is that I cannot create a gateway to refresh the data. I tried to configure the gateway data source type with web and sharepoint, but both gives me error on credentials. The user has access to sharepoint folder and file.

 

Can anyone help?

Thanks in advance.

 

P.S.: In know I can use Personal Gateway, but because this does not work as a service, does not suit me.

 

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    If you did not merge/append the xlsx file (Cloud Data Source) and the SQL database (on-premises Data Gateway), you did not need to configure gateway for the xlsx file, please refer to this document: 

    https://docs.microsoft.com/en-us/power-bi/refresh-data#review-data-infrastructure-dependencies

     

    If a dataset uses separate mashup queries to connect to on-premises and cloud sources, Power BI uses a gateway connection to reach the on-premises sources and a direct network connection to the cloud sources. If a mashup query merges or appends data from on-premises and cloud sources, Power BI switches to the gateway connection even for the cloud sources.

     

    When using the web connector, we recommend use the Relative Path options to configure credential in service easily, such as following query:

    let
  Source = Web.Contents("https://XXX.sharepoint.com/sites/SiteName/A/B/C.xlsx")
in
  Source

     

    can be replaced with 

     

    let
  Source = Web.Contents("https://XXX.sharepoint.com/sites/SiteName",[RelativePath="/A/B/C.xlsx"])
in
  Source


    Or you can also use the SharePoint Folder Connector. If you want to know if it refresh correctly, you can run an on-demand refresh and verify the refresh history in "Settings" - "Dataset".


    Best regards,

     

6 Replies

  • mitsu's avatar
    mitsu
    Resolver IV
    6 years ago

    Hi , 

     

    You should not need any gateway to refresh ondrive files they refresh automatically every hour . You would only need gateway for the SQL connection .  

    Are you not seeing them being refresh automatically on the PBi Service .

     

     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Mitsu.

      If I configure with web option, I get this error:

       

      If I use the Sharepoint folder option, I don't need to select anything in the "red box"? It'll update?

       

       

      Also,

      Where I can check if have OneDrive refresh enabled?

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        If you did not merge/append the xlsx file (Cloud Data Source) and the SQL database (on-premises Data Gateway), you did not need to configure gateway for the xlsx file, please refer to this document: 

        https://docs.microsoft.com/en-us/power-bi/refresh-data#review-data-infrastructure-dependencies

         

        If a dataset uses separate mashup queries to connect to on-premises and cloud sources, Power BI uses a gateway connection to reach the on-premises sources and a direct network connection to the cloud sources. If a mashup query merges or appends data from on-premises and cloud sources, Power BI switches to the gateway connection even for the cloud sources.

         

        When using the web connector, we recommend use the Relative Path options to configure credential in service easily, such as following query:

        let
  Source = Web.Contents("https://XXX.sharepoint.com/sites/SiteName/A/B/C.xlsx")
in
  Source

         

        can be replaced with 

         

        let
  Source = Web.Contents("https://XXX.sharepoint.com/sites/SiteName",[RelativePath="/A/B/C.xlsx"])
in
  Source


        Or you can also use the SharePoint Folder Connector. If you want to know if it refresh correctly, you can run an on-demand refresh and verify the refresh history in "Settings" - "Dataset".


        Best regards,