Extract property from JSON child object array into a comma-delimited value in a column

Question

I have a hierarchical JSON array of objects of a fixed depth, and I would like to use PowerQuery to import it as a table into Excel.

Here's a sample that represents how far I've been able to successfully get:

let
    json = "[
    {
      ""t"": {
        ""a"": {
          ""p"": [
            {
              ""id"": ""5z1V"",
              ""name"": ""p-First"",
              ""type"": ""p""
            }
          ],
          ""id"": ""2aXp"",
          ""name"": ""a-un""
        },
        ""p"": [
          {
            ""id"": ""5z1V"",
            ""name"": ""p-First"",
            ""type"": ""p""
          },
          {
            ""id"": ""3BDN"",
            ""name"": ""p-Second"",
            ""type"": ""p""
          }
        ],
        ""id"": ""6Goh"",
        ""name"": ""t-Primary""
      }
    },
    {
      ""t"": {
        ""a"": {
          ""p"": [
            {
              ""href"": ""https://api.example.com/v1/p/1Gxk"",
              ""id"": ""1Gxk"",
              ""name"": ""p-Third"",
              ""type"": ""p"",
              ""uri"": ""p:1Gxk""
            }
          ],
          ""id"": ""3CKV"",
          ""name"": ""a-deux""
        },
        ""p"": [
          {
            ""id"": ""1Gxk"",
            ""name"": ""p-Third"",
            ""type"": ""p""
          }
        ],
        ""id"": ""0lLd"",
        ""name"": ""t-Secondary""
      }
    }
]",
    Source = Json.Document(json),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"t"}, {"t"}),
    #"Expanded t" = Table.ExpandRecordColumn(#"Expanded Column1", "t", {"a", "p", "id", "name"}, {"t.a", "t.p", "t.id", "t.name"}),
    #"Expanded t.a" = Table.ExpandRecordColumn(#"Expanded t", "t.a", {"p", "id", "name"}, {"t.a.p", "t.a.id", "t.a.name"})
in
    #"Expanded t.a"

However, that only gets me a table like this:

`t.a.p`	`t.a.id`	`t.a.name`	`t.p`	`t.id`	`t.name`
`[List]`	`2aXp`	`a-un`	`[List]`	`6Goh`	`t-Primary`
`[List]`	`3CKV`	`a-deux`	`[List]`	`0lLd`	`t-Secondary`

What I would like is to expand those [List] values into the comma-delimited values from the "name" property of the "p" objects (all "p" objects are of the same type, although some "p" objects are children of "a" objects, and some "p" objects are children of "t" objects.

When I try to expand the t.a.p column or the t.p column, I get two choices:

Expand To New Rows: captures all "p" properties and create new rows, which is a problem when there is more than one "p" object. I only want to keep the "name" property of the "p" object.
Expant Values...: Produces an error for each row: "Expression.Error: We cannot convert a value of type Record to type Text.". The offending line in the query is:

= Table.TransformColumns(#"Expanded t.a", {"t.a.p", each Text.Combine(List.Transform(_, Text.From), ","), type text})

The end result should look like this:

`t.a.p`	`t.a.id`	`t.a.name`	`t.p`	`t.id`	`t.name`
`p-First`	`2aXp`	`a-un`	`p-First, p-Second`	`6Goh`	`t-Primary`
`p-Third`	`3CKV`	`a-deux`	`p-Third`	`0lLd`	`t-Secondary`

If it helps, there is always one or more "p" objects inside each "a" object and one or more "p" objects inside each "t" object. "p" objects have the same schema whether they are in "a" or "t" objects.

Alexis Olson Alexis Olson · Accepted Answer · 2020-12-22T15:33:14

You're very close. The problem is that these columns are lists of records and you want to pull out the name record specifically. So instead of Text.From, which doesn't make sense for a record, you can use the function each [name].

= Table.TransformColumns(
      #"Expanded t.a", 
      {"t.p", each Text.Combine(List.Transform(_, each [name]), ", "), type text}
  )

The each syntax may be slightly confusing here, so if you prefer, you can write this using the functional syntax with () =>. I.e.

= Table.TransformColumns(
      #"Expanded t.a", 
      {"t.p", (C) => Text.Combine(List.Transform(C, (R) => R[name]), ", "), type text}
  )

I've chosen C as the variable name for the column and R for the record.

Extract property from JSON child object array into a comma-delimited value in a column

1 Answers