SCB (Statistics Sweden) live dashboard

In this Power BI desktop project, I will explore data from Statistics Sweden’s (Sw: Statistikmyndigheten SCB) Statistical database via their API. The database contains official statistics from Statistics Sweden and 15 other government agencies. The statistics cover many subject areas, and often over a long period of time.

I have added step-by-step instructions on how to establish a “live connection” between Power BI and the Statistic Sweden API. At the bottom of the page I have embedded the final Power BI dashboard.

Step-by-step instructions

Step 1 – find an appropriate table in Statistics Sweden’s Statistical database

In the statistical database it is easy to browse through the available subject areas and tables. Yeo reach the statistical database on this link: https://www.statistikdatabasen.scb.se/pxweb/en/ssd/

When you have found the subject area you are interested in, you can drill-down to a specific table that contain the information you want.

Step 2 – Find the table’s API URL and JSON query

1. Click on the link to the table as shown above.
2. Choose which variables you want to include in the table (see image below).
3. Click on “Continue”

4. Click on “API for this table”

5. We will need the URL and JSON query information as shown below. This will be specific for each table and each selection of variables.

Step 3 – Connect Power BI to Statistics Sweden’s API

6. Copy the JSON query as shown in the example above and paste it in an editor such as “Notepad++”.
7. Replace all “ with “” as shown in the example below.

7. Open Power BI
8. On the “Get Data” window, select “Blank Query” and click on “Connect“.

9. In the “Power Query Editor“, click on “Advanced Editor“.

10. In the “Power Query Editor“, click on “Advanced Editor“.
11. Delete any default text in the “Advanced Editor” and paste the example code shown below.

let
PostContents= "
{
  ""query"": [
    {
      ""code"": ""Anvandningstyp"",
      ""selection"": {
        ""filter"": ""item"",
        ""values"": [
          ""BNPM""
        ]
      }
    },
    {
      ""code"": ""ContentsCode"",
      ""selection"": {
        ""filter"": ""item"",
        ""values"": [
          ""NR0103BV""
        ]
      }
    }
  ],
  ""response"": {
    ""format"": ""CSV""
  }
}
",
    Source = Web.Contents("http://api.scb.se/OV0104/v1/doris/en/ssd/START/NR/NR0103/NR0103A/NR0103ENS2010T01Kv",
    [Content=Text.ToBinary(PostContents)]),
    #"Imported CSV" = Csv.Document(Source,null,",",null,1252)
in
    #"Imported CSV"

11. The sections marked with a different color in the above code example need to be  replaced with your JSON query and URL
12. Note that you need to change the part “”format””: “”px”” to “”format””: “”CSV”” in order to get the data in a format which easy can be read by Power BI.
13. When you have made the above changes, click on “Done“.

14. Now the data will be loaded into Power BI. Before we can start visualizing the data, we need to do some cleaning and formatting changes. E.g. promoting the headers, transpose rows and columns, split some text, replace some text/values and hide and rename a few columns.
15. In building the final dashboard shown below, I have gone through this process five times (i.e. one for each table in the statistical database I would like to receive data from)

The final dashboard