Parse JSON Column with the Relational Key Value as an Attribute

[Syndicate_Admin]

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

[Syndicate_Admin]

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-...

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/1447...

[Syndicate_Admin]

@BBlakl2,

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"

[Syndicate_Admin]

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.

[Syndicate_Admin]

@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.

[Syndicate_Admin]

Hi @BBlakl2, 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_ResponseValue

[Syndicate_Admin]

@dufoq3 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.

[Syndicate_Admin]

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.

[Syndicate_Admin]

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