Expand List to same row in Power Query

0
votes

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"

enter image description here

1
powerquery

1 Answers

0
votes

Add extra step:

ExtractList = Table.TransformColumns(#"Expanded json", {"categories", each Text.Combine(List.Transform(_, Text.From), ","), type text})