We use Power BI quite a bit nowadays to help customers visualize their data. Power BI is a great because it simplifies the querying and presentation of business data using dynamic and interactive visualizations. It’s relatively user friendly with drag and drop functionality and supports many different data sources such as SharePoint, Excel, SQL and many others.

One thing that we have seen cause confusion with Power BI (and its counterpart in Excel, Power Query) is how to handle multi-value fields where the data is stored in a single field as a delimited value. We see this quite a bit with SharePoint Choice and Lookup columns that allow multiple selections. You will also see the same behavior with Project Server and Project Online Enterprise Custom Fields (ECFs) that use a Lookup Table and allow multiple selections.

If you want to report on this data and use it as a Slicer and/or to get discrete counts from it, it’s not always intuitive on how to handle it in Power BI or Power Query.

Let’s walk through an example using a SharePoint list and Power BI.

First, let’s create a SharePoint Custom List called Training Requests.

1. In your SharePoint site, click on New > List
   
2. In Name, enter Training Requests
3. Click on Add column and select Number
4. In Name, enter # of Students
   
5. Click on Save
6. Click on Add column and select Choice
7. In Name, enter Training Topic
8. For Choices, enter the following:
   1. Power BI
      
   2. PowerApps
      
   3. Flow
      
   4. SharePoint
      
9. Click on Save
10. Click on Add column and select Choice
11. In Name, enter Locations
12. In Description, enter At which locations should the training be conducted?
13. For Choices, enter the following:
    1. Boston
       
    2. New York
       
    3. Philadelphia
       
    4. Washington DC
       
    5. Pittsburgh
       
    6. Raleigh
       
    7. Charlotte
       
    8. Atlanta
       
14. Click on More options
15. Change Allow multiple selections to Yes
16. Click on Save

Now that we have our Training Requests list created, let’s populate the list with sample data. Create 15-20 items with a variety of locations and # students. When finished, your data should look something like this:

Note how the Locations column is storing the data – it’s a multi-value field showing a comma delimited list.

Now let’s move to Power BI and see how it sees this data.

1. Open Power BI Desktop
2. First we need to query the data from our SharePoint list. To do this, on the Ribbon click on Get Data > More… > Online Services > SharePoint Online List > Connect
3. In Site URL, enter the URL to your SharePoint site and click on OK
4. If prompted for credentials, click on Microsoft Account > Sign In and login with your Office 365 account.
5. Click on Connect
6. In the Navigator screen, select the Training Requests list and click on Edit
7. We don’t need all of the list columns, so on the Ribbon click on Choose Columns > Choose Columns. Uncheck (Select All Columns) and check the following: Id, Title, # of Students, Locations, Training Topic
8. Note that Locations shows as a List and not the actual values. For now, let’s click on the Locations expand icon and choose Extract Values. Choose Comma for the delimiter. Click on OK
9. Click on OK then Close and Apply
10. On your Power BI canvas, add a Table.
11. In the Fields pane, select all 5 fields for Training Requests
12. You may have to resize your table to see all the data. Your Power BI canvas should look like this:

13. Let’s add a Slicer and select Locations
14. Note how the Slicer and the Table are showing the locations as a single text string. If you click on a value in the Slicer, it will filter on an exact match of that string, not each discrete location.

15. As you can see, Extract Values doesn’t give you the desired result if you want to find each discrete location. Let’s fix this so we get the results we want.
16. On the Ribbon, click on Edit Queries > Edit Queries
17. Let’s create a new table that will show each discrete location. To do this, right-click on the Training Requests table and click on Duplicate.
18. Rename the new table to Locations
19. In the Query Settings pane, click on the Gear for Removed Other Columns step
20. Uncheck all columns EXCEPT Id and Locations
21. Click on the Locations expand icon and choose Expand to New Rows. What this does is it creates a new row for each location specified for that item (id).
22. Click on Close and Apply
23. Next we want to create a relationship between the two tables. Click on the Relationships icon on the left.
24. You should see that Power BI automatically created a relationship for you. Double-click on the link and change Cross filter direction to Both and click on OK
25. Go back to the Power BI canvas
26. Delete the previous Slicer and add a new Slicer
27. In the Fields pane, expand Locations and check Locations
28. Now you should see discrete locations in your Slicer giving you the ability to find the Training Requests for each location like below:

29. At this point you can add additional visualizations such as bar charts and pie charts and finish building out your Power BI dashboard.