One to Many Relationships
In Talis Aspire we often have fields in the exported data which contain multiple values.
These values are separated using a ‘
; ’ (Semicolon and space).
Common fields where this might apply are:
This happens because we are compressing a field which has a one-to-many relationship with the list or item into a single column of the report.
To work with this data in a Business Intelligence data processing tool, you would want to restore the one-to-many nature of the relationship by creating a lookup table. We want to have a key on one side and a value to lookup in a 2 column table. We won’t mind if the key repeats multiple times.
The steps described in this video example using Power BI are broadly as follows:
- Select only the columns that we are interested in from the source data.
- Split the column with multiple values into as many columns as needed. This is sometimes called ‘unflattening’ a row.
- Unpivot the split columns and leave the key column alone.
- Update the relationships in the data model.