Solved: Re: Power Query Nested JSON (Record-> Record List ...

Syndicate_Admin · ‎10-25-2021

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	submitted_on	spent_time
[List]: {"QuestionId": "16306365464751", "Question": "How likely are you to recommend your team switching experience to a colleague?","Answer": "10", "type": "range"}, {"QuestionId": "16306366720030","Question": "How easy was it to make the change of your computer?","Answer": "3","type": "range"}, {"QuestionId": "16306365464752","Question": "How do you rate your overall experience?" ","Answer": 3,"type": "webform_rating"}, {"QuestionId": "16312899762243","Question": "Why do you give us this rating?","Answer": "Why is it a bit late","type": "textfield"}, {"QuestionId": "16327629594800","Question": "RollOut type (HP/MAC/OTHER)","Answer": "MAC","type": "hidden"}	2021-10-04T16:19:36.570Z	28
[List]: {"QuestionId": "16306365464751","Question": "How likely are you to recommend your team switching experience to a colleague?", "Answer": "10","type": "range"}, {"QuestionId": "16306366720030","Question": "How easy was it to make the change of your computer?","Answer": "5","type": "range"}, {"QuestionId": "16306365464752","Question": "How do you rate your overall experience?" ","Answer": 5,"type": "webform_rating"}, {"QuestionId": "16312899762243","Question": "Why do you give us this rating?","Answer": "Very fast and friendly","type": "textfield"}, {"QuestionId": "16327629594800","Question": "RollOut type (HP/MAC/OTHER)","Answer": "MAC","type": "hidden"}	2021-10-04T16:48:54.886Z	75
[List]: {"QuestionId": "16306365464751","Question": "How likely are you to recommend your team switching experience to a colleague?","Answer": "10", "type": "range"}, {"QuestionId": "16306366720030","Question": "How easy was it to make the change of your computer?","Answer": "5","type": "range"}, {"QuestionId": "16306365464752","Question": "How do you rate your overall experience?" ","Answer": 5,"type": "webform_rating"}, {"QuestionId": "16312899762243","Question": "Why do you give us this rating?","Answer": "Because it was easy and fast","type": "textfield"}, {"QuestionId": "16327629594800","Question": "RollOut type (HP/MAC/OTHER)","Answer": "MAC","type": "hidden"}	2021-10-04T16:55:04.245Z	40

What I want as a result is the following:

How likely are you to recommend your team switching experience to a colleague?	How easy was it to make the change of your team?	How do you rate your overall experience?	Why do you give us this rating?	RollOut Type (HP/MAC/OTHER)	Submitted On	Spent time
10	3	Just OK	Why it's a bit time-consuming	MAC	2021-10-04T16:19:36.570Z	28
10	5	Very Satisfied	Very fast and friendly	MAC	2021-10-04T16:48:54.886Z	75
10	5	Very Satisfied	Because it was easy and fast	MAC	2021-10-04T16:55:04.245Z	40

When you transform your data in Power BI, you get a record for each question.

See the example below:

question_answers. Question	question_answers. Answer	submitted_on	spent_time
How likely are you to recommend your team switching experience to a colleague?	10	2021-10-04T16:19:36.570Z	28
How easy was it to make the change of your team?	3	2021-10-04T16:19:36.570Z	28
How do you rate your overall experience?	3	2021-10-04T16:19:36.570Z	28
Why do you give us this rating?	Why it's a bit time-consuming	2021-10-04T16:19:36.570Z	28
RollOut Type (HP/MAC/OTHER)	MAC	2021-10-04T16:19:36.570Z	28
How likely are you to recommend your team switching experience to a colleague?	10	2021-10-04T16:48:54.886Z	75
How easy was it to make the change of your team?	5	2021-10-04T16:48:54.886Z	75
How do you rate your overall experience?	5	2021-10-04T16:48:54.886Z	75
Why do you give us this rating?	Very fast and friendly	2021-10-04T16:48:54.886Z	75
RollOut Type (HP/MAC/OTHER)	MAC	2021-10-04T16:48:54.886Z	75
How likely are you to recommend your team switching experience to a colleague?	10	2021-10-04T16:55:04.245Z	40
How easy was it to make the change of your team?	5	2021-10-04T16:55:04.245Z	40
How do you rate your overall experience?	5	2021-10-04T16:55:04.245Z	40
Why do you give us this rating?	Because it was easy and fast	2021-10-04T16:55:04.245Z	40
RollOut Type (HP/MAC/OTHER)	MAC	2021-10-04T16:55:04.245Z	40

Can someone guide me on what I'm doing wrong and provide a Power Query that can result in the table I want?

I have tried the following queries without luck:

let
    Origen = Json.Document(File.Contents("C:\Users\a0m0plu\Downloads\response_1633623033932.json")),
    #"Convertida en tabla" = Table.FromList(Origen, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Se expandió Column1" = Table.ExpandRecordColumn(#"Convertida en tabla", "Column1", {"question_answers", "submitted_on", "spent_time"}, {"question_answers", "submitted_on", "spent_time"}),
    question_answers = #"Se expandió Column1"{0}[question_answers],
    #"Convertida en tabla1" = Table.FromList(question_answers, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Se expandió Column2" = Table.ExpandRecordColumn(#"Convertida en tabla1", "Column1", {"Question", "Answer"}, {"Column1.Question", "Column1.Answer"}),
    #"Tabla transpuesta" = Table.Transpose(#"Se expandió Column2"),
    #"Encabezados promovidos" = Table.PromoteHeaders(#"Tabla transpuesta", [PromoteAllScalars=true]),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"¿Qué tan probable es que recomiendes tu experiencia de cambio de equipo a un colega?", Int64.Type}, {"¿Qué tan fácil fue realizar el cambio de tu equipo?", Int64.Type}, {"¿Cómo calificas tu experiencia en general? ", Int64.Type}, {"¿Por qué nos das esta calificación?", type text}, {"Tipo de RollOut (HP/MAC/OTRO)", type text}})
in
    #"Tipo cambiado"

v-yalanwu-msft · ‎10-27-2021

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.

How to get your questions answered quickly -- How to provide sample data

View solution in original post

v-yalanwu-msft · ‎10-27-2021

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.

How to get your questions answered quickly -- How to provide sample data

Syndicate_Admin · ‎10-25-2021

Thanks for the reply:

Actually this is the JSON when validating it on the page I do not see any error:

[
  {
    "question_answers": [
      {
        "QuestionId": "16306365464751",
        "Question": "¿Qué tan probable es que recomiendes tu experiencia de cambio de equipo a un colega?",
        "Answer": "10",
        "type": "range"
      },
      {
        "QuestionId": "16306366720030",
        "Question": "¿Qué tan fácil fue realizar el cambio de tu equipo?",
        "Answer": "5",
        "type": "range"
      },
      {
        "QuestionId": "16306365464752",
        "Question": "¿Cómo calificas tu experiencia en general? ",
        "Answer": 5,
        "type": "webform_rating"
      },
      {
        "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"
      },
      {
        "QuestionId": "16327629594800",
        "Question": "Tipo de RollOut (HP/MAC/OTRO)",
        "Answer": "HP",
        "type": "hidden"
      }
    ],
    "submitted_on": "2021-10-12T15:33:06.479Z",
    "submitted_by": "0",
    "spent_time": 49
  },
  {
    "question_answers": [
      {
        "QuestionId": "16306365464751",
        "Question": "¿Qué tan probable es que recomiendes tu experiencia de cambio de equipo a un colega?",
        "Answer": "10",
        "type": "range"
      },
      {
        "QuestionId": "16306366720030",
        "Question": "¿Qué tan fácil fue realizar el cambio de tu equipo?",
        "Answer": "5",
        "type": "range"
      },
      {
        "QuestionId": "16306365464752",
        "Question": "¿Cómo calificas tu experiencia en general? ",
        "Answer": 5,
        "type": "webform_rating"
      },
      {
        "QuestionId": "16312899762243",
        "Question": "¿Por qué nos das esta calificación?",
        "Answer": "La anticipación y los webinars para respaldo fueron útiles ",
        "type": "textfield"
      },
      {
        "QuestionId": "16327629594800",
        "Question": "Tipo de RollOut (HP/MAC/OTRO)",
        "Answer": "HP",
        "type": "hidden"
      }
    ],
    "submitted_on": "2021-10-12T15:33:06.777Z",
    "submitted_by": "0",
    "spent_time": 95
  },
  {
    "question_answers": [
      {
        "QuestionId": "16306365464751",
        "Question": "¿Qué tan probable es que recomiendes tu experiencia de cambio de equipo a un colega?",
        "Answer": "10",
        "type": "range"
      },
      {
        "QuestionId": "16306366720030",
        "Question": "¿Qué tan fácil fue realizar el cambio de tu equipo?",
        "Answer": "5",
        "type": "range"
      },
      {
        "QuestionId": "16306365464752",
        "Question": "¿Cómo calificas tu experiencia en general? ",
        "Answer": 5,
        "type": "webform_rating"
      },
      {
        "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"
      },
      {
        "QuestionId": "16327629594800",
        "Question": "Tipo de RollOut (HP/MAC/OTRO)",
        "Answer": "HP",
        "type": "hidden"
      }
    ],
    "submitted_on": "2021-10-12T16:13:38.086Z",
    "submitted_by": "0",
    "spent_time": 64
  }
]

mattww · ‎10-26-2021

Ah great, yeah that JSON looks fine, I have tried following steps 3 and 4 from my original post and that works to parse the JSON out. See sample below

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZZNa9tAEIb/yuBTC/nQtyxfQsglhRY7IafaxoyktbOw2lVWKxK39Mfk2ENOufVSqP5YR1JsnDR2iKE3XcRKM7v7vjPDg8bj3ngiAb7XD4BJ76ZkheFKzlAWt0wXk94Axm1wndVmXjxlfkrrnEnPDlwrcAPfC7zQtye9g9eS29Q/vy/K6icYlJBrFWMsGLAC6G7QLFEZZzKld1MCu8uZpteEI6QMEsxiruoVuyl5rgChlJAowRZ48uLK08bAkzbrRdAsc9aGNMoFLVfBHwfvMRuEjmW5Lw/fanZe3SdcwLzxiYJ/Qw1MbLiqHTfGdpnx/4+XtnHOG17OqsdMkWLB5zzBf3rEJCyYZBrFCWy14G+Rf8viudLZTKPhcrGPD9vpR1EYOI7nvuFjpDTNG/VFqgJSMkJhXPtKePUod/XgMwKZLpTEZmozBrgsUyqMks87mvHVqNZdz8olYNbM+3IjbxXS1X3O6ZStDTbszsw5E+k+xXGoMJEfeX1r98Be1WpJ+qUSYlga+HA+Ov5yenY8vLocftxRk/PRVt3XPE2Z3BDdLqYHK+wUZZxxY1g6W6lwLMc+tK1D27my/YHrDqzgyAujr+s7NjfFy3aTtRnNmTQzw7NGgxfV39tidbDrYNfB7l2wQ2l4wvM2l9gl6CBywCXqAnLUSD0uchRpAzNNFKx+GS5orjuWvc6yMAz3ZVnkdyzrWNaxbB+WrbdT+0grk5xotfkPVz1CgXH1gBBTaQh0TfCaTsYOZc9RFgxsd+D2j6x+sC/KAq9B2UROe9PpXw==", BinaryEncoding.Base64), Compression.Deflate)), 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])),
#"Expanded JSON Document" = Table.ExpandListColumn(#"Added Custom", "JSON Document"),
#"Expanded JSON Document1" = Table.ExpandRecordColumn(#"Expanded JSON Document", "JSON Document", {"question_answers", "submitted_on", "submitted_by", "spent_time"}, {"question_answers", "submitted_on", "submitted_by", "spent_time"}),
#"Expanded question_answers" = Table.ExpandListColumn(#"Expanded JSON Document1", "question_answers"),
#"Expanded question_answers1" = Table.ExpandRecordColumn(#"Expanded question_answers", "question_answers", {"QuestionId", "Question", "Answer", "type"}, {"QuestionId", "Question", "Answer", "type"})
in
#"Expanded question_answers1"

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

Syndicate_Admin · ‎10-26-2021

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

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 · ‎10-27-2021

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 · ‎10-27-2021

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

The values of question_answers. Question must be columns not records

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

mattww · ‎10-28-2021

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

mattww · ‎10-25-2021

Hi @Syndicate_Admin , one thing that might be causing you an issue is that you have an extra double quote (") in the JSON example you gave which is causing the JSON to appear invalid. It's after "How do you rate your overall experience?". Use something like JSON Formatter which would highlight where the glitch is

https://jsonformatter.curiousconcept.com/

Once that's resolved, you should be able to process that JSON column in Power Query using the following steps

Use Text.AfterDelimiter followed by clean/trim to get rid of the [List]: prefix
Add opening and closing square brackets to that, so that your JSON is converted into a JSON Array
Use Json.Document to interpret the text as JSON
Expand to new rows then expand into columns

I have copied the Power Query M below to show what I mean, of course my data source is just manually entered data from your original post

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZNfa9swFMW/ykVPG6SpLEv+9zLKGGSw0a0UBovDUO2bWNS2gizXNaPffVK80i3JAntuXoS5ujr3pyOf5ZIsP6nOrrK8zdufOfnaY2eVbj+WOckgJ0EU0iiMBI94LIKczOClZ+pY6AFqdY/1CNIgjLoHq8FgoZsG29IXDFiUDXSDskWl2g3g4xaNwrZA3yuh0HWNctPjOzchJ1dtN6D5DUB9yc2y4xankpHtxn0+zU4RRzGjNNwdPgKMshthkB0o6wkaee9IKoSi8tqg1xO2u8O2t2gOscJd5T+ZJhfZP5hKvTPPSIvTdP2ARtb1H27tc4R/MQx4t9am+eEUnMmnYAKWpGkcMcbDIzDfqvEZZqMeEPrOeaM6mHQPvfD9amelhDu31u4Ge/ZYfLRrhXV5ioo5olSkPKHHnu3G/SPXvXsuJwpvFl8uP1+9v7y+XXy4eXtA5Lb2ACpVluiUnsiMMMqCi4BeUH4bRFmQZmE0FzH97rcSspqdM3HOxOvOBE8ywedJEvlMxOKciXMmXn0mhMgonzMufCY4JavVLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [questions_answers = _t, submitted_on = _t, spent_time = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"questions_answers", type text}, {"submitted_on", type datetime}, {"spent_time", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "question_answers_clean", each Text.AfterDelimiter([questions_answers],":")),
#"Cleaned Text" = Table.TransformColumns(#"Added Custom",{{"question_answers_clean", Text.Clean, type text}}),
#"Added Custom2" = Table.AddColumn(#"Cleaned Text", "json_object", each "[" & [question_answers_clean] & "]"),
#"Added Custom1" = Table.AddColumn(#"Added Custom2", "Custom", each Json.Document([json_object])),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
#"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"QuestionId", "Question", "Answer", "type"}, {"QuestionId", "Question", "Answer", "type"})
in
#"Expanded Custom1"

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

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

Helpful resources

FabCon Global Hackathon

Power BI Monthly Update - October 2025

FabCon Atlanta 2026

How to Get Your Question Answered Quickly

FabCon is coming to Atlanta

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

Helpful resources

FabCon Global Hackathon

Power BI Monthly Update - October 2025

FabCon Atlanta 2026

How to Get Your Question Answered Quickly