by Tom M.

A REST API can simplify the building process of the data model. This is because you don't need to know the, sometimes complex, structure of a database. Also, data can be processed and formatted which might be complicated through SQL. The data provided in the JSON can contain a lot more data than that's required for one report but might be helpful for another report.

Importing the received JSON data is basically the same as importing data from a flat file. The main idea is that the data is converted from a single wide table to multiple small tables that are related to each other. This limits the usage of memory and can increase the performance of the report drastically.

Importing the data
Now, let's get started with importing some JSON data through a REST API. In Power BI Desktop, start a new report and click the "Get data" button in the toolbar which opens the "Get data" dialog. In the dialog search for "Web" and click "Connect".

A faster way is to open the dropdown menu of the "Get data" button and select "Web".

This opens a new dialog where you can specify the URL of the REST API that you want to call.

The basic version will do in case you just need to call the API without any authorization, other required headers or parameter fields that are used to build the request URL. In case i.e. authorization is required, the "Advanced" dialog allows you specify the "Authorization" header with i.e. a value like "Bearer {token}".

For instance specify https://swapi.co/api/people as the URL which retrieves all characters from the movie Star Wars.

After clicking the "OK" button the data is retrieved from the REST API. When successfully retrieved, the "Power Query Editor" becomes visible and shows the data. The part that interests us is the "results" field which contains a list. Click on the "List" value of the results field which shows the characters as a list of records.

Of course, this isn't useful and readable, so now this list must be converted to a data table.

Converting JSON to a data table
Start by clicking on the "List" column header. The "To table" button in the toolbar now becomes enabled. Click on the "To table" button which shows a new popup dialog where you specify some transformation rules. For JSON however, no specific rules must be specified and clicking "OK" is enough to convert the record list to a table.

This is how the data now looks like. So instead of a list of records, it's a data table with a single column containing records.

That's nice but still not useful. What we really want to see is the actual JSON record data as columns. This is easily done by clicking on the double arrows (= expand) button in the column header.

This opens a new popup window that allows you to select the JSON data that you want to see as a data column. The "Use original column name as prefix" checkbox adds the column name (column1) as a prefix for the new created column names. Personally, I prefer to untick this checkbox because the column names become too long. This makes it difficult to find a column that must be used in a report. The panes must also be resized so you can read the whole column name, since the actual column name is at the end of the line.
So, uncheck "Use original column name as prefix" and select the fields you want to convert to columns.

When you scroll to the right, you'll see that there are four columns called "films", "species", "vehicles" and "starships" that also contain lists. You can expand these lists as records again by clicking on the column header and press the "Expand" button. This shows a popup menu with the options "Expand to New Rows" and "Extract Values…". Click the "Expand to New Rows" menu item and you'll see that additional records are added.

Do this again for the other three columns. Now the data model starts to look as an actual data table.

On the right side of the window there's the "Query settings" panel. This panel contains the properties of the data table which is in this case the name of the table. Here you can rename the table when this is required.
The panel also contains the "Applied steps" section. This list contains all the steps that are currently taken to convert the received JSON data into a data table. In case you want to remove a step, you click on the "Delete" icon in front of the step. In case you want to add or remove a column from the data table you can press the cog icon of the corresponding step.

This opens a similar dialog like the expand button where you can remove or add the additional column. When originally expanding the column only a "Use original column name as prefix" checkbox was available. Here you can change or clear the column prefix when necessary.

Data types and data manipulation
When you take a closer look at the column headers you see at the end of the header a dropdown arrow. This allows you filter and/or sort the data. The filtering and sorting can be done for multiple rows.
In front of the column name is an icon. This icon indicates the current data type which is by default "Text". Click on the icon and choose a matching data type for this column when necessary.
In case a column contains date/time values, but you only want to see the date part. You set the data type to date/time (not "date", this will show "error" since the data can't be parsed to a date). Next, you click on the "Date" button on the "Transform" toolbar tab which opens a dropdown menu. Here you can select "Date only". You'll see that the time part is removed from the data. The data type of the column can now be set to "Date".
The "Transform" toolbar contains some other useful features to manipulate the data. You can extract parts from a text value, replace values, …
I.e. when a column contains status codes which are represented by a letter, then the values can be replaced with i.e. a description of the status code. You simply select the column where you want to replace the values and click on "Replace values" in the "Transform" toolbar.

This shows a new popup dialog where you can specify the value that must be replaced and of course the new value. You can also replace parts of the column value when that's required.

Multiple data tables
There are a few methods to create multiple data tables. All depends on the data that's specified in the JSON data.

The first way is to simply call another REST API function to retrieve some additional data which can be transformed into another data table just as we did with the first table. To add another REST API call you click on the "New source" button in the "Home" toolbar tab. Or right-click in the "Queries" pane on the left side of the screen and select "New query" > "Web" from the popup menu.
In our example the "people" table only contains data about the characters and has a link to other tables by means of a REST URL. To create the other data tables, we do the same as we did for the "people" table. We call a new REST API and transform the data again until we end up with multiple data tables that can be linked to each other. So we can call https://swapi.co/api/vehicles to create a "vehicles" table, https://swapi.co/api/films to create a "films" table, https://swapi.co/api/planets for a "planets" table and https://swapi.co/api/starships to create a "starships" table.

In case the received JSON contains data of a master table as well as some detail table(s). The data can be split over multiple tables as well. I.e. the JSON data contains order information. Besides the order data, the details of each order are also provided, as well as none to multiple notes for each order. This means the order data can be stored in one table, the order lines in a second and the notes in a third data table which are then linked to each other.

All the data for these additional tables are already available in the current data table. So instead of calling the REST API again, we can duplicate the table we already transformed, into a new table. This is done by right-clicking on the table in the "Queries" pane (which is on the left side of the screen) and select "Duplicate". In this example we can create two duplicates, one for the order details and one for the notes.

Next, we can further transform each data table separately, so it only contains the data related to this table.
In the "Queries" pane, click on the first table. In the "Query Settings" pane (on the right side of the screen), you can rename the table to a proper name. I.e. "Orders", "OrderDetails" and "Notes". Next, we can start optimizing the tables by only using the columns that are related to this table and that's required for the report. If the "Orders" table contains i.e. some customer information which isn't required in the report, the columns can be removed as well. If they are required later, the columns can be added again by clicking on the cog icon next to the corresponding "Applied step".

To remove the columns, you press the "Choose columns" button on the "Home" toolbar tab. This shows a new dialog that allows you to select the columns you want to keep. Click the "OK" button to reduce the data table.

The "Orders" data table is now reduced to:

Since the order details and notes we also available in the data set, the order lines are specified multiple times. This causes conflicts when the tables are linked to each other (a many to many relation). Since these duplicate records aren't required anyway, they can be safely removed. This is done by clicking the "Remove rows" button on the "Home" toolbar tab. This opens a dropdown list where you can select "Remove duplicates".
The same actions can be repeated for the "OrderDetails" and "Notes" tables which result in:

The "Notes" table contains an empty note in case there we more order detail records than notes available. These aren't duplicates or blank records due to the "OrderId" column. You can right-click on the "Note" column and select "Remove duplicates" here. This removes the records with duplicate "Note" values. However, when a note contains the same text, these notes are removed as well.

Now the data tables are ready to be linked. Click on the "Close & Apply" button in the "Home" toolbar tab to return to the "Power BI Desktop" application.

Data table relations
After the query changes are applied by the "Power BI Desktop" application, the relations can be created between the tables. To get an overview of the table relations, click on the "Model" button in the left pane.

When two tables have a column with the same name and data type Power BI automatically creates a link between these tables.

When no links are detected you can select and drag the primary key field from one table to the foreign key field of the second table.

In case of a many-to-many relationship, the "Create relationship" dialog is shown. These types of relationships must be avoided and require a link table but this is for another blog post.

You can double-click on the created link to edit the relation in the "Edit relationship" dialog.

In the "Cross filter direction" dropdown, you can specify the data flow. You can specify whether the "Films" table must be updated when a record in the "People" table is selected and vice versa or only in one direction. I.e. only when a record in the "People" table is selected.

In case you want to go back to the "Query editor", click on the "Edit queries" button in the "Home" toolbar tab.