The following should help with any of our connector types, where the data is stored in a column (or columns) in JSON format.
Note that the document presumes at least a basic familiarity with Power BI Desktop and its Power Query Editor
Identifying the data required
Open Power Query Editor, if it isn't already.
The example uses Simpro® data, but the source does not matter as the process is the same.
Two Example Columns
There are two columns containing JSON; in one case we can see the data (1); the other column (2) appears to be empty, but we know some tags should be present; we require the data in the TagData column.
Finished Example
What we need is shown in the following image. We have the Job 'id' and the TagData ID easily accessible.
We're going to create a mapping table, based on the data available in the SimproJob table. As the above example displays, all we need is the 'id' of the Job and the TagData ID.
First Create a Reference of the table.
Right click on the table (SimproJobs in this example) and select the option 'Reference' as show in the image below.
Then rename: right click and select rename as displayed in the image below.
(The new name should identify the purpose of the new table reference; 'SimproJobTagsMapping' or similar describes its purpose.)
Remove the columns not Required
1. The renamed table.
2. The selected columns. (Note that your columns may be different.)
3. Right click on one of the selected columns > Remove Other Columns.
Removing Empty Values
After removing the other columns, we are left with just those we selected previously (image below).
Left click on the little down arrow (1) and Remove Empty (2).
Now to Transform the JSON
Now we can see the data, which will contain an identifier, such as 'name' or in this case 'ID'.
Right click on the column (1 in the image below) > then select Transform > and then JSON (2).
Expanding New Rows
The first part of this process is to:
Click the button with the Left & Right arrows (1 in the image below) > then select the Expand to New Rows (2).
Now Select the Required Columns
The second part of obtaining the required columns is:
Again, click the button with the Left & Right arrows (1 in the image below).
2. Select the columns required. (There is only one here called 'ID', so we're good.)
3. Click OK.
There may be empty values at this stage (4); we can deal with them next.
Removing the Empties again.
Left click on the little down arrow (1) and Remove Empty (2).
What does the column data refer to?
Where there is an ID, as explained previously, this is likely to reference a different table.
In our case here, the ID in TagData refers the the 'id' in the SimproProjectTag table, which is shown below.
We can see the ID 140, and the name: 'Example Name'; this is referenced in the image above, where we stated to note the id for later.
If you wish further assistance, please reach out to us: support@tuggerapp.com.
This completes the tutorial on extracting complex data.