Shaping / Transforming JSON Data

[274188A]

I have a public API that's freely availabe to use by anyone (no auth required).  The Request URL I have is:

https://myhospitalsapi.aihw.gov.au/api/v1/measure-downloads/measure-download-codes

This returns JSON (see JSON Result below for full result).

I'm having trouble Transforming this to a Table of data.

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.

  "myh-adm": [

      {

        "datasheet_code": "myh-adm",

        "datasheet_description": "A datasheet covering the number of admisions to hospitals.",

        "datasheet_type": "MEASURE-DATA"

      }

    ],

JSON Result

{

  "result": {

    "myh-adm": [

      {

        "datasheet_code": "myh-adm",

        "datasheet_description": "A datasheet covering the number of admisions to hospitals.",

        "datasheet_type": "MEASURE-DATA"

      }

    ],

    "myh-los": [

      {

        "datasheet_code": "myh-los",

        "datasheet_description": "A datasheet covering the average length of stay of overnight hospital stays and bed days.",

        "datasheet_type": "MEASURE-DATA"

      }

    ],

    "myh-cancer": [

      {

        "datasheet_code": "myh-cancer",

        "datasheet_description": "A datasheet covering cancer surgery waiting times.",

        "datasheet_type": "MEASURE-DATA"

      }

    ],

    "myh-cws": [

      {

        "datasheet_code": "myh-cws",

        "datasheet_description": "A datasheet covering financial performance of hospitals based on Cost per NWAU.",

        "datasheet_type": "MEASURE-DATA"

      }

    ],

    "myh-ed": [

      {

        "datasheet_code": "myh-ed",

        "datasheet_description": "A datasheet covering emergency department waiting times and the time in emergency departments.",

        "datasheet_type": "MEASURE-DATA"

      }

    ],

    "myh-es": [

      {

        "datasheet_code": "myh-es",

        "datasheet_description": "A datasheet covering elective surgery waiting times.",

        "datasheet_type": "MEASURE-DATA"

      }

    ],

    "myh-hh": [

      {

        "datasheet_code": "myh-hh",

        "datasheet_description": "A datasheet covering hand hygiene rates for public hospitals.",

        "datasheet_type": "MEASURE-DATA"

      }

    ],

    "myh-hh-private": [

      {

        "datasheet_code": "myh-hh-private",

        "datasheet_description": "A datasheet covering hand hygiene rates for private hospitals.",

        "datasheet_type": "MEASURE-DATA"

      }

    ],

    "myh-sab": [

      {

        "datasheet_code": "myh-sab",

        "datasheet_description": "A datasheet covering rates and numbers of healthcare-associated Staphylococcus aureus bloodstream infections for public hospitals.",

        "datasheet_type": "MEASURE-DATA"

      }

    ],

    "myh-sab-private": [

      {

        "datasheet_code": "myh-sab-private",

        "datasheet_description": "A datasheet covering rates and numbers of healthcare-associated Staphylococcus aureus bloodstream infections for private hospitals.",

        "datasheet_type": "MEASURE-DATA"

      }

    ],

    "myh-ssi": [

      {

        "datasheet_code": "myh-ssi",

        "datasheet_description": "A datasheet covering the available services of reporting units.",

        "datasheet_type": "MEASURE-DATA"

      }

    ]

  },

  "version_information": {

    "api_version": "1.6.0.0",

    "data_version": 2023101101,

    "date_uploaded": "2023-10-11T00:00:00",

    "requested_time_stamp": "2023-10-25T13:46:50.7503006+11:00"

  }

}

[274188A]

Life Saver - thanks so much

[wdx223_Daniel · Accepted solution]

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