Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

DAX to read text and add to another table

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 >


Super User
Super User

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


    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"})
    #"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.

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.