Can I achieve this with JSON

Any advise?

DanielV91

I'd suggest extract the JSON in below format, as it is more normalized.

let
    Source = Json.Document("{
  ""products"": [
    {
      ""product"": ""Tank Toy"",
      ""progress"": ""done"",
      ""components"": [
        {
          ""self"": ""https://dummy.com/1"",
          ""id"": ""1"",
          ""name"": ""plastic""
        },
        {
          ""self"": ""https://dummy.com/2"",
          ""id"": ""2"",
          ""name"": ""metal""
        },
        {
          ""self"": ""https://dummy.com/5"",
          ""id"": ""5"",
          ""name"": ""polyurethane""
        }
      ],
      ""id"": ""100""
    },
    {
      ""product"": ""Car Toy"",
      ""progress"": ""in progress"",
      ""components"": [
        {
          ""self"": ""https://dummy.com/6"",
          ""id"": ""6"",
          ""name"": ""polycarbonate""
        },
        {
          ""self"": ""https://dummy.com/12"",
          ""id"": ""12"",
          ""name"": ""aluminium""
        }
      ],
      ""id"": ""23""
    },
    {
      ""product"": ""Doll Toy"",
      ""progress"": ""done"",
      ""components"": [
        {
          ""self"": ""https://dummy.com/11"",
          ""id"": ""6"",
          ""name"": ""Polystyrene""
        }
      ],
      ""id"": ""40""
    }
  ]
}"),
    products = Source[products],
    #"Converted to Table" = Table.FromList(products, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"product", "progress", "components", "id"}, {"Column1.product", "Column1.progress", "Column1.components", "Column1.id"}),
    #"Expanded Column1.components" = Table.ExpandListColumn(#"Expanded Column1", "Column1.components"),
    #"Expanded Column1.components1" = Table.ExpandRecordColumn(#"Expanded Column1.components", "Column1.components", {"name"}, {"Column1.components.name"})
in
    #"Expanded Column1.components1"

For the case in your Post.

let
    Source = Json.Document("{
  ""products"": [
    {
      ""product"": ""Tank Toy"",
      ""progress"": ""done"",
      ""components"": [
        {
          ""self"": ""https://dummy.com/1"",
          ""id"": ""1"",
          ""name"": ""plastic""
        },
        {
          ""self"": ""https://dummy.com/2"",
          ""id"": ""2"",
          ""name"": ""metal""
        },
        {
          ""self"": ""https://dummy.com/5"",
          ""id"": ""5"",
          ""name"": ""polyurethane""
        }
      ],
      ""id"": ""100""
    },
    {
      ""product"": ""Car Toy"",
      ""progress"": ""in progress"",
      ""components"": [
        {
          ""self"": ""https://dummy.com/6"",
          ""id"": ""6"",
          ""name"": ""polycarbonate""
        },
        {
          ""self"": ""https://dummy.com/12"",
          ""id"": ""12"",
          ""name"": ""aluminium""
        }
      ],
      ""id"": ""23""
    },
    {
      ""product"": ""Doll Toy"",
      ""progress"": ""done"",
      ""components"": [
        {
          ""self"": ""https://dummy.com/11"",
          ""id"": ""6"",
          ""name"": ""Polystyrene""
        }
      ],
      ""id"": ""40""
    }
  ]
}"),
    products = Source[products],
    #"Converted to Table" = Table.FromList(products, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"product", "progress", "components", "id"}, {"Column1.product", "Column1.progress", "Column1.components", "Column1.id"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Column1", "Custom", each Text.Combine(Table.ToList(Table.SelectColumns(Table.FromList([Column1.components],Record.FieldValues, {"self", "id", "Name"}),"Name")),",")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Combine([Custom],each Text.From(_))),
    #"Column1 components" = #"Added Custom1"{0}[Column1.components]
in
    #"Column1 components"