Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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"
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"
Any advise?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |