JSON Record/List problem

[Socrates]

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 apply those answers to what I need.

Going back right to the start I can get my JSON body into a list and then convert that to a table with my ~25 records.

However, each of those Records has a List inside of it - each List then contains a further 280 Records. Inside of each of those Records is a question and answer pair. (Note: I can amened my API call to not return all 280 questions as some are superfluous, but it will still be at least 40 questions)

If I expand everything out I'm left with two columns - questions and answers - but I can't seem to figure out how to split those columns so each row is a single response.

Is it best to do something at this point (say, increment an Index column every 280 rows and go from there?) or can I tease out the question/answer values into columns without losing the record rows?

Most of the other help topics don't seem to have this second nested set of records in the list, which is throwing me from applying other ideas.

Thanks guys

[Socrates · Accepted solution]

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]

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]

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

[v-kkf-msft]

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