Shaping / Transforming JSON Data

Question

I have a public API that's freely availabe to use by anyone (no auth required).&nbsp; The Request URL I have is:https://myhospitalsapi.aihw.gov.au/api/v1/measure-downloads/measure-download-codesThis returns JSON (see&nbsp;JSON Result&nbsp;below for full result).I'm having trouble Transforming this to a Table of data.&nbsp;My hope is to have a single table that has 3 columns:- 'datasheet_code', 'datasheet_description', 'datasheet_type'.I'm not sure how to cater for the result json format that has an extra layer sitting above these fields e.g.&nbsp;&nbsp; "myh-adm": [&nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_code":&nbsp;"myh-adm",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_description":&nbsp;"A datasheet covering the number of admisions to hospitals.",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_type":&nbsp;"MEASURE-DATA"&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; ],&nbsp;JSON Result{&nbsp;&nbsp;"result": {&nbsp; &nbsp;&nbsp;"myh-adm": [&nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_code":&nbsp;"myh-adm",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_description":&nbsp;"A datasheet covering the number of admisions to hospitals.",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_type":&nbsp;"MEASURE-DATA"&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; ],&nbsp; &nbsp;&nbsp;"myh-los": [&nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_code":&nbsp;"myh-los",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_description":&nbsp;"A datasheet covering the average length of stay of overnight hospital stays and bed days.",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_type":&nbsp;"MEASURE-DATA"&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; ],&nbsp; &nbsp;&nbsp;"myh-cancer": [&nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_code":&nbsp;"myh-cancer",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_description":&nbsp;"A datasheet covering cancer surgery waiting times.",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_type":&nbsp;"MEASURE-DATA"&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; ],&nbsp; &nbsp;&nbsp;"myh-cws": [&nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_code":&nbsp;"myh-cws",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_description":&nbsp;"A datasheet covering financial performance of hospitals based on Cost per NWAU.",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_type":&nbsp;"MEASURE-DATA"&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; ],&nbsp; &nbsp;&nbsp;"myh-ed": [&nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_code":&nbsp;"myh-ed",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_description":&nbsp;"A datasheet covering emergency department waiting times and the time in emergency departments.",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_type":&nbsp;"MEASURE-DATA"&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; ],&nbsp; &nbsp;&nbsp;"myh-es": [&nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_code":&nbsp;"myh-es",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_description":&nbsp;"A datasheet covering elective surgery waiting times.",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_type":&nbsp;"MEASURE-DATA"&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; ],&nbsp; &nbsp;&nbsp;"myh-hh": [&nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_code":&nbsp;"myh-hh",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_description":&nbsp;"A datasheet covering hand hygiene rates for public hospitals.",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_type":&nbsp;"MEASURE-DATA"&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; ],&nbsp; &nbsp;&nbsp;"myh-hh-private": [&nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_code":&nbsp;"myh-hh-private",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_description":&nbsp;"A datasheet covering hand hygiene rates for private hospitals.",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_type":&nbsp;"MEASURE-DATA"&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; ],&nbsp; &nbsp;&nbsp;"myh-sab": [&nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_code":&nbsp;"myh-sab",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_description":&nbsp;"A datasheet covering rates and numbers of healthcare-associated Staphylococcus aureus bloodstream infections for public hospitals.",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_type":&nbsp;"MEASURE-DATA"&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; ],&nbsp; &nbsp;&nbsp;"myh-sab-private": [&nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_code":&nbsp;"myh-sab-private",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_description":&nbsp;"A datasheet covering rates and numbers of healthcare-associated Staphylococcus aureus bloodstream infections for private hospitals.",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_type":&nbsp;"MEASURE-DATA"&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; ],&nbsp; &nbsp;&nbsp;"myh-ssi": [&nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_code":&nbsp;"myh-ssi",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_description":&nbsp;"A datasheet covering the available services of reporting units.",&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"datasheet_type":&nbsp;"MEASURE-DATA"&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; ]&nbsp; },&nbsp;&nbsp;"version_information": {&nbsp; &nbsp;&nbsp;"api_version":&nbsp;"1.6.0.0",&nbsp; &nbsp;&nbsp;"data_version":&nbsp;2023101101,&nbsp; &nbsp;&nbsp;"date_uploaded":&nbsp;"2023-10-11T00:00:00",&nbsp; &nbsp;&nbsp;"requested_time_stamp":&nbsp;"2023-10-25T13:46:50.7503006+11:00"&nbsp; }}

wdx223_daniel · Accepted Answer

let
    Source = Web.Contents("https://myhospitalsapi.aihw.gov.au/api/v1/measure-downloads/measure-download-codes"),
    Custom1 = Table.FromRecords(List.Combine(Record.ToList(Json.Document(Source)[result])))
in
    Custom1

&nbsp;

Forum Discussion

Shaping / Transforming JSON Data

2 Replies

Related content

Table Transformation

Transform columns / type

Shape map

Creating custom shape map json file

Data transform using power query