Get data from Web

[kokos88]

Hi,

does anybody of you have an idea how to import data from the table on this website to Power BI? I can't do it using Get data from Web. I don't want to use excel file. Any other idea how it can be done? 

StatLine - Monthly labour participation and unemployment

Thank you

[MasonMA · Accepted solution]

In Power BI use Get Data- 'OData Feed', You sample website OData root URL is 

{
  "odata.metadata":"https://opendata.cbs.nl/ODataApi/OData/80590eng/$metadata","value":[
    {
      "name":"TableInfos","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/TableInfos"
    },{
      "name":"UntypedDataSet","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/UntypedDataSet"
    },{
      "name":"TypedDataSet","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/TypedDataSet"
    },{
      "name":"DataProperties","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/DataProperties"
    },{
      "name":"CategoryGroups","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/CategoryGroups"
    },{
      "name":"Sex","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/Sex"
    },{
      "name":"Age","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/Age"
    },{
      "name":"Periods","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/Periods"
    }
  ]
}

Paste URL (For example, for 'TypedDataSet' it's 'https://opendata.cbs.nl/ODataApi/OData/80590ENG/UntypedDataSet'). repeat this process for all url you need and load tables to Power Query for transformations of your data there before creating a model for reporting. 

[kokos88]

Thank you a lot for your answers. It works perfectly!! 🙂

[MasonMA · Accepted solution]

In Power BI use Get Data- 'OData Feed', You sample website OData root URL is 

{
  "odata.metadata":"https://opendata.cbs.nl/ODataApi/OData/80590eng/$metadata","value":[
    {
      "name":"TableInfos","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/TableInfos"
    },{
      "name":"UntypedDataSet","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/UntypedDataSet"
    },{
      "name":"TypedDataSet","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/TypedDataSet"
    },{
      "name":"DataProperties","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/DataProperties"
    },{
      "name":"CategoryGroups","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/CategoryGroups"
    },{
      "name":"Sex","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/Sex"
    },{
      "name":"Age","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/Age"
    },{
      "name":"Periods","url":"https://opendata.cbs.nl/ODataApi/OData/80590ENG/Periods"
    }
  ]
}

Paste URL (For example, for 'TypedDataSet' it's 'https://opendata.cbs.nl/ODataApi/OData/80590ENG/UntypedDataSet'). repeat this process for all url you need and load tables to Power Query for transformations of your data there before creating a model for reporting. 

[kokos88]

Thank you a lot! It works perfectly!

[MohdZaid_ · Accepted solution]

Hey @kokos88 ,

If you cannot load the table using Get Data → Web, it is usually because the website loads the data dynamically (JavaScript), which Power BI cannot read directly from the rendered HTML.

In the case of StatLine – Monthly labour participation and unemployment from Statistics Netherlands, the data is not meant to be scraped from the webpage it is published through an official OData API, which is the correct and professional way to connect Power BI.

Instead of using the Web connector, use the OData Feed connector.

Recommended Method: Use OData Feed

In Power BI Desktop
Home → Get Data → OData Feed

Use the dataset OData URL from StatLine
(Each dataset has its own OData endpoint — you can find it on the dataset page under “Data API” or “OData link”.)

The URL format is usually like:

https://opendata.cbs.nl/ODataApi/odata/{DatasetCode}

For example (replace with the actual dataset code shown on the page):

https://opendata.cbs.nl/ODataApi/odata/85264ENG

After connecting:

Select the table called TypedDataSet

Load it into Power BI

Expand the columns as needed

If You Only Have the Dataset Code

You can also directly connect using Power Query (Advanced Editor):

let
    Source = OData.Feed("https://opendata.cbs.nl/ODataApi/odata/85264ENG"),
    TypedData = Source{[Name="TypedDataSet"]}[Data]
in
    TypedData

Why Web Connector Does Not Work

• The table is rendered dynamically
• Power BI Web connector only reads static HTML
• The actual data sits behind an API endpoint

If this explanation helped, please mark it as the solution so others can find it easily.

If it helped, a quick Kudos is always appreciated it highlights useful answers for the community.

Thanks for being part of the discussion!

[kokos88]

Thank you a lot!!