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 ...
Anonymous
In Power BI I do not see the raw JSON code because it is coming in from the custom connector (which is requred to handle authentication). If I use the Graph Explorer I can see the code is as follows...
{
"@odata.context": "https://graph.microsoft.com/v1.0/$metadata#Collection(microsoft.graph.plannerTask)",
"@odata.count": 5,
"@odata.nextLink": "https://graph.microsoft.com/v1.0/Planner/Plans/-P7_IGBCtkiJyt2xBSqWemQAExwv/Tasks?$skiptoken=1%2523A%2b8586855581914330690%2b863peSslZkurdOcNBmN9e2QAOSxn",
"value": [
{
"@odata.etag": "W/\"JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBAUCc=\"",
"createdBy": {
"user": {
"displayName": null,
"id": "d3e9e991-14a4-4f9b-8b9d-4d5f25eac966"
}
},
"planId": "-P7_IGBCtkiJyt2xBSqWemQAExwv",
"bucketId": "g5376dgDB02uAN83h-IhvWQAHDLF",
"title": "Task 5",
"orderHint": "8586854698039826339",
"assigneePriority": "",
"percentComplete": 50,
"startDateTime": null,
"createdDateTime": "2018-01-16T21:11:21.4949468Z",
"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-16T21:13:40.5867453Z",
"orderHint": ""
}
},
"conversationThreadId": null,
"id": "ZUlmdtP3WEepk6yFRElcPmQALknV"
},
{
"@odata.etag": "W/\"JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBARCc=\"",
"createdBy": {
"user": {
"displayName": null,
"id": "d3e9e991-14a4-4f9b-8b9d-4d5f25eac966"
}
},
"planId": "-P7_IGBCtkiJyt2xBSqWemQAExwv",
"bucketId": "mxT1Zno0ZUa1WQ_Qzal8QGQAGh3U",
"title": "Task 4",
"orderHint": "8586854698075421298",
"assigneePriority": "",
"percentComplete": 0,
"startDateTime": null,
"createdDateTime": "2018-01-16T21:11:17.9354509Z",
"dueDateTime": null,
"hasDescription": false,
"previewType": "automatic",
"completedDateTime": null,
"completedBy": null,
"referenceCount": 0,
"checklistItemCount": 0,
"activeChecklistItemCount": 0,
"appliedCategories": {},
"assignments": {},
"conversationThreadId": null,
"id": "aojdRXYRak6x4kMmUIlSkmQANTbc"
},
{
"@odata.etag": "W/\"JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBARCc=\"",
"createdBy": {
"user": {
"displayName": null,
"id": "d3e9e991-14a4-4f9b-8b9d-4d5f25eac966"
}
},
"planId": "-P7_IGBCtkiJyt2xBSqWemQAExwv",
"bucketId": "mxT1Zno0ZUa1WQ_Qzal8QGQAGh3U",
"title": "Task 3",
"orderHint": "8586854698106996886",
"assigneePriority": "",
"percentComplete": 0,
"startDateTime": null,
"createdDateTime": "2018-01-16T21:11:14.7778921Z",
"dueDateTime": null,
"hasDescription": false,
"previewType": "automatic",
"completedDateTime": null,
"completedBy": null,
"referenceCount": 0,
"checklistItemCount": 0,
"activeChecklistItemCount": 0,
"appliedCategories": {},
"assignments": {},
"conversationThreadId": null,
"id": "GNkaqs4BDkmHthGXt7rRVmQAIRc7"
},
{
"@odata.etag": "W/\"JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBARCc=\"",
"createdBy": {
"user": {
"displayName": null,
"id": "d3e9e991-14a4-4f9b-8b9d-4d5f25eac966"
}
},
"planId": "-P7_IGBCtkiJyt2xBSqWemQAExwv",
"bucketId": "mxT1Zno0ZUa1WQ_Qzal8QGQAGh3U",
"title": "Task 2",
"orderHint": "8586854698136865161",
"assigneePriority": "",
"percentComplete": 0,
"startDateTime": null,
"createdDateTime": "2018-01-16T21:11:11.7910646Z",
"dueDateTime": null,
"hasDescription": false,
"previewType": "automatic",
"completedDateTime": null,
"completedBy": null,
"referenceCount": 0,
"checklistItemCount": 0,
"activeChecklistItemCount": 0,
"appliedCategories": {},
"assignments": {},
"conversationThreadId": null,
"id": "EuOGAEYGpUSogY-Kd_qilGQANw2y"
},
{
"@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"
}
]
}Power BI seems to parse that reponse and shows this is what it looks like in Power BI...
I expand out the list and get to here...
However, I can't expand the assignment because of the way the JSON is formatted Power BI tries to use the assignement guid as the column name which won't work.
Hi, I am struggling with the exactly same issue and found this thread.
My approach is a bit different: I have created a MS Flow which uses Graph API to get O365 Groups; --> Plans --> Tasks associated to the group and finally the assignees for each Task. Ultimately I want to have the same result: a table which lists the task id in one column and the user guids in another.
However, I also hit the wall trying to parse the user id:s out of the result. Hoping to find the solution in this thread..