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"

     

     

Socrates's avatar
Socrates
Frequent Visitor
4 years ago

Hi v-kkf-msft 

 

Guess some screenshots would've been helpful!

 

So, loading the JSON in, expanding out the body leaves me with the Records:

 

If I expand that, the only thing inside each record is a List:

 


As you can see, if I select one, each list contains a Record and Answer pair for each question:

 

If I expand that, I then get all those Records listed (also selected so you can see the Q&A pair):

 

 

I've nixed the JSON below so it only contains a few questions from one response as it'll be quite long otherwise. I've left the header information in though:

 

 

{
  "body": {
    "filter": "(ID.endDate>=2022-06-30) and (ID.endDate<=2022-06-30)",
    "responses": [
      {
        "variables": [
          {
            "value": "2",
            "variableId": "V12"
          },
          {
            "value": "1",
            "variableId": "V13"
          },
          {
            "status": "NR",
            "value": "",
            "variableId": "V697"
          },
          {
            "value": "f1385b7d-50c6-422b-a03c-3353df505263",
            "variableId": "V695"
          },
          {
            "value": "30/06/2022",
            "variableId": "V14"
          },
          {
            "value": "7:54:05",
            "variableId": "V15"
          },
          {
            "value": "30/06/2022",
            "variableId": "V16"
          },
          {
            "value": "8:07:30",
            "variableId": "V17"
          },
          {
            "value": "13.42",
            "variableId": "V18"
          },
          {
            "value": "text",
            "variableId": "V696"
          },
        ]
      }
    ],
    "surveyId": "1ddcd4e7-e417-45a6-b225-8d7e56a29428"
  },
  "headers": {
    "X-RateLimit-Limit-Per-Sec": "1",
    "Transfer-Encoding": "chunked",
    "X-RateLimit-Remaining-Per-Sec": "0",
    "Strict-Transport-Security": "max-age=31536000",
    "X-RateLimit-Remaining-Per-Min": "8",
    "X-RateLimit-Reset-Per-Min": "1656671026",
    "X-XSS-Protection": "1; mode=block",
    "Expires": "-1",
    "X-RateLimit-Reset-Per-Sec": "1656670981",
    "X-RateLimit-Limit-Per-Min": "10",
    "Cache-Control": "no-cache",
    "X-Content-Type-Options": "nosniff",
    "Content-Type": "application/json; charset=utf-8",
    "Pragma": "no-cache",
    "Date": "Fri, 01 Jul 2022 10:23:00 GMT",
    "SERVER": "online1",
    "api-deprecated-versions": "1.0"
  }
}

 

 

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi Socrates ,

     

    I have created the following example data. 

     

     

    So do you expect to keep the row number it belongs to when expanding to the final data? Like this.

     

     

    Best Regards,
    Winniz