Forum Discussion
Parse JSON Column with the Relational Key Value as an Attribute
Greetings all,
Problem:
I am currently attempting to ingest data into our Power BI ecosphere by building a dataflow connected to an Odata source. Connection is set up and functioning correctly. When I begin transforming data in Power Query, I found that one of the key pieces of information was in a JSON structure I've never worked with before in Power BI. The JSON object is structured with the relational key (think foreign key in SQL db terms) as the Attribute/Property and then a nested JSON object as its value. The consequence of this, is when I use the 'Parse JSON' transform, it pivots these key values as column headers.
Question:
Is there a way to parse this object in Power Query such that the attribute becomes a key column with its values as rows instead of each attribute mapping to a separate column? I'm assuming this will be accomplished with the advanced editor and M script. I have provided an example object shape below with before and after "transform output" examples.
Existing JSON Object:
Array: [
{
ObjectId: guid,
Attribute1: guid,
Attribute2: guid,
GUID:{SubAttribute1:guid,SubAttribute2:string,SubAttribute3:string}
},
{
ObjectId: guid,
Attribute1: guid,
Attribute2: guid,
GUID:{SubAttribute1:guid,SubAttribute2:string,SubAttribute3:string}
},
{
ObjectId: guid,
Attribute1: guid,
Attribute2: guid,
GUID:{SubAttribute1:guid,SubAttribute2:string,SubAttribute3:string}
}
]
Example Transform:
Appreciate any feedback available. BB
8 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Anonymous,
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSi/NTFHSQaOqg0uTHEtKijKTSktSDa1AojrIQkZWxUBWXjqKoDFUsBZoAAjF6uAwnSoW4DOdcgtiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ObjectID = _t, Attribute1 = _t, Attribute2 = _t, GUID1 = _t, GUID2 = _t, GUID3 = _t]), GuidColumns = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_,"GUID")), StepBack = Source, ReplacedGuidColumns = Table.ReplaceValue(StepBack,"",null,Replacer.ReplaceValue, GuidColumns), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(ReplacedGuidColumns, List.Select(Table.ColumnNames(ReplacedGuidColumns), each not List.Contains(GuidColumns, _)), "TransformAttribute", "Value") in #"Unpivoted Other Columns"Thank you dufoq3 for your very thorough response. Unfortunately, I did a poor job with the example and because of that, your solution is too literal. For example, your code is searching for the literal value of 'GUID' and then unpivoting the columns accordingly. The variables GUID1, GUID2 and GUID3 are actually real GUID values that are dynamic (e.g., unknown to me at the time the query compiles). They are actually IDs to another object that I am pulling in separately. I will be uploading a better example shortly with a more representative dataset example. Thanks again.
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 TransformBecause 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.
