topic Re: DAX to read text and add to another table in DAX Commands and Tips

DAX to read text and add to another table

NVNR_01Reddy — Mon, 26 Feb 2024 16:57:05 GMT

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 >

Re: DAX to read text and add to another table

lbendlin — Tue, 27 Feb 2024 01:03:49 GMT

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.