Using the Property Bag

There are several metadata properties for a Gimmal Records item accessed via the oData connector that may be included in a container referred to as a ‘property bag’. The steps below describe how to include values for these property bag items in a PowerBI extraction. Other reporting tools may have different capabilities related to using data in JSON strings.

This is advanced functionality and Gimmal recommends that only users with experience in PowerBI attempt this procedure.

Converting the Property Bag

The records PropertyBag column is a well-formed JSON string. It is necessary to convert this column’s data type to a more usable Table in Power BI. To begin, launch the Power Query Editor by using the Transform data button in the Home tab of the ribbon.

Next, right-click on the records query and choose Reference. This will create a new view of the records Table without modifying it. Rename your new table something meaningful - in this example it is renamed recordsPropertyBag.

Select recordsPropertyBag in the Queries pane, then click Advanced Editor from the Home ribbon.

You will now see the Power Query code editor for this table.

Copy and paste the following code into the code editor and click Done.

1. let

2. Source = records,

3. #"Renamed Columns" = Table.RenameColumns(Source,{{"Id", "RecordId"}}),

4. #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"RecordId", "RecordClassId", "PropertyBag"}),

5. #"Parsed JSON" = Table.TransformColumns(#"Removed Other Columns",{{"PropertyBag", Json.Document}}),

6. #"Expanded PropertyBag" = Table.ExpandListColumn(#"Parsed JSON", "PropertyBag"),

7. #"Expanded PropertyBag1" = Table.ExpandRecordColumn(#"Expanded PropertyBag", "PropertyBag", {"Key", "Value"}, {"PropertyBag.Key", "PropertyBag.Value"})

8. in

9. #"Expanded PropertyBag1"

Note line 5, the Table.TransformColumns function parses the Records’ PropertyBag column using the Json.Document function for each row in your data source. This ensures the well-formed JSON string is correctly parsed and represents the data accurately.

At this point, the recordsPropertyBag query contains one row per property for each record (i.e. the properties for a single record might use up 30 rows or more), with a single column for the property names, PropertyBag.Key, and a single column for the property values, PropertyBag.Value.

If this format is sufficient, you can now exit the Power Query Editor by clicking Close & Apply from the Home tab. Power BI will apply the changes to your new data model and once complete, you can configure the relationships as illustrated below in Model View.

Transform Data

The final step, if desired, is to further transform the data so that each row represents a single record, with a column for each property.

In the Home tab of the ribbon, click Transform data. Make sure the recordsPropertyBag query is selected then complete the following steps:

1. Select the PropertyBag.Key column

2. In the Transform tab of the ribbon, click Pivot Column

3. In the Pivot Column window that appears, select PropertyBag.Value from the Values Column drop-down, since this is where the values for the new columns are sourced from, then click OK.

4. Expand Advanced options and select Don't Aggregate. Then click OK.

5. You will now see that the recordsPropertyBag table has a single row for each record with each property in its own column.

6. Click Close & Apply in the Home tab to close the Power Query Editor and return to the Report View.