Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago
Solved

Power Query Nested JSON (Registry-> Record List -> Registry)

Hello I'm dealing with a JSON from a survey app that has the answers to each survey question nested. I have this raw data in the table: questions_answers ...
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Syndicate_Admin ;

    You could change it to excel table like below:

    questions_answers submitted_on spent_time
    {
                "QuestionId":"16306365464751",
                "Question":"¿Qué tan probable es que recomiendes tu experiencia de cambio de equipo a un colega?",
                "Answer":"10",
                "type":"range"
             }    		 2021-10-04T16:19:36.570Z 28
    {
            "QuestionId": "16306366720030",
            "Question": "¿Qué tan fácil fue realizar el cambio de tu equipo?",
            "Answer": "5",
            "type": "range"
          }    		 2021-10-04T16:19:36.570Z 28
     {
            "QuestionId": "16306365464752",
            "Question": "¿Cómo calificas tu experiencia en general? ",
            "Answer": 5,
            "type": "webform_rating"
          }    		 2021-10-04T16:19:36.570Z 28
    {
            "QuestionId": "16312899762243",
            "Question": "¿Por qué nos das esta calificación?",
            "Answer": "La persona que me ayudo con el cambio de mi equipo fue muy amable y el cambio fue muy rápido ",
            "type": "textfield"
          }    		 2021-10-04T16:19:36.570Z 28
    {
            "QuestionId": "16327629594800",
            "Question": "Tipo de RollOut (HP/MAC/OTRO)",
            "Answer": "HP",
            "type": "hidden"
          }    		 2021-10-04T16:19:36.570Z 28
    {
            "QuestionId": "16306365464751",
            "Question": "¿Qué tan probable es que recomiendes tu experiencia de cambio de equipo a un colega?",
            "Answer": "10",
            "type": "range"
          }    		 2021-10-04T16:48:54.886Z 75
    {
            "QuestionId": "16306366720030",
            "Question": "¿Qué tan fácil fue realizar el cambio de tu equipo?",
            "Answer": "5",
            "type": "range"
          }    		 2021-10-04T16:48:54.886Z 75
    {
            "QuestionId": "16306365464752",
            "Question": "¿Cómo calificas tu experiencia en general? ",
            "Answer": 5,
            "type": "webform_rating"
          }    		 2021-10-04T16:48:54.886Z 75
    {
            "QuestionId": "16312899762243",
            "Question": "¿Por qué nos das esta calificación?",
            "Answer": "La anticipación y los webinars para respaldo fueron útiles ",
            "type": "textfield"
          }    		 2021-10-04T16:48:54.886Z 75
    {
            "QuestionId": "16327629594800",
            "Question": "Tipo de RollOut (HP/MAC/OTRO)",
            "Answer": "HP",
            "type": "hidden"
          }    		 2021-10-04T16:48:54.886Z 75
    {
            "QuestionId": "16306365464751",
            "Question": "¿Qué tan probable es que recomiendes tu experiencia de cambio de equipo a un colega?",
            "Answer": "10",
            "type": "range"
          }    		 2021-10-04T16:55:04.245Z 40
    {
            "QuestionId": "16306366720030",
            "Question": "¿Qué tan fácil fue realizar el cambio de tu equipo?",
            "Answer": "5",
            "type": "range"
          }    		 2021-10-04T16:55:04.245Z 40
    {
            "QuestionId": "16306365464752",
            "Question": "¿Cómo calificas tu experiencia en general? ",
            "Answer": 5,
            "type": "webform_rating"
          }    		 2021-10-04T16:55:04.245Z 40
    {
            "QuestionId": "16312899762243",
            "Question": "¿Por qué nos das esta calificación?",
            "Answer": "Por qué el ingeniero que me ayudó sabía bien lo que hacia",
            "type": "textfield"
          }    		 2021-10-04T16:55:04.245Z 40
    {
            "QuestionId": "16327629594800",
            "Question": "Tipo de RollOut (HP/MAC/OTRO)",
            "Answer": "HP",
            "type": "hidden"
          }    		 2021-10-04T16:55:04.245Z 40

    2.Then import to Power BI.

    3.click the questions_answers column then tranform JSON.

    4.Expand it 

    The final output is shown below:

    https://www.youtube.com/watch?v=ipI6mrWLQKA

    https://www.youtube.com/watch?v=6A55DmIHqBY

     

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago

I have tried the solution but there is a friendly problem:

Captura de pantalla 2021-10-26 163524.jpg

The code I used was as follows:

let
Source = Table.FromRows(Json.Document(File.Contents("C:\Users\a0m0plu\Downloads\response_1633623033932.json")), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"JSON Source" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"JSON Source", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "JSON Document", each Json.Document([JSON Source])),
#"Documento JSON expandido" = Table.ExpandListColumn(#"Added Custom", "JSON Document"),
#"Expanded JSON Document1" = Table.ExpandRecordColumn(#"Documento JSON expandido", "JSON Document", {"question_answers", "submitted_on", "submitted_by", "spent_time"}, {"question_answers", "submitted_on", "submitted_by", "spent_time"}),
#"question_answers expandida" = Table.ExpandListColumn(#"Expanded JSON Document1", "question_answers"),
#"Expanded question_answers1" = Table.ExpandRecordColumn(#"question_answers expandida", "question_answers", {"QuestionId", "Question", "Answer", "type"}, {"QuestionId", "Question", "Answer", "type"})
in
#"Expanded question_answers1"

mattww's avatar
mattww
Icon for Responsive Resident rankResponsive Resident
4 years ago

Hi Syndicate_Admin, it looks like you're loading the data directly from a .json file, so you shouldn't need the steps I described.

 

From your original post, it looked like you had a table with normal data that included one column with JSON data. If you are just loading a JSON file, that should be straightforward using Get Data > File > JSON

 

I've taken the JSON sample you provided and put it in a .json and that loads fine for me

 

let
Source = Json.Document(File.Contents("C:\response_1633623033932.json")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"question_answers", "submitted_on", "submitted_by", "spent_time"}, {"question_answers", "submitted_on", "submitted_by", "spent_time"}),
#"Expanded question_answers" = Table.ExpandListColumn(#"Expanded Column1", "question_answers"),
#"Expanded question_answers1" = Table.ExpandRecordColumn(#"Expanded question_answers", "question_answers", {"QuestionId", "Question", "Answer", "type"}, {"question_answers.QuestionId", "question_answers.Question", "question_answers.Answer", "question_answers.type"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded question_answers1",{{"question_answers.QuestionId", Int64.Type}, {"question_answers.Question", type text}, {"question_answers.Answer", type any}, {"question_answers.type", type text}, {"submitted_on", type datetime}, {"submitted_by", Int64.Type}, {"spent_time", Int64.Type}})
in
#"Changed Type"

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    4 years ago

    The problem remains that I can't organize the questions as culmnas:

    hecthor_1-1635372300743.png

    The values of question_answers. Question must be columns not records

    hecthor_2-1635372390717.png

    In the example there are 12 questions but I end up having 60 records.

    • mattww's avatar
      mattww
      Icon for Responsive Resident rankResponsive Resident
      4 years ago

      From that list of 60 records, you could Pivot the question column so those values appear as column headers

       

      If you have a PBIX you are able to share, send it over and I can take a look