Loading Excel Files from Sharepoint

Load Excel Files from SharePoint

This is a quick tutorial on how to load Excel files from a SharePoint page.  SharePoint is a nice landing place for your data because it can be connected to the PowerBI.com service and thus can be used to schedule refreshes of data within your company (if you already have a SharePoint o365 account).

This tutorial will be a slightly different than my previous tutorials as I don’t have a publicly available SharePoint site that can be used to connect to.  So you will have to slightly adapt what I’m presenting to you to fit your particular SharePoint needs.

First you must start off with a SharePoint with a document library that includes an Excel file.

Sharepoint Location
Sharepoint Location

The document library is titled SampleDocs, and the file we want to bring into PowerBI is called SampleData.

Clicking on the Home in the left navigation will take you to the home location of the SharePoint site.  Copy down the HTML site address from your browser of this location it should look similar to the following:

https://partner.onmicrosoft.com/sites/[Your Site Name]/SitePages/Home.aspx

Open up PowerBI Desktop and on the home ribbon click Get Data.  Highlight the SharePoint Folder and click Connect to continue.

SharePoint Folder Connection
SharePoint Folder Connection

Upon clicking connect you will be presented with another screen asking for the SharePoint folder location. In the URL window you will add the SharePoint site that we identified above.  However, it is important to note that you don’t need the entire web address.  Rather PowerBI only needs the specific site name, thus all that needs to be inserted into the URL field is highlighted below in Red.

https://partner.onmicrosoft.com/sites/[Your Site Name]/SitePages/Home.aspx

The ending “Sitepages/Home.aspx” can be removed.

Enter Shortened Site URL
Enter Shortened Site URL

Clicking ok will present a authentication screen.  Depending on your company or SharePoint authentication you will need to enter the credentials to log into the SharePoint Site.  You may have to try a couple different connection methods until you are able to properly connect to the SharePoint site.  In my example I had to select Organization Account then click the Sign in.  I signed in with my credentials given me via my I.T. group.  Also, I had to use the drop down to select the proper level to apply the settings.  I used the same address as listed above: https://partner.onmicrosoft.com/sites/[Your Site Name]/

User Sign In Page
User Sign In Page

After signing in click Connect to proceed.  PowerBI Desktop will then load all the files from the SharePoint site in a preview window. Click Edit to modify the query.

Query Editor View
Query Editor View

We can now see our SampleData File and the folder path.  Each document library will be a separate folder path, thus if you have multiple document libraries then you will have all the files in those different folder paths.

Next click the double down arrows to load the excel file.

Load File
Load File

Power BI Desktop will then go to the SharePoint site and download the information inside your excel file.  For my data I have all the information retained in a table within my excel document.  The table name is call MyDataTable.  Thus, clicking on the Table link in the MyDataTable row I will be able to open all the data within this table.

Load Table of Data from Excel File
Load Table of Data from Excel File

Finally the data is loaded from the excel table.  Click Close & Apply on the Home ribbon to load the data into PowerBI.

Note: It is always important to check your columns and verify that your data types are correct.  Highlight each column and make sure you select the proper Data Type for each column.  Data Type can be found on the Home ribbon. 

Final Load Data
Final Load Data

Thanks for visiting.  Make sure you stop by again for more great tutorials.

 

35 Comments

  1. Hi Mike, Thanks for the post. I try it, in Power BI Desktop works perfekt in power BI online it is not possible to refresh the dataset. It commes an error that said: “Failed to update data source credentials” Do you know why?

    thanks for your help

    • I have seen this error multiple times. Here are a couple of ideas to help you with the credentials error.

      First, you will want to make sure that your SharePoint is part of the O365 service. SharePoint can be implemented at companies only on premise. When the SharePoint installation is only on premise there isn’t away to connect to PowerBI.com the service via a refresh. If your company is running SharePoint you will need to make sure your running the latest version of SharePoint if it is hosted on O365.

      Second, when you connect to SharePoint in the desktop there will be multiple credentials selections. Depending on how your organization is set up you may need to try different combinations of credentials to allow you to connect to SharePoint. When I connect to my installation of O365 SharePoint I use the credentials for “Organizational account”. Also, there is a drop down that selects the online location to apply the settings. When I sign in I use the most detailed credentials such as (https://my.sharepoint.com/sites/(my site name).

      Last, if you are having a refresh issue on PowerBI.com on your loaded dashboard you can re-enter the credentials for that dataset. After you have logged into powerbi.com click the gear icon on the right side of the screen. Select the Settings button from the drop down list. Then select Datasets which are listed at the top of the window. On the right side of the screen select the dataset which is having credential issues. Then under the drop down click Edit Credentials, and re-enter your SharePoint credentials.

  2. Hi Mike,

    I am actually having the same issue. I’ve tried in two accounts: a OneDrive for Business trial account (with O365 not activated) and in a corporate O365 with OneDrive for Business (don’t know how is the integration between O365 and OneDrive/Sharepoint). In both cases I face the error of the data source refresh error. The data source is called ShrepointListOnPremise and it seems that the problem is happening to many people these days. The issue has been reported here: http://community.powerbi.com/t5/Issues/Invalid-credentials-on-Sharepoint-folder/idi-p/93059. Anyway if you find a workaround for this, would be much aprreciated!
    Regards, Fran

    • Thanks for the feedback. I’m sorry your having some serious issues with the connection piece. In my organization I am not the O365 administrator thus, when I ran into connectivity issues I had to engage my I.T. group which in turn had to work with Microsoft. I believe there is a setting in either the administrator part of PowerBI.com or on SharePoint that allows the two services to talk to each other.

      One way of testing this is to log into PowerBI.com and try to connect to the SharePoint Team Site. This thread may help you out. If you are unable to connect to your SharePoint team site through the PowerBI service then there is most likely a setting not turned on. You would need to engage your I.T. administrators to trouble shoot this. If you are able to connect to the SharePoint team site, then the credentials that your using to log into the SharePoint site are most likely not correct. You would need to try different log-ins such as Organizational. Hope that helps.

  3. Great read. Sadly for me it doesn’t work. I’m copying the URL that is similar to the one shown here of my own Sharepoint but the system is giving me an error message saying “The URL isn’t valid. Please enter the site’s root URL only.

    • I was having this exact same issue when i first started trying to load data from SharePoint. Here are a couple of things to check.
      1. You will most likely need to be an admin of the site. I have had issues with users trying to connect without having a higher level of access to the site.
      2. The SharePoint I am trying to connect to is within the O365 system, therefore there maybe issues your having which is due to a on premise install of SharePoint.
      3. Make sure your cutting down your SharePoint address enough. Start at the home page for the full address. My site home address is something like https://my.company.com/sites/Analytics/SitePages/Home.aspx, then I needed to cut down this address to the following for it to work, https://my.company.com/sites/Analytics… Hope those trouble shooting options help.

    • I have not tried an on premise Share Point. From working with enterprise gateways, my assumption would be that an enterprise gateway would need to be installed in order to access the data via an on premise Share Point Page.

  4. I have tried all those step but it still not solve the issue here is error”can’t use URL from sharepoint”

    • Make sure you have access from your administrator. If you are not a designer or higher level within the SharePoint site you are trying to get data from I have found there are issues accessing the data. Also, this is an example from SharePoint online, from the office 365 suite. I do not believe this example works for an on premise SharePoint site.

  5. Hi Mike – I’m trying to load a pbix file and it seems like it might not be supported. The ultimate requirement is to use sharepoint (or alternative) to store pbix files for version control. Using desktop or service (to ultimately create/publish apps), connect to the pbix on sharepoint (using get data) and load the file. Then as we make changes to the pbix in sharepoint, have it automatically update in desktop or the service using a refresh. If that doesn’t work we could reload the file. I tried your instructions above but for a pbix file but it looks like it’s not supported when i click the double arrows in the editor.

    Any suggestions on how to accomplish what I’m looking for. If you have suggestions other that sharepoint, I’m all ears. I hope this makes sense 🙂

    • Super good question, I was just working on this exact issue today. Then I found this article. It’s not super clear, but I think this feature is available now.

      https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-desktop-file-onedrive/

      Here are the rough steps.
      1. Log into the PowerBI.com service
      2. Create a new workspace (it takes about 30 mins) for the files option to show up.
      3. Then click the ellipsis next to the newly created workspace, this should let you see a item called Files.
      4. Click the files option, then a sharepoint page should open up allowing you to add files
      5. Place your PBIX file in the sharepoint library
      6. Go back to PowerBI.com then open up the workspace
      7. Click get data
      8. Then click on files
      9. You should see a new option for one drive with your workspace name
      10. Your PBIX file that you uploaded to the sharepoint workspace should be there. Then you can click the PBIX file there and load it to PowerBI.com.

      good luck, I hope that helps

  6. Hi I have issue finding an error when schedule refresh “Unable to connect error”, my data source is sharepoint and I already check URL and it is correct, Unable to find where is the problem . Is any other way to solve it.

    • One thing you will need to make sure is that you are using SharePoint online. Power BI.com the service needs to connect to the Microsoft offered SharePoint Sites. If your SharePoint sites are locally hosted within your company then the PowerBI.com service will not be able to connect. An alternative approach would be to put the files you need to access in PowerBI.com directly into the attached workspace of the PowerBI work group. Here is an article that shows you how to access this SharePoint site. https://powerbi.tips/2017/08/best-practices-for-sharing/

  7. Hi, I have one more query .. My excel file is on sharepoint with 5 or 6 sheets in it. After importing data in power bi , I only need 2 or 3 sheets from my file and I don’t want to combine them , I want them as a separate 3 tables.

  8. If I import an excel file in power BI from the share point, does it dynamically change data ON REFRESH whenever data in excel got changed. or do we have to publish it again and then refresh it in order to get the current data.

    • The data will only change when the report is refreshed. You can schedule up to 8 refreshes for each dataset on PowerBI.com If you schedule a refresh, and the excel data changed, then the change will be loaded into the data model. It does not harm things to have the schedule refresh conducting a refresh if the excel data hasn’t change.

      In this way you can connect your data to SharePoint online, set up the dataset to refresh and then modify the excel files until your heart is content. When the Power BI data model is refreshed it will automatically pick up the changes. If you need to trigger this on demand you can click the ellipsis on the dataset and select refresh now. This will manually force the refresh to happen. When you do this, it does not count against your scheduled refresh limit per day of 8.

      • Not an affliate or anything of the sort, but think Power Update is worth a mention here as it can automate refreshing data sources in Excel workbooks stored on SharePoint which is pretty handy to anyone working with this set up.
        Cheers

  9. Hi Mike
    I am trying to connect data from 23 different folders on SharePoint to PowerBI. There is no issue with connecting the data. However working with them is bit hard. Reshaping the data using query editor takes a lot of time. Whole power BI system works slow. Is there any workaround that?

    • An alternative approach would be to not use Sharepoint but use Azure Blob storage to access the files. I have use Azure blog storage a couple of times but not a ton. This might speed up some of your performance issues.
      One thing I have noticed is if the data you are collecting from sharepoint requires a lot of transformations the data loading process can get slow, as you have described. When I have had issues in the data transformations in the Query Editor, I have had to build more robust data collection items. Thus, I have created a process to load data into a SQL server then do the transformations using views, and finally pull the data through to Power BI.

  10. Hi Mike
    I am trying to pull multiple excel files from SharePoint, more than 10. Doing so editing the data, reshaping the data in power bi is very slow. Every operation takes a lot of time. Another thing when I try to apply the changes after editing I get the error “Failed to save data modification to server. Error returned :’OLE DB or ODBC error….”. Is there any simpler solution for pulling data from multiple excel sheets stored on SharePoint?

    • It is likely that the error is coming from one of the files where there is un-expected data with the file causing the error. As far as simpler solutions to pulling Excel files, I have not found any other such methods. If the Excel data is coming from an automated system or a report extract, the data would load faster if you could instead connect to the source and not use excel.

      An alternative could be to use CSV files instead, there is less bloat in an CSV file compared to an excel file.

  11. Hi Mike, super cool that you are still active here answering comments that come up even years later. I have one.

    I am trying to connect to a SharePoint Folder to get to an excel document. When I input the root folder I get the thousands of documents that are across all the different locations. How do I get around this? I can’t input my specific folder that I want to go to, only the root of the whole SharePoint. What am I missing?

    • Yes, this is possible. When you open up SharePoint there will be a ton of files. When you first open up the Power Query Dialog there will be one column on the far right that has the folder name. You can filter this column down to the folder that you are interested in. This will pair down the full list to something smaller and more manageable.

  12. I am having an issue while uploading folder from Sharepoint into Power BI .
    The table is blanks like i have nothing in my Sharepoint which very weird
    Do you guys have any clus ?

  13. This was helpful and I was able to extract a single Excel file, although it would be easier if we could point to a specific Sharepoint path/filename. I tested with my locally installed Enterprise Power BI gateway, and it seems to work. For Sharepoint credentials, I used Authentication method “OAuth2” and Privacy Level “Organizational.” After that it prompts me to login and I use the same MS account which can access the OneDrive folder. Manual refresh worked fine, and I’ve setup a schedule.

  14. Hi Mike, the effort you put in here is unbelievable.
    I have a bit more general question. I am getting data from a SharePoint Folder. Works fine. The data are two tables, one for Budget figures and one for actual figures. The table is only having data for this year. Is there any possibility to add a new table, when the new year starts, and add this data to the existing dataset or do i have to do a new dataset, Reports dashboards etc. every year?

  15. Hi, I’ve connected successfully Power Bi with SharePoint, and Works fine for small Excel Files. But we have very large Excel Files in one library. 5 workbooks=160MB in total. It takes hours to read. Any hint on processing files with large size will be appreciated: Load from file server? upload first to a database?

    Thanks

    • To be honest when things get to this size, it is a good time to look at using data flows, or start looking at pushing that data into a database. I have not had great success with really large excel files refreshing into Power BI Desktop using SharePoint. One way of trying to optimize a bit might be to save out the excel files into CSV files, and only taking the data that you need for reporting. Excel files can get quite large when there are multiple formulas and additional feature design to make a useful excel workbook.

  16. I have a situation wherein all the files in SharePoint are visible in Power BI. However, when I try connecting through excel power query two files that are needed are missing. Any suggestion?

    • I have not seen this issue before.

      You can try to copy the M code out of Power BI Desktop and add it to Power Query in Excel and see if that fixes the issue.

Comments are closed.