Skip to main content
cancel
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.

Reply
NVNR_01Reddy
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

NVNR_01Reddy_1-1708966595197.png

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 >

 

1 REPLY 1
lbendlin
Super User
Super User

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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