Forum Discussion

Socrates's avatar
Socrates
Frequent Visitor
4 years ago
Solved

JSON Record/List problem

Hello   I'm new to working with JSON and I can't quite get my head around my problem. Lots of the other questions in the help forum don't quite echo the structure of my output, so it's hard to appl...
Need Help
  • Socrates's avatar
    Socrates
    4 years ago

    Hi Winniz

     

    Thanks for replying. Putting the index in helped a lot but it wasn't quite where I wanted it. Happily though, that helped with my line of questioning and I found the below thread that helped me pivot out the data once I had fully expanded it:


    https://community.powerbi.com/t5/Desktop/Transpose-one-column-except-first/td-p/1216185

     

    Ashish_Mathur's code did the trick:

     

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    Partition = Table.Group(#"Changed Type", {"Column1"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Column2", "Index"}, {"Column2", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN")[Index]), "Index", "Column2")
in
    #"Pivoted Column"

     

     

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi Socrates ,

 

Not really sure about your data structure, could you please share the sample JSON file and the expected output? If it contains sensitive data, you can also share detailed step-by-step screenshots without sensitive data.

 

Best Regards,
Winniz