How to query Project Online multiple value fields in Power BI
This post addresses a need to arises when querying Project Online data via OData. It can also serve as a solution template for other data sources.
A number of projects in our portfolio have impacts in different countries. These countries are designated via a Country Project level multi-value (MV) custom field. When multiple values are selected from the dropdown list, it creates a comma-delimited list of values. For example, you could see Brazil, Canada, United States returned as a field value from OData.
One of the challenges is that this list can be any number of values and will grow and contract over time. Therefore, the technique should automatically adjust to the underlying maximum number of specified values.
For each MV field, a new data set representing all values in the MV field will be added to the data model. This new MV field data set will be a child to the original Project master data set. The relationship between the Project set and the MV set will be 1:N.
An inline function will also be used so that it can be executed for each record in the data set. This function will perform the split of values into separate columns.
Once split, the values will be unpivoted, creating a record for each ProjectId, MV Field value pair.
- Correct data set relationships. Initially, Power BI created the MV data set with a 1:1 relationship instead of a 1:N. Once corrected, all errors went away.
- You will need to watch refresh performance as you are basically querying the same data multiple times within the model.
Register here for this free session to see the step by step video and to download the PBIX file containing the data model with sample code. http://academy.tumbleroad.com/courses/how-to-parse-multi-value-columns-in-power-bi