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).
First you must start off with a SharePoint with a document library that includes an Excel file.
The document library is titled SampleDocs, and the file we want to bring into Power BI is called SampleData.
Clicking on the title on the SharePoint page 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. however, it is important to note that you don’t need the entire web address. Rather Power BI only needs the specific site name, thus all that needs to be inserted into the URL field is:
https://[Website Address]/teams/[Your Site Name]/
Open up Power BI Desktop and on the home ribbon click Get Data. Highlight the SharePoint Folder and click Connect to continue.
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.
Clicking ok will present an 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.
After signing in click Connect to proceed. Power BI Desktop will then load all the files from the SharePoint site in a preview window. Click Edit to modify the query.
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.
Filter and search for the relevant file, click the "Binary" text to load the specific excel 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.
Finally the data is loaded from the excel table. To make the first row of the data table the column header, browse to "Transform" tab and select "Use First Row as Headers"
Click Close & Apply on the Home ribbon to load the data into Power BI.
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.