Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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...
Need Help
dufoq3's avatar
dufoq3
Community Champion
2 years ago

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_ResponseValue
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.


 

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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