Can I achieve this with JSON

[DanielV91]

I have the following JSON but with more products

{
  "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"
    }
  ]
}

I am interested into generating a table like this

product	progress	components	id
Tank Toy	done	plastic,metal,polyurethane	100
Car Toy	in progress	polycarbonate,aluminium	23
Doll Toy	done	polystyrene	40

As you can see the only thing that is bugging me is how can I join all the components name into a single cell. I am only interested in the names in the components array. It is bugging me that a product can have multiple components. I couldn't make it work and I want to know if it is possible. 

If somebody has an idea I would gladly appreciate

[DanielV91 · Accepted solution]

Any advise?

[Eric_Zhang · Accepted solution]

@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"

[Eric_Zhang · Accepted solution]

@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"

[DanielV91 · Accepted solution]

Thank you very much for helping @Eric_Zhang Sorry I couldn't reply faster.

[DanielV91 · Accepted solution]

Any advise?