Eurostat ds-045409 rest API how to interpret the VALUES

[lakmoor]

Hello,

I followed the below blog and was able to connect to ds-045409 via rest API in Power BI desktop. I was able to create the dimensions. I am stuck at interpreting the values, any help is appreciated.

https://eriksvensen.wordpress.com/2019/01/09/guide-how-to-import-data-from-eurostat-directly-into-po...

Source = Json.Document(Web.Contents("https://ec.europa.eu/eurostat/api/comext/dissemination/statistics/1.0/data/ds-045409?format=JSON&fre..."))

The blog talks abt just one Geo dimension and time dimension, but I am looking at multiple dimensions;

REPORTER,PARTNER,PRODUCT FLOW, STAT_PROCEDURE, PERIOD, and measures VALUE_IN_EUR QUANTITY_IN_KG

For eg, below data was downloaded from Eurostat website , so looking to map it in power BI at this lowest level

Appreciate all the help.

Thank you

[v-nuoc-msft]

Hi @lakmoor 

For your question, I assume you want to get the data from the api in power bi and if the data has a hierarchical structure, filter multiple matches starting from the bottom. Here's how to help you with the process:

Import data from the API into Power BI

Get data from API:

Open Power BI Desktop.

Click Get Data and select Web as the data source.

Enter the API endpoint URL. If the API requires authentication, you will be prompted to provide the necessary credentials or API key.

Download Data:

Once the data is available, you can choose to load the data directly or use the Power Query editor to transform the data, if needed.

Convert data (optional):

If hierarchical or nested data is returned from the API, use the Power Query editor to expand columns or create the hierarchies required for analysis.

Bottom-up filtering of hierarchical data

Once you have hierarchical data in Power BI, you can filter from the bottom up by following these steps:

Create a hierarchy:

In Power BI Desktop, create a hierarchy in your data model by dragging fields toward each other in the Fields pane to create a hierarchy.

Use layered slicers:

Add a slicer to the report and set it to use the hierarchy you created.

This allows users to drill down into the hierarchy and select the lowest level item, effectively filtering from bottom to top.

DAX indicator (advanced):

If more complex filtering is required, consider creating DAX measures that reference the underlying hierarchy to filter the data accordingly.

If you are still having problems, please provide more details and the results you require. It's best to provide the pbix file and be careful to remove sensitive data.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.