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.

 

It's only fair to share...
Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Pin on Pinterest0Share on Tumblr0

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

Leave a Reply

Your email address will not be published.


*


Comment moderation is enabled. Your comment may take some time to appear.