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
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@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.
@Anonymous,
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"Helpful resources
Fabric Community Update - July 2025
Find out what's new and trending in the Fabric community.
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
