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...
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,

     

mitsu's avatar
mitsu
Icon for Resolver IV rankResolver 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
      Icon for Community Support rankCommunity 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,

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks.

        I was able to configure correctly via WebConector (but without Relative Path) and Sharepoint Folder Connector. And yes, it refreshes.

        Why do you recommend configure Relative Path?

        When configure Web Connector, I get this:

         

        let
    Source = Excel.Workbook(Web.Contents("https://XXXXXX.sharepoint.com/sites/MngControl-TarefasFechoMs/Shared%20Documents/Tarefas%20Fecho%20M%C3%AAs/TarefasFechoMes.xlsx"]), null, true),
    TarefasFechoMes_Sheet = Source{[Item="TarefasFechoMes",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(TarefasFechoMes_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Year", Int64.Type}, {"Month", Int64.Type}, {"Business Unit", type text}, {"Task", type text}, {"Dependence", type text}, {"Status", type text}, {"End Date", type date}, {"Evidence", type text}, {"Comment", type text}, {"Type", type text}, {"#", Int64.Type}})
in
    #"Changed Type"

         

        I tried to configured the Relative Path, as mencioned, but get authentication error.