PowerQuery M Language- expand a column of type "List of Records of Lists of Records"

1
votes

I am using Excel Powerquery (M Language) to access the RESTful API of a SNOMED CT server. The JSON returned by the server is a deeply nested structure.

The example below is filtered to show a single item, normally the items array will contain multiple results.

Example-

{
  "items": [
    {
      "id": "258674000",
      "released": true,
      "active": true,
      "effectiveTime": "20020131",
      "moduleId": "900000000000207008",
      "iconId": "362981000",
      "definitionStatus": {
        "id": "900000000000074008"
      },
      "subclassDefinitionStatus": "NON_DISJOINT_SUBCLASSES",
      "fsn": {
        "id": "3508354011",
        "term": "Micrometer (qualifier value)",
        "concept": {
          "id": "258674000"
        },
        "type": {
          "id": "900000000000003001"
        },
        "typeId": "900000000000003001",
        "conceptId": "258674000",
        "acceptability": {
          "900000000000509007": "PREFERRED",
          "900000000000508004": "PREFERRED"
        }
      },
      "pt": {
        "id": "384891018",
        "term": "um",
        "concept": {
          "id": "258674000"
        },
        "type": {
          "id": "900000000000013009"
        },
        "typeId": "900000000000013009",
        "conceptId": "258674000",
        "acceptability": {
          "900000000000509007": "PREFERRED",
          "900000000000508004": "PREFERRED"
        }
      },
      "descriptions": {
        "items": [
          {
            "id": "2609609012",
            "released": true,
            "active": false,
            "effectiveTime": "20170731",
            "moduleId": "900000000000207008",
            "iconId": "900000000000003001",
            "term": "micrometer (qualifier value)",
            "semanticTag": "qualifier value",
            "languageCode": "en",
            "caseSignificance": {
              "id": "900000000000448009"
            },
            "concept": {
              "id": "258674000"
            },
            "type": {
              "id": "900000000000003001"
            },
            "typeId": "900000000000003001",
            "conceptId": "258674000",
            "caseSignificanceId": "900000000000448009",
            "acceptability": {}
          },
          {
            "id": "384891018",
            "released": true,
            "active": true,
            "effectiveTime": "20020131",
            "moduleId": "900000000000207008",
            "iconId": "900000000000013009",
            "term": "um",
            "semanticTag": "",
            "languageCode": "en",
            "caseSignificance": {
              "id": "900000000000017005"
            },
            "concept": {
              "id": "258674000"
            },
            "type": {
              "id": "900000000000013009"
            },
            "typeId": "900000000000013009",
            "conceptId": "258674000",
            "caseSignificanceId": "900000000000017005",
            "acceptability": {
              "900000000000509007": "PREFERRED",
              "900000000000508004": "PREFERRED"
            }
          },
          {
            "id": "650119013",
            "released": true,
            "active": false,
            "effectiveTime": "20060731",
            "moduleId": "900000000000207008",
            "iconId": "900000000000003001",
            "term": "um (qualifier value)",
            "semanticTag": "qualifier value",
            "languageCode": "en",
            "caseSignificance": {
              "id": "900000000000017005"
            },
            "concept": {
              "id": "258674000"
            },
            "type": {
              "id": "900000000000003001"
            },
            "typeId": "900000000000003001",
            "conceptId": "258674000",
            "caseSignificanceId": "900000000000017005",
            "acceptability": {}
          },
          {
            "id": "384888018",
            "released": true,
            "active": false,
            "effectiveTime": "20170731",
            "moduleId": "900000000000207008",
            "iconId": "900000000000013009",
            "term": "micrometer",
            "semanticTag": "",
            "languageCode": "en",
            "caseSignificance": {
              "id": "900000000000448009"
            },
            "concept": {
              "id": "258674000"
            },
            "type": {
              "id": "900000000000013009"
            },
            "typeId": "900000000000013009",
            "conceptId": "258674000",
            "caseSignificanceId": "900000000000448009",
            "acceptability": {}
          },
          {
            "id": "3508354011",
            "released": true,
            "active": true,
            "effectiveTime": "20170731",
            "moduleId": "900000000000207008",
            "iconId": "900000000000003001",
            "term": "Micrometer (qualifier value)",
            "semanticTag": "qualifier value",
            "languageCode": "en",
            "caseSignificance": {
              "id": "900000000000448009"
            },
            "concept": {
              "id": "258674000"
            },
            "type": {
              "id": "900000000000003001"
            },
            "typeId": "900000000000003001",
            "conceptId": "258674000",
            "caseSignificanceId": "900000000000448009",
            "acceptability": {
              "900000000000509007": "PREFERRED",
              "900000000000508004": "PREFERRED"
            }
          },
          {
            "id": "3508411019",
            "released": true,
            "active": true,
            "effectiveTime": "20170731",
            "moduleId": "900000000000207008",
            "iconId": "900000000000013009",
            "term": "Micrometer",
            "semanticTag": "",
            "languageCode": "en",
            "caseSignificance": {
              "id": "900000000000448009"
            },
            "concept": {
              "id": "258674000"
            },
            "type": {
              "id": "900000000000013009"
            },
            "typeId": "900000000000013009",
            "conceptId": "258674000",
            "caseSignificanceId": "900000000000448009",
            "acceptability": {
              "900000000000509007": "ACCEPTABLE"
            }
          },
          {
            "id": "384889014",
            "released": true,
            "active": true,
            "effectiveTime": "20020131",
            "moduleId": "900000000000207008",
            "iconId": "900000000000013009",
            "term": "micrometre",
            "semanticTag": "",
            "languageCode": "en",
            "caseSignificance": {
              "id": "900000000000017005"
            },
            "concept": {
              "id": "258674000"
            },
            "type": {
              "id": "900000000000013009"
            },
            "typeId": "900000000000013009",
            "conceptId": "258674000",
            "caseSignificanceId": "900000000000017005",
            "acceptability": {
              "900000000000508004": "ACCEPTABLE"
            }
          },
          {
            "id": "384890017",
            "released": true,
            "active": true,
            "effectiveTime": "20020131",
            "moduleId": "900000000000207008",
            "iconId": "900000000000013009",
            "term": "micron",
            "semanticTag": "",
            "languageCode": "en",
            "caseSignificance": {
              "id": "900000000000017005"
            },
            "concept": {
              "id": "258674000"
            },
            "type": {
              "id": "900000000000013009"
            },
            "typeId": "900000000000013009",
            "conceptId": "258674000",
            "caseSignificanceId": "900000000000017005",
            "acceptability": {
              "900000000000509007": "ACCEPTABLE",
              "900000000000508004": "ACCEPTABLE"
            }
          }
        ],
        "limit": 8,
        "total": 8
      },
      "ancestorIds": [
        "-1",
        "138875005",
        "258667005",
        "362981000",
        "767524001"
      ],
      "parentIds": [
        "258668000"
      ],
      "statedAncestorIds": [
        "-1",
        "138875005",
        "258667005",
        "362981000",
        "767524001"
      ],
      "statedParentIds": [
        "258668000"
      ],
      "definitionStatusId": "900000000000074008"
    }
  ],
  "searchAfter": "AoE_BTAxMzRlZWNhLTYxODEtNDFjYi1hNmJlLWQzN2IwMGFlYzEyNA==",
  "limit": 50,
  "total": 1
}

The top level object in the JSON represents a List of Records in M language terms. Using a custom function fromServer(endpoint) that queries the server I was able to expand the JSON result using-

let
    concepts = Table.FromRecords(fromServer("API_ENDPOINT")[items]),
in
    concepts

This gives me a table of concepts, one per line. However i'm stuck at the next point.

Each concept has a set of possible synonyms. These are descriptions in SNOMED terms. The descriptions column in the table described above is a column of Records, where each Record has a field items. itemsis a list of Records, and the Records contain the key/value pairs to access. What I want to do is expand the descriptions column, unrolling either selected values by name, or all values from the bottom level Records.

So, that is something like a Table.ExpandTableColumn() where the values to be expanded come from a column of shape-

descriptions (the column name)
  Records
    items: List
      Records
        Keys : Values

I must admit i'm not sure how to get started on this, marching down into a nested structured value in a table field and then accumulating the results. Any pointers would be appreciated.

1
jsonpowerquerym

1 Answers

0
votes

If I understand your question correctly, I think you're after something like this:

let
    json = "{""items"":[{""id"":""258674000"",""released"":true,""active"":true,""effectiveTime"":""20020131"",""moduleId"":""900000000000207008"",""iconId"":""362981000"",""definitionStatus"":{""id"":""900000000000074008""},""subclassDefinitionStatus"":""NON_DISJOINT_SUBCLASSES"",""fsn"":{""id"":""3508354011"",""term"":""Micrometer (qualifier value)"",""concept"":{""id"":""258674000""},""type"":{""id"":""900000000000003001""},""typeId"":""900000000000003001"",""conceptId"":""258674000"",""acceptability"":{""900000000000509007"":""PREFERRED"",""900000000000508004"":""PREFERRED""}},""pt"":{""id"":""384891018"",""term"":""um"",""concept"":{""id"":""258674000""},""type"":{""id"":""900000000000013009""},""typeId"":""900000000000013009"",""conceptId"":""258674000"",""acceptability"":{""900000000000509007"":""PREFERRED"",""900000000000508004"":""PREFERRED""}},""descriptions"":{""items"":[{""id"":""2609609012"",""released"":true,""active"":false,""effectiveTime"":""20170731"",""moduleId"":""900000000000207008"",""iconId"":""900000000000003001"",""term"":""micrometer (qualifier value)"",""semanticTag"":""qualifier value"",""languageCode"":""en"",""caseSignificance"":{""id"":""900000000000448009""},""concept"":{""id"":""258674000""},""type"":{""id"":""900000000000003001""},""typeId"":""900000000000003001"",""conceptId"":""258674000"",""caseSignificanceId"":""900000000000448009"",""acceptability"":{}},{""id"":""384891018"",""released"":true,""active"":true,""effectiveTime"":""20020131"",""moduleId"":""900000000000207008"",""iconId"":""900000000000013009"",""term"":""um"",""semanticTag"":"""",""languageCode"":""en"",""caseSignificance"":{""id"":""900000000000017005""},""concept"":{""id"":""258674000""},""type"":{""id"":""900000000000013009""},""typeId"":""900000000000013009"",""conceptId"":""258674000"",""caseSignificanceId"":""900000000000017005"",""acceptability"":{""900000000000509007"":""PREFERRED"",""900000000000508004"":""PREFERRED""}},{""id"":""650119013"",""released"":true,""active"":false,""effectiveTime"":""20060731"",""moduleId"":""900000000000207008"",""iconId"":""900000000000003001"",""term"":""um (qualifier value)"",""semanticTag"":""qualifier value"",""languageCode"":""en"",""caseSignificance"":{""id"":""900000000000017005""},""concept"":{""id"":""258674000""},""type"":{""id"":""900000000000003001""},""typeId"":""900000000000003001"",""conceptId"":""258674000"",""caseSignificanceId"":""900000000000017005"",""acceptability"":{}},{""id"":""384888018"",""released"":true,""active"":false,""effectiveTime"":""20170731"",""moduleId"":""900000000000207008"",""iconId"":""900000000000013009"",""term"":""micrometer"",""semanticTag"":"""",""languageCode"":""en"",""caseSignificance"":{""id"":""900000000000448009""},""concept"":{""id"":""258674000""},""type"":{""id"":""900000000000013009""},""typeId"":""900000000000013009"",""conceptId"":""258674000"",""caseSignificanceId"":""900000000000448009"",""acceptability"":{}},{""id"":""3508354011"",""released"":true,""active"":true,""effectiveTime"":""20170731"",""moduleId"":""900000000000207008"",""iconId"":""900000000000003001"",""term"":""Micrometer (qualifier value)"",""semanticTag"":""qualifier value"",""languageCode"":""en"",""caseSignificance"":{""id"":""900000000000448009""},""concept"":{""id"":""258674000""},""type"":{""id"":""900000000000003001""},""typeId"":""900000000000003001"",""conceptId"":""258674000"",""caseSignificanceId"":""900000000000448009"",""acceptability"":{""900000000000509007"":""PREFERRED"",""900000000000508004"":""PREFERRED""}},{""id"":""3508411019"",""released"":true,""active"":true,""effectiveTime"":""20170731"",""moduleId"":""900000000000207008"",""iconId"":""900000000000013009"",""term"":""Micrometer"",""semanticTag"":"""",""languageCode"":""en"",""caseSignificance"":{""id"":""900000000000448009""},""concept"":{""id"":""258674000""},""type"":{""id"":""900000000000013009""},""typeId"":""900000000000013009"",""conceptId"":""258674000"",""caseSignificanceId"":""900000000000448009"",""acceptability"":{""900000000000509007"":""ACCEPTABLE""}},{""id"":""384889014"",""released"":true,""active"":true,""effectiveTime"":""20020131"",""moduleId"":""900000000000207008"",""iconId"":""900000000000013009"",""term"":""micrometre"",""semanticTag"":"""",""languageCode"":""en"",""caseSignificance"":{""id"":""900000000000017005""},""concept"":{""id"":""258674000""},""type"":{""id"":""900000000000013009""},""typeId"":""900000000000013009"",""conceptId"":""258674000"",""caseSignificanceId"":""900000000000017005"",""acceptability"":{""900000000000508004"":""ACCEPTABLE""}},{""id"":""384890017"",""released"":true,""active"":true,""effectiveTime"":""20020131"",""moduleId"":""900000000000207008"",""iconId"":""900000000000013009"",""term"":""micron"",""semanticTag"":"""",""languageCode"":""en"",""caseSignificance"":{""id"":""900000000000017005""},""concept"":{""id"":""258674000""},""type"":{""id"":""900000000000013009""},""typeId"":""900000000000013009"",""conceptId"":""258674000"",""caseSignificanceId"":""900000000000017005"",""acceptability"":{""900000000000509007"":""ACCEPTABLE"",""900000000000508004"":""ACCEPTABLE""}}],""limit"":8,""total"":8},""ancestorIds"":[""-1"",""138875005"",""258667005"",""362981000"",""767524001""],""parentIds"":[""258668000""],""statedAncestorIds"":[""-1"",""138875005"",""258667005"",""362981000"",""767524001""],""statedParentIds"":[""258668000""],""definitionStatusId"":""900000000000074008""}],""searchAfter"":""AoE_BTAxMzRlZWNhLTYxODEtNDFjYi1hNmJlLWQzN2IwMGFlYzEyNA=="",""limit"":50,""total"":1}",
    
    parsed = Json.Document(json),
    toTable = Table.FromRecords(parsed[items]),
    flattenedDescriptions = Table.TransformColumns(toTable, {"descriptions", each Table.FromRecords([items]), type table}),

    // If the columns you want to expand are fixed/constant, you can hard code them. Something like this.
    expandHardCoded = Table.ExpandTableColumn(flattenedDescriptions, "descriptions", {"id", "released", "active", "effectiveTime", "moduleId", "iconId", "term", "semanticTag", "languageCode", "caseSignificance", "concept", "type", "typeId", "conceptId", "caseSignificanceId", "acceptability"}, {"id.1", "released.1", "active.1", "effectiveTime.1", "moduleId.1", "iconId.1", "term", "semanticTag", "languageCode", "caseSignificance", "concept", "type", "typeId", "conceptId", "caseSignificanceId", "acceptability"}),
    
    // If the columns you want to expand need to be determined dynamically but every table will have the same column names, then can look at first table and use its column names when expanding.
    expandBasedOnFirstRowOnly = 
        let
            columnsToExpand = Table.ColumnNames(flattenedDescriptions{0}[descriptions]),
            renamed = List.Transform(columnsToExpand, each "descriptions." & _),
            expanded = Table.ExpandTableColumn(flattenedDescriptions, "descriptions", columnsToExpand, renamed)
        in expanded,
    
    // If the columns you want to expand need to be determined dynamically but every table will not have the same column names, then create an exhaustive, unique list. This goes through every row in the table though, so might be a little slower.
    expandBasedOnAllRows =
        let
            columnsToExpand = List.Distinct(List.Combine(List.Transform(flattenedDescriptions[descriptions], Table.ColumnNames))),
            renamed = List.Transform(columnsToExpand, each "descriptions." & _),
            expanded = Table.ExpandTableColumn(flattenedDescriptions, "descriptions", columnsToExpand, renamed)
        in expanded
in
    expandBasedOnAllRows
  • Table.TransformColumns will iterate over each value in the descriptions column and turn each value's items field into a table -- which can then subsequently be expanded.
  • The expandHardCoded, expandBasedOnFirstRowOnly, expandBasedOnAllRows steps are just three different approaches to expand the nested columns. Depending on the nature of the data you're dealing with, (n)one of these approaches may be okay for you.