Display JSON array as column in Power Query
I'm new to power query. I'm parsing JSON. I have an array name as "categories" when I expand it using Power Query it creates three rows for each category while I just want to remain in one row and want to create 3 separate column for each category like category1,category2,category3. here is my code: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"no", type text}, {"complete", Int64.Type}, {"json", type text}}), #"Parsed JSON" = Table.TransformColumns(#"Changed Type",{{"json", Json.Document}}), #"Expanded json" = Table.ExpandRecordColumn(#"Parsed JSON", "json", {"title", "price", "StoreName", "ratings", "merchant", "categories", "VariantB", "detailA", "detailB", "bullets", "images", "description"}, {"title", "price", "StoreName", "ratings", "merchant", "categories", "VariantB", "detailA", "detailB", "bullets", "images", "description"}) in #"Expanded json" Here is my JSON; { "title": "Braun 6-in-1 All-in-one Trimmer 3 MGK3221, Beard Trimmer for Men, Hair Clipper and Face Trimmer with Lifetime Sharp Blades, Ear & Nose Trimmer Head, 5 Attachments, Black/Volt Green, UK Two Pin Plug", "price": "£30.49", "StoreName": "Braun", "ratings": "4.5", "merchant": "Amazon", "categories": [ "Shaving & Hair Removal", "Trimmers, Clippers & Body Groomers", "Hair Clippers" ], "VariantB": { "MGK 3221": "B0842P7BRK", "MGK 3245": "B0842PDGRK" }, "detailA": { "Product Name": "Braun 6-in-1 Trimmer MGK3221", "Units": "1 count", "Brand": "Braun" }, "detailB": { "ASIN": "B0842P7BRK" }, "bullets": [ "Rechargeable all-in-one trimmer with unprecedented cutting performance vs. previous generations of Braun beard trimmers", "6-in-1 beard trimmer, face and hair trimmer for men's grooming", "Lifetime Sharp Blades and 13 length settings for utmost styling precision", "NiMH battery for 50 minutes of trimming with 10 hour charge", "Designed and engineered in Germany by Braun", "Clip hair to the length you need, simply attach the short & long hair combs, good for cutting hair from 0.5 to 21 mm" ], "images": [ "https://images-na.ssl-images-amazon.com/images/I/813c6SQbWoL._AC_SL1500_.jpg", "https://images-na.ssl-images-amazon.com/images/I/91HnJxkPEML._AC_SL1500_.jpg", "https://images-na.ssl-images-amazon.com/images/I/81fTXUsQRML._AC_SL1500_.jpg", "https://images-na.ssl-images-amazon.com/images/I/91SRO6XOChL._AC_SL1500_.jpg", "https://images-na.ssl-images-amazon.com/images/I/81-h71RecyL._AC_SL1500_.jpg", "https://images-na.ssl-images-amazon.com/images/I/81Xo4qcb4mL._AC_SL1500_.jpg", "https://images-na.ssl-images-amazon.com/images/I/8145FOZPbAL._AC_SL1500_.jpg" ], "description": "<div id=\"productDescription\" class=\"a-section a-spacing-small\"><div class=\"disclaim\">Style Name:<strong>MGK 3221</strong></div><!-- show up to 2 reviews by default --><h3>Product Description</h3><p>Master your style with the rechargeable MGK3221, 6-in-1 beard trimmer for men. With Lifetime Sharp Blades and four combs, it offers 13 lengths (0.5-21 mm), so you can easily achieve the look you want across your beard, face and hair.</p><h3>Box Contains</h3><p></p><ul><li>1 x Beard and hair trimmer</li> <li>1 x Ear and Nose trimmer attachment</li> <li>4 x comb attachments</li> <li>1 x Smart plug charger</li></ul></div>" } I want to show each object and array as a seperate column in one row. Thanks in advance for any kind of help!
