Forum Discussion
Expanding records from JSON
I am having trouble with transforming the data from a column. The data is coming from a JSON call to the MS Graph API that is getting Tasks from Planner. The challenge is that a task can be assigned to multiple users and each assignment is labeled with the unique user id. The JSON looks like this..
{
"@odata.etag": "W/\"JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBAXCc=\"",
"createdBy": {
"user": {
"displayName": null,
"id": "d3e9e991-14a4-4f9b-8b9d-4d5f25eac966"
}
},
"planId": "-P7_IGBCtkiJyt2xBSqWemQAExwv",
"bucketId": "EiG-2X7DMkCJS7u8_JucyGQALV_W",
"title": "Test task 1",
"orderHint": "8586855581914330690",
"assigneePriority": "8586855577804544375",
"percentComplete": 50,
"startDateTime": null,
"createdDateTime": "2018-01-15T20:38:14.0445117Z",
"dueDateTime": null,
"hasDescription": false,
"previewType": "automatic",
"completedDateTime": null,
"completedBy": null,
"referenceCount": 0,
"checklistItemCount": 0,
"activeChecklistItemCount": 0,
"appliedCategories": {},
"assignments": {
"1cbfdb94-5650-4a97-af52-f36880e7bb05": {
"@odata.type": "#microsoft.graph.plannerAssignment",
"assignedBy": {
"user": {
"displayName": null,
"id": "d3e9e991-14a4-4f9b-8b9d-4d5f25eac966"
}
},
"assignedDateTime": "2018-01-17T18:50:51.1539215Z",
"orderHint": ""
},
"d3e9e991-14a4-4f9b-8b9d-4d5f25eac966": {
"@odata.type": "#microsoft.graph.plannerAssignment",
"assignedBy": {
"user": {
"displayName": null,
"id": "d3e9e991-14a4-4f9b-8b9d-4d5f25eac966"
}
},
"assignedDateTime": "2018-01-15T20:45:05.0231432Z",
"orderHint": ""
}
},
"conversationThreadId": null,
"id": "863peSslZkurdOcNBmN9e2QAOSxn"
},As you can see each assignment has a unique id so when I try to expand the records I end up with a bunch of columns. Also if a new assignment is added it will break because the values are hard coded.
Here's what I'm talking about...
When I expand the columns
Unique values are used in column names.
The only information I need from the record is the user id. I want to either expand the id's into multiple columns like "Assigned1" "Assigned2" OR ideally I want to duplicate the taskId for each assignment. Ultimatly I would like it to look like this.
taskId | assignment |
863peSslZkurdOcNBmN9e2QAOSxn | 1cbfdb94-5650-4a97-af52-f36880e7bb05 |
863peSslZkurdOcNBmN9e2QAOSxn | d3e9e991-14a4-4f9b-8b9d-4d5f25eac966 |
Can anyone point me in the right direction on how I would go about writting the Power Query for this?
6 Replies
kgubler,
Add blank queries in your Power BI Desktop, and paste the following code into Advanced Editor of the two blank queries. Replace path with your own, and please note that in my scenario, the JSON file is called test119.let Source = Json.Document(File.Contents("Path\test119.JSON")), #"Converted to Table" = Record.ToTable(Source), #"Removed Top Rows" = Table.Skip(#"Converted to Table",19), Value = #"Removed Top Rows"{0}[Value], #"Converted to Table1" = Record.ToTable(Value), #"Removed Columns" = Table.RemoveColumns(#"Converted to Table1",{"Value"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},#"test119 (2)",{"Index"},"test119 (2)",JoinKind.LeftOuter), #"Expanded test119 (2)" = Table.ExpandTableColumn(#"Merged Queries", "test119 (2)", {"Value"}, {"test119 (2).Value"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded test119 (2)",{{"test119 (2).Value", "taskId"}, {"Name", "assignment"}}) in #"Renamed Columns"let Source = Json.Document(File.Contents("path\test119.JSON")), #"Converted to Table" = Record.ToTable(Source), #"Removed Top Rows" = Table.Skip(#"Converted to Table",19), #"Removed Top Rows1" = Table.Skip(#"Removed Top Rows",2), #"Appended Query" = Table.Combine({#"Removed Top Rows1", #"Removed Top Rows1"}), #"Added Index" = Table.AddIndexColumn(#"Appended Query", "Index", 1, 1) in #"Added Index"
Regards,
LydiaAnonymous
Lynda, thank you so much for helping with this! I tried your solution and I still wasn't able to make it work. Perhaps I need to explain a little more about what I'm doing.
I've created a custom connector that uses the Graph API to get planner tasks. The M code in the custom connector is simply...
let source = Json.Document(Web.Contents("https://graph.microsoft.com/v1.0/Planner/Plans/-P7_IGBCtkiJyt2xBSqWemQAExwv/Tasks")) in source;In Power BI I take that and convert it to the table using the following code...
let Source = MyGraph.Tasks(), value = Source[value], #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"title", "assignments", "id"}, {"title", "assignments", "id"}) in #"Expanded Column1"That gives me a table that looks like this...
This is where I get stuck. I need to expand the records from assignments but I couldn't quite figure out how to modify what you had provided to make it work.
Thanks again for your help!!
kgubler,
In your first post, you only post part of the JSON file. Could you please post the full JSON code here?
Regards,
Lydia
