Forum Discussion

PushkarP's avatar
PushkarP
Icon for Helper I rankHelper I
5 years ago
Solved

Loading excel files in Power BI desktop

Hello,

I want to load data in few excel files into Power BI desktop. The excel files are store on SharePoint. To import the data I opened the SharePoint in Windows explorer and browsed the excel files. The data load was successful and I publsed the report to My workspace. However, while trying to referesh, I was prompted for a Data Gateway. I was expecting Data Gateway is not required as excel files are on SharePoint. Due to some constraints I cannot use a Data Gateway. Now I want to know.

1. Is there a way to configure so that the Data Gateway is not required?

2. Is there any other way (than using Windows Explorer) to get data from excel files, which are stored on SharePoint, in Power BI desktop?

Please advise.

Thanks in advance!

Regards,
Pushkar

Need Help

10 Replies

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, PushkarP 

    According to your description, I think you maybe make some mistakes when getting data, you can follow my steps and try again:
    1. Go to your Sharepoint page, copy the main URL, like this:

     

    1. Get data->Sharepoint folder, then sign in with Microsoft account:

     

    1. Transform data, select the file and table you want:

     

    1. Create the report and publish, you can go to the setting of the dataset, it says you don’t have to use the gateway to refresh this dataset(because data sources are in the cloud)

     

    You should edit the correct credentials, then you can set scheduled refresh.

    I guess that the main reason that the dataset needs a gateway to configure Scheduled refresh is using separate mashup queries to connect to on-premises and cloud sources.

    If you just connect to files from Share point Online, you won’t need a gateway to configure Scheduled refresh.

    Review data infrastructure dependencies

    More info about connecting to Sharepoint on desktop

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • PushkarP's avatar
    PushkarP
    Icon for Helper I rankHelper I
    5 years ago

    Update:

    Also, tried to load file with 'SharePoint Folder', however, an error '

    Test Table
    Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] The key didn't match any rows in the table.. '., occured.
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      PushkarP  First would like to check if the sharepoint which you are using is online or onpremise...For online there is no need of gateway and for onpremise you need a gateway


    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      PushkarP  you can make use of Web connector in power bi to connect the files from sharepoint....Also if you find that your sharepoint is onpremise which will need gateway configuration ...To meet your requirement you can paste the excel file to OneDrive  which will not need gateway configuration

      Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.


      Appreciate your Kudos, Press the thumbs up button!!👍



      • PushkarP's avatar
        PushkarP
        Icon for Helper I rankHelper I
        5 years ago

        Anonymous Thanks for your reply. I did try with web connector. I got following message when tried to authenticate with organisational account.

        I can access the site using browser so it is not an access issue. Can you please guide?