SharePoint lists can be a popular data source for Power BI reports. However, when connecting to SharePoint lists, there are two options available. This article helps explain the differences.
I will focus on the “SharePoint Online List” which is for cloud-based SharePoint sites. It offers up two implementation versions as shown in the following image.
Let’s walk through the difference. To help explain this, we’re going to create a very simple SharePoint list with four columns, “Title”, “Project Name”, “End Date”, and “Notes”. (Note: the “End Date” column was created with the name “Date” originally, but renamed to “End Date” – I’ll explain why later.)
I’ll now walk through the differences between Implementation 2.0 version versus 1.0. I’ll begin with 2.0 as this is the latest and the simplest.
Implementation 2.0 Version
When using Implementation 2.0, there are two “View Modes” available:
- “All – Retrieve items from a SharePoint List”
- “Default – Review the columns set in the ‘Default View’ of a SharePoint List”
The main difference between these modes is that the “Default” mode will only pull into Power BI/Power Query the columns that are in the “default” SharePoint list view. And, unless you start creating different views for your SharePoint list, the “default” view will be just those columns that you see when you created the list originally.
Using my sample list above, choosing the “Default” mode will only pull into Power BI the four columns in my list. However, behind the scenes, SharePoint lists contain a number of additional standard columns that aren’t displayed by default. Below is a sample of how you can customize which fields are shown for a SharePoint view. Note that although 12 columns are shown in the image, there are many more.
Thus, if I connect to my sample SharePoint list, select “Implementation 2.0” and then select the “Default – Review the columns….” mode, the result in Power Query will be just those columns as shown in this image. The one exception is that the “ID” SharePoint field is also included.
Had I selected the “All – Retrieve items…” mode, then all those additional fields, such as “App Created By”, “App Modified By”, etc. would also be included.
You will also notice that the column names in Power Query/PBI match the exact names that I have in my SharePoint list. When I get to Implementation 1.0, you will see that this will not be the same.
Overall, using the “Implementation 2.0” is the best approach when you are only interested in the columns and data within your list. Furthermore, by using the “Default” mode, this allows you to further reduce the data that is pulled into Power BI.
Implementation 1.0 Version
This was the original option for connecting to a SharePoint Online list. It allows you to connect to the list and all of the fields within the list as well as additional hidden metadata available with that list. Furthermore, there is no option to limit what fields are pulled in. All cleanup has to be done in Power Query.
In other words, when you connect with this version – you get the columns within your list – plus a lot more. Furthermore, the column names in Power BI/Power Query will be the true column names stored in SharePoint. What do I mean by this?
- SharePoint removes any spaces being the scenes. So a column name of “Project Name” will be stored internally as “ProjectName”.
- If you rename the column after it is created, the original field name is still maintained. For example, in my example above, the “End Date” field was originally created with the name “Date”. I then renamed it to “End Date”.
These two points are important to know when you get into the details. Let me provide a visual example where you can find the internal column names. In the picture below, when I view the List Settings in SharePoint and hover over the “End Date” column, I can see how SharePoint still maintains the original name called “Date”.
Let’s get back to connecting Power BI to the SharePoint list through Implementation 1.0. The two main differences you will note is:
- The column names will reflect the internal names stored in SharePoint (i.e. no spaces and the column name that was originally created); and
- You will see a significant number of additional hidden columns, including additional metadata related to each item. Much of this will then need to be removed in Power Query.
Below is a comparison of some column names between 1.0 and 2.0. You can see both the difference in which columns are pulled into Power BI/Power Query as well as their respective column names.
This begs the question – why would you ever want to use Implementation 1.0 as a connection when 2.0 is so much cleaner? I’ll discuss that in the next blog post.