Display JSON array as column in Power Query

[wseef]

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!

[edhans · Accepted solution]

@wseef - you want to extract to values. See this code:

let
    Source = #table(1,{{{1..3}}}),
    #"Extracted Values" = Table.TransformColumns(Source, {"Column1", each Text.Combine(List.Transform(_, Text.From), ":"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"})
in
    #"Split Column by Delimiter"

That has a list of 1, 2, and 3.

Press the Extract to Values and pick a delimiter. I picked the colon.

You get this:

Then simply select that column and on the home ribbon of Power Query, Split Columns, and set it as follows:

You get this:

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.