Get correct data structure from an API url

[rouzbehrasai]

Hi all.

I am trying to get data from this API url as a JSON file but I am not able to transform the data to get the correct structure. Can you please help?

Here is the url:
https://statistikkportalen.udir.no/api/rapportering/rest/v1/Statistikk/VGO/GjennomfoeringFY/2/5/data...

[v-vpabbu]

Hi @rouzbehrasai,

Thanks @lbendlin for Addressing the issue.

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[rouzbehrasai]

Amazing. Thank you so much for your reply.

See the attached screenshot. The table in the end should look like this. And you are almost there. 

The "name" column should be split into two separate columns: "Completion" and "Education program":

1. "Completion" should contain the following categories:

   • "Oppnådd **bleep**-/svennebrev"
   • "Fortsatt i lære"
   • "Fullført læretid, uten **bleep**brev"
   • "Ute av lære uten å ha fullført"

2. "Education program" should contain:

   • "Elektro og datateknologi"
   • "Teknologi- og industrifag"

Additionally, there is a hierarchical structure in the dataset, meaning you should also include a column called "Field of study", which will have values such as:

• "ELMEL4 - Maritim elektrikerfaget vg3 i bedrift"
• "TPMOM3 - Motormannfaget"
• "TPMTS3 - Matrosfaget"

The final table should match the attached example.

URL Handling and Query Update

When new data becomes available, the Aarskull_KalenderAarID parameter (e.g., 2018_2019_2020_2021) will be updated to include the next year (e.g., adding 2022). The URL and query should dynamically update to reflect this new year.

Furthermore, as the education authorities track student completion over multiple years, new data points will become available:

• When 2022 is added, we will also get completion data for 2019 students who finished "Etter 5 år" (after 5 years).
• Similarly, 2020 will now have "Etter 4 år" available, 2021 will have "Etter 3 år," and so on.

The query logic should automatically account for these additional year-based categories when new data is introduced.

I do completely agree with you, this data is very difficult to work with. I would also appreciate if you can show me how you ended up with the result above and how you work further on this. Because there are several other tables that I am working with and for this table we are working with I will also add which area these students go to school which will make the table even more difficult to work with. 

Appreciate your help!

RR

[lbendlin]

You can examine the Power Query code I cobbled together. The most heavy lift is the unpacking of the dimension attributes across all columns. The code is already dynamic and can handle any number of rows and columns.

[rouzbehrasai]

Can I please ask how I can add an automatic update of the URL so that I dont need to update it every year new data is available? 

[lbendlin]

Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1

[lbendlin]

That's a rather convoluted format.  Any idea what your expected output would look like?  What's the meaning of

?  Data rows seem to be structured in these groups of three?

Here's a first stab, but as I mentioned it is not clear how these columns should be mapped.

let
    Source = Json.Document(Web.Contents("https://statistikkportalen.udir.no/api/rapportering/rest/v1/Statistikk/VGO/GjennomfoeringFY/2/5/data?radSti=F&filter=AarEtterID(1_2_3_4)_Aarskull_KalenderAarID(2018_2019_2020_2021)_FylkeID(-10)_KontraktsTypeID(1)_ProgramomraadeID(-30_-24_1487_1489_9332)_UngdomsrettID(-10)_VisAntallGjennomfoeringsstatus(1)_VisAntallPersoner(1)&dataChanged=2025-02-07_091600")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded metadata" = Table.ExpandRecordColumn(#"Converted to Table", "metadata", {"rowHierarchy", "columns"}, {"metadata.rowHierarchy", "metadata.columns"}),
    rows = #"Expanded metadata"{0}[rows],
    #"Converted to Table1" = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"navn", "id", "data"}, {"navn", "id", "data"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Column1", {"data", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "data", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"data.1", "data.2", "data.3", "data.4", "data.5", "data.6", "data.7", "data.8", "data.9", "data.10", "data.11", "data.12", "data.13", "data.14", "data.15", "data.16", "data.17", "data.18", "data.19", "data.20", "data.21", "data.22", "data.23", "data.24", "data.25", "data.26", "data.27", "data.28", "data.29", "data.30"})
in
    #"Split Column by Delimiter"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

Use anonymous login. Later you will need to use Web.Contents correctly, with RelativePath and Query.

EDIT: I think I got it now - working on the mapping 

[lbendlin · Accepted solution]

Here is the result, minus the row hierarchy - I leave that up to you.  This is a very, very heavy lift.  I hope they have a better format available.