DAX to read text and add to another table

[NVNR_01Reddy]

Hi folks,

I have a table where a data is available and the text is basically in one column as shown below 

In the screenshot there are 8 rows in table ("Inputdata") and in column Z the below text is availalable per row

ROW 1 data -> "[{""questionId"":""r9cbd0bd3150d487e928d96309fe028b7"",""response"":""No""},{""questionId"":""r400d164cd32348a8aa9d01c1700597c0"",""response"":""Yes""}]"
ROW 2 data -> "[{""questionId"":""r5d4f85ce74844463808afc2131214600 "",""response"":""No""}]"

Now, here is the problem to solve

I need to take one row and breakdown the text and insert into rows into another table "DataSet"

So the final table will have 3 columns

QuestionID, Response, Inserteddate

row level data is 

r9cbd0bd3150d487e928d96309fe028b7 ; NO ; datetime()

r400d164cd32348a8aa9d01c1700597c0 ; YES ; datetime()

r5d4f85ce74844463808afc2131214600 ; ABC ; datetime()

Any hits on how address this beast >

[lbendlin]

No need to torture DAX for that. Power Query is much better at parsing JSON.

let
    Source = #table({"Row"},{{"[{""questionId"":""r9cbd0bd3150d487e928d96309fe028b7"",""response"":""No""},{""questionId"":""r400d164cd32348a8aa9d01c1700597c0"",""response"":""Yes""}]"},{"[{""questionId"":""r5d4f85ce74844463808afc2131214600 "",""response"":""No""}]"}}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Json.Document([Row])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"questionId", "response"}, {"questionId", "response"})
in
    #"Expanded Custom1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.