Forum Discussion
Parse JSON Column with the Relational Key Value as an Attribute
dufoq3 , lbendlin Below is my updated dataset and expected outcome. Appreciate you're patience and expertise. These are real GUIDs but they are completely fictitious to the system I am working in. The first data table is the output just before my attempts to transform the JSON (e.g., its the starting point from where I need assistance). I've also included the full JSON object string in case you want to load and use in any examples.
Full JSON String
[{"RecordId":"70F7A8E9-B59F-417B-A074-08D745B691B7","EnrollmentId":"4B2651CC-0718-489A-8934-0E810365E256","QuizId":"91C4EBCC-2D7E-46EB-BBEA-B2A0661DE04A","Response":{"EABB39EC-9A43-4271-9B8A-44E132099D5B":{"Percent":0.0,"AnswerResult":{"Answer":"7EEBA0A8-F0C2-4773-BDB4-26F3FC512DCF","QuestionType":1}},"A2A7272C-4B38-4E1E-951B-7512AFAB1712":{"Percent":0.0,"AnswerResult":{"Answer":"C4A0D147-A7A6-4F2C-88A7-A61C78F5A389","QuestionType":1}}}},{"RecordId":"5161BD49-2918-4626-9154-CD3D4A4A4390","EnrollmentId":"BEAAD33B-50C5-42DF-BD1D-C9E9904F3235","QuizId":"67E19DA5-5BE8-4CBC-9951-1CDB8DBF40B8","Response":{"45DC5861-F3ED-4E98-BEDE-1E1B5B8578CA":{"Percent":0.0,"AnswerResult":{"Answer":"E8C99291-6C81-454C-8D0F-ADB73358D516","QuestionType":1}},"FA04612C-6C0E-429E-A97E-FE27E5B2E4B1":{"Percent":0.0,"AnswerResult":{"Answer":"6CB77D11-8BBE-44AB-9D16-C3925719984B","QuestionType":1}}}},{"RecordId":"18AB3B1A-A5CB-499C-816D-F83C493F95B1","EnrollmentId":"94A3B295-4F86-4333-8604-17BA1DFE3884","QuizId":"5DBA2205-1D65-4664-8FC5-8C2A6F70C40D","Response":{"3AA95D56-8B5C-4663-96E5-B99FE2B1D07B":{"Percent":0.0,"AnswerResult":{"Answer":"3B80C204-55B8-40D7-9558-42599F5BDEE9","QuestionType":1}},"8B74A99C-0C68-461E-BF79-3096C058D3F1":{"Percent":0.0,"AnswerResult":{"Answer":"9EB0842B-F793-45B7-B1D7-6A8ACDDF695B","QuestionType":1}}}}]
Existing Data Table
| RecordId | EnrollmentId | QuizId | Response |
| 70F7A8E9-B59F-417B-A074-08D745B691B7 | 4B2651CC-0718-489A-8934-0E810365E256 | 91C4EBCC-2D7E-46EB-BBEA-B2A0661DE04A | {"EABB39EC-9A43-4271-9B8A-44E132099D5B": {"Percent": 0.0,"AnswerResult": {"Answer": "7EEBA0A8-F0C2-4773-BDB4-26F3FC512DCF","QuestionType": 1}},"A2A7272C-4B38-4E1E-951B-7512AFAB1712": {"Percent": 0.0,"AnswerResult": {"Answer": "C4A0D147-A7A6-4F2C-88A7-A61C78F5A389","QuestionType": 1}}} |
| 5161BD49-2918-4626-9154-CD3D4A4A4390 | BEAAD33B-50C5-42DF-BD1D-C9E9904F3235 | 67E19DA5-5BE8-4CBC-9951-1CDB8DBF40B8 | {"45DC5861-F3ED-4E98-BEDE-1E1B5B8578CA": {"Percent": 0.0,"AnswerResult": {"Answer": "E8C99291-6C81-454C-8D0F-ADB73358D516","QuestionType": 1}},"FA04612C-6C0E-429E-A97E-FE27E5B2E4B1": {"Percent": 0.0,"AnswerResult": {"Answer": "6CB77D11-8BBE-44AB-9D16-C3925719984B","QuestionType": 1}}} |
| 18AB3B1A-A5CB-499C-816D-F83C493F95B1 | 94A3B295-4F86-4333-8604-17BA1DFE3884 | 5DBA2205-1D65-4664-8FC5-8C2A6F70C40D | {"3AA95D56-8B5C-4663-96E5-B99FE2B1D07B": {"Percent": 0.0,"AnswerResult": {"Answer": "3B80C204-55B8-40D7-9558-42599F5BDEE9","QuestionType": 1}},"8B74A99C-0C68-461E-BF79-3096C058D3F1": {"Percent": 0.0,"AnswerResult": {"Answer": "9EB0842B-F793-45B7-B1D7-6A8ACDDF695B","QuestionType": 1}}} |
A note about the data model: each record is the 'state' of a quiz attempt. Quizzes contain a series of Questions. In this way, the exact question responses are captured in the Response column but can be effectively any value that the system recognizes as a question. So the Response object has a structure of:
{
"Question Identifier":
{
"Percent": value,
"AnswerResult":
{
"Answer": value
,"QuestionType": value
}
}
}
Needed Output After Transform
Because the Question Identifier is both a JSON property key and an identifier to a question (which I can get from another OData query), I need the Question Identifier as a list. The Parse JSON transform (stock) attempts to create a new column with the value of the QuestionId (a guid) as the column name. Expanding this way creates a static list of 'strings' in the transform, which will break anytime a new question is added to the response object. I need the output to look like the table below after the first JSON transform. Once in this format, I can handle the rest, I think.
| RecordId | EnrollmentId | QuizId | QuestionId | ResponseValue |
| 70F7A8E9-B59F-417B-A074-08D745B691B7 | 4B2651CC-0718-489A-8934-0E810365E256 | 91C4EBCC-2D7E-46EB-BBEA-B2A0661DE04A | EABB39EC-9A43-4271-9B8A-44E132099D5B | {"Percent": 0.0,"AnswerResult": {"Answer": "7EEBA0A8-F0C2-4773-BDB4-26F3FC512DCF","QuestionType": 1}} |
| 70F7A8E9-B59F-417B-A074-08D745B691B7 | 4B2651CC-0718-489A-8934-0E810365E256 | 91C4EBCC-2D7E-46EB-BBEA-B2A0661DE04A | A2A7272C-4B38-4E1E-951B-7512AFAB1712 | {"Percent": 0.0,"AnswerResult": {"Answer": "C4A0D147-A7A6-4F2C-88A7-A61C78F5A389","QuestionType": 1}} |
| 5161BD49-2918-4626-9154-CD3D4A4A4390 | BEAAD33B-50C5-42DF-BD1D-C9E9904F3235 | 67E19DA5-5BE8-4CBC-9951-1CDB8DBF40B8 | 45DC5861-F3ED-4E98-BEDE-1E1B5B8578CA | {"Percent": 0.0,"AnswerResult": {"Answer": "E8C99291-6C81-454C-8D0F-ADB73358D516","QuestionType": 1}} |
| 5161BD49-2918-4626-9154-CD3D4A4A4390 | BEAAD33B-50C5-42DF-BD1D-C9E9904F3235 | 67E19DA5-5BE8-4CBC-9951-1CDB8DBF40B8 | FA04612C-6C0E-429E-A97E-FE27E5B2E4B1 | {"Percent": 0.0,"AnswerResult": {"Answer": "6CB77D11-8BBE-44AB-9D16-C3925719984B","QuestionType": 1}} |
| 18AB3B1A-A5CB-499C-816D-F83C493F95B1 | 94A3B295-4F86-4333-8604-17BA1DFE3884 | 5DBA2205-1D65-4664-8FC5-8C2A6F70C40D | 3AA95D56-8B5C-4663-96E5-B99FE2B1D07B | {"Percent": 0.0,"AnswerResult": {"Answer": "3B80C204-55B8-40D7-9558-42599F5BDEE9","QuestionType": 1}} |
| 18AB3B1A-A5CB-499C-816D-F83C493F95B1 | 94A3B295-4F86-4333-8604-17BA1DFE3884 | 5DBA2205-1D65-4664-8FC5-8C2A6F70C40D | 8B74A99C-0C68-461E-BF79-3096C058D3F1 | {"Percent": 0.0,"AnswerResult": {"Answer": "9EB0842B-F793-45B7-B1D7-6A8ACDDF695B","QuestionType": 1}} |
Thanks again for any insight. BB.
Hi Anonymous, what about this?
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVQ9jxU7DP0raOo1ihM7sensOKnh6XV7qdAtkNCC2EUIof3veLS0bHE1TRKdOD4fnvv7Y5Q9TJaCs24gHA5WBkGRGMTeFX0cdwd57YxzQhkoQKIGoi1hS7C0zqtyT5jipOUJqzEWUF8O7svAq5XeMVYhS9jvy7HMvemaoEYNqA4EdTEgWthqUQ32y/HuTULfX79/uj48nbvyttxdDnt4/Hn9/t/18ceXp7+Yl6NzcznGWm7FBHaZFWiMBh5OUPtuezLWmPtyZJ0PP66PT5+/Pvz/69v1vIrPz2f1aqOOOoG8JdWFC5TRYeRN2+Y4sN7Y2SQrgTTAhnWgnY+IWG47ziGbrYn+q7Pn4+Pd/cHY0YMUqp4+9NpBkQlmtCDLr2lJgVNzi9YcuExOdWOnBBgwdakW2q02TlgfCzWMgX1ltelpR3IFnOESvqm4vNhFHJOlI+y2IkVRAV+xABc6u/CQaTeKsmSqJh3oUxCIKUWJssHCR2sskZxfsWtboY6pZJ8lI1d1gWmGb686Fntd5HhjZ336GIEIkiHOZJqDBnaYTSsPVBXy1+1CMW+OBsbTgVSTG/aALW2Stq3seI4NWfOq6dSWzEVrDaQXgpxGw9iriVDCONxqLQwYPbG9E0gGGmRW63uUSSVe7GpmysE9O+d5IhtoXwyumro4Rhm3TldzybHK5jh9h3xx5HhwripncfZY658ZzlPxQXbqUGY/A5zT5XsotKJpYLrd9q126fIiVB2yXv5S2Ack0wHdxGbE7sqv2fXxDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RecordId = _t, EnrollmentId = _t, QuizId = _t, Response = _t]),
#"Duplicated Column" = Table.DuplicateColumn(Source, "Response", "ResponseValue"),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"Response", each Text.BeforeDelimiter(_, "{", 1), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Response", "QuestionId"}}),
Clean_QuestionID_ResponseValue = Table.TransformColumns(#"Renamed Columns",{{"QuestionId", each Text.Trim(_, {"{", ":", " ", """"}), type text}, {"ResponseValue", each Text.AfterDelimiter(_, ": "), type text}})
in
Clean_QuestionID_ResponseValuedufoq3 Getting closer. Output is not exactly as expected. The string manipulation only pulls one QuestionId out of the 'Response' JSON and there are 2. Should end up with 6 rows. I dumped your code into Power Query and confirmed (see screenshot). The red highlighted value should breakout into a second row value for QuestionId (and then the string behind it becomes the value for ResponseValue). The real challenge here is that I could have 2 or more question responses. So the ultimate solution will be something that dynamically transforms this object.
Power Query does not take kindly to dynamic output column changes. You will be perpetually trapped in the "Evaluating..." issue of meta data updates. You need to unpivot your response data to avoid that.
That's what I was afraid of. I don't control the source, just consume from it. I'm going to have to dump to a local SQL db, re-model there (and probably just normalize into a small relational model anyway), then bring back to Power Query to build the report.
Thanks for your input.
BB
