cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Socrates
Frequent Visitor

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 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

 

1 ACCEPTED SOLUTION
Socrates
Frequent Visitor

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"

 

 

View solution in original post

4 REPLIES 4
Socrates
Frequent Visitor

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
Frequent Visitor

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:

 

Step1.png

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

 


Step2.png

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

Step3.png

 

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

 

Step4.png

 

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"
  }
}

 

 

Hi @Socrates ,

 

I have created the following example data. 

 

vkkfmsft_0-1657093750070.png

 

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

 

vkkfmsft_1-1657093819301.png

 

Best Regards,
Winniz

 

v-kkf-msft
Community Support
Community Support

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors