Power BI and SharePoint List
BY: Collin Quiring
Here is an interesting one if you are using Power BI to access data from a SharePoint List. In this case the type of SharePoint is literally “List” and so this probably works differently than using a Document Library or other type of list.
We built a list and added a number of custom fields to it. We then used Power BI and The SharePoint Online List method to connect to it. All worked well. Our visuals and tables in Power BI were as expected.
We added other columns to the list, refreshed in Power BI and all worked well. The column appears and the data appeared.
We had one field called “Part” and it stood for the Part Number for the item in our list. It was a simple choice field where the user selects the appropriate Part Number from the list. It was decided that this was too time consuming based on the list and that we wanted to use a Lookup instead. So, we created a separate and different SharePoint List and we then added a column in our main list that looked to the other list for it’s value. This allowed it to work faster for the user and for the user to start typing in the Part Number and have the column jump to that number/letter as they are typing. For this change, we deleted the old field called “Part” and created a new field called “Part” that did the lookup.
All worked well in the list but when we went to Power BI to refresh – no joy. The field just flat out did not appear. No error messages either. The field just wasn’t there. We thought perhaps it was the delete and the re-add that did this to us – that we had removed something that Power BI was looking for and now couldn’t find. We tried all sorts of refresh and tried different methods to connect to the data, but still no change – the field just wasn’t there.
After some research, we discovered that some folks were getting around this problem by writing REST calls and queries that EXCLUDE the multi-select columns. That isn’t helpful in this case because we want to use that list. We thought about pulling in the other list (Part Number list) that we were using for the look up table. And, we tried that and found the ID to match up but that just didn’t feel like the best solution. Other options were to expand the properties for all items in the right list by clicking on the double arrows as show here:
We found a list item called Part and when we expanded the properties the items available never said “Part”.
We did find one that was called “Part.Title” and when we expanded that field we finally saw our solution.
Which, this then added that field for the Query and is now available in the list:
We were able to get our Business Intelligence working as expected and were able to make the visuals by Part Number as we had originally hoped!
Note – This did solve our problem of getting the data but it also increased the refresh time of the query, at least initially.