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 kgubler,
the trick is to transform the records with the different field names into a list before expanding:
That way, the records field names doesn't matter any more.
To check, please paste this code into the advaned editor:
let
Source = "{#(lf) ""@odata.context"": ""https://graph.microsoft.com/v1.0/$metadata#Collection(microsoft.graph.plannerTask)"",#(lf) ""@odata.count"": 5,#(lf) ""@odata.nextLink"": ""https://graph.microsoft.com/v1.0/Planner/Plans/-P7_IGBCtkiJyt2xBSqWemQAExwv/Tasks?$skiptoken=1%2523A%2b8586855581914330690%2b863peSslZkurdOcNBmN9e2QAOSxn"",#(lf) ""value"": [#(lf) {#(lf) ""@odata.etag"": ""W/\""JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBAUCc=\"""",#(lf) ""createdBy"": {#(lf) ""user"": {#(lf) ""displayName"": null,#(lf) ""id"": ""d3e9e991-14a4-4f9b-8b9d-4d5f25eac966""#(lf) }#(lf) },#(lf) ""planId"": ""-P7_IGBCtkiJyt2xBSqWemQAExwv"",#(lf) ""bucketId"": ""g5376dgDB02uAN83h-IhvWQAHDLF"",#(lf) ""title"": ""Task 5"",#(lf) ""orderHint"": ""8586854698039826339"",#(lf) ""assigneePriority"": """",#(lf) ""percentComplete"": 50,#(lf) ""startDateTime"": null,#(lf) ""createdDateTime"": ""2018-01-16T21:11:21.4949468Z"",#(lf) ""dueDateTime"": null,#(lf) ""hasDescription"": false,#(lf) ""previewType"": ""automatic"",#(lf) ""completedDateTime"": null,#(lf) ""completedBy"": null,#(lf) ""referenceCount"": 0,#(lf) ""checklistItemCount"": 0,#(lf) ""activeChecklistItemCount"": 0,#(lf) ""appliedCategories"": {},#(lf) ""assignments"": {#(lf) ""1cbfdb94-5650-4a97-af52-f36880e7bb05"": {#(lf) ""@odata.type"": ""#microsoft.graph.plannerAssignment"",#(lf) ""assignedBy"": {#(lf) ""user"": {#(lf) ""displayName"": null,#(lf) ""id"": ""d3e9e991-14a4-4f9b-8b9d-4d5f25eac966""#(lf) }#(lf) },#(lf) ""assignedDateTime"": ""2018-01-16T21:13:40.5867453Z"",#(lf) ""orderHint"": """"#(lf) }#(lf) },#(lf) ""conversationThreadId"": null,#(lf) ""id"": ""ZUlmdtP3WEepk6yFRElcPmQALknV""#(lf) },#(lf) {#(lf) ""@odata.etag"": ""W/\""JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBARCc=\"""",#(lf) ""createdBy"": {#(lf) ""user"": {#(lf) ""displayName"": null,#(lf) ""id"": ""d3e9e991-14a4-4f9b-8b9d-4d5f25eac966""#(lf) }#(lf) },#(lf) ""planId"": ""-P7_IGBCtkiJyt2xBSqWemQAExwv"",#(lf) ""bucketId"": ""mxT1Zno0ZUa1WQ_Qzal8QGQAGh3U"",#(lf) ""title"": ""Task 4"",#(lf) ""orderHint"": ""8586854698075421298"",#(lf) ""assigneePriority"": """",#(lf) ""percentComplete"": 0,#(lf) ""startDateTime"": null,#(lf) ""createdDateTime"": ""2018-01-16T21:11:17.9354509Z"",#(lf) ""dueDateTime"": null,#(lf) ""hasDescription"": false,#(lf) ""previewType"": ""automatic"",#(lf) ""completedDateTime"": null,#(lf) ""completedBy"": null,#(lf) ""referenceCount"": 0,#(lf) ""checklistItemCount"": 0,#(lf) ""activeChecklistItemCount"": 0,#(lf) ""appliedCategories"": {},#(lf) ""assignments"": {},#(lf) ""conversationThreadId"": null,#(lf) ""id"": ""aojdRXYRak6x4kMmUIlSkmQANTbc""#(lf) },#(lf) {#(lf) ""@odata.etag"": ""W/\""JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBARCc=\"""",#(lf) ""createdBy"": {#(lf) ""user"": {#(lf) ""displayName"": null,#(lf) ""id"": ""d3e9e991-14a4-4f9b-8b9d-4d5f25eac966""#(lf) }#(lf) },#(lf) ""planId"": ""-P7_IGBCtkiJyt2xBSqWemQAExwv"",#(lf) ""bucketId"": ""mxT1Zno0ZUa1WQ_Qzal8QGQAGh3U"",#(lf) ""title"": ""Task 3"",#(lf) ""orderHint"": ""8586854698106996886"",#(lf) ""assigneePriority"": """",#(lf) ""percentComplete"": 0,#(lf) ""startDateTime"": null,#(lf) ""createdDateTime"": ""2018-01-16T21:11:14.7778921Z"",#(lf) ""dueDateTime"": null,#(lf) ""hasDescription"": false,#(lf) ""previewType"": ""automatic"",#(lf) ""completedDateTime"": null,#(lf) ""completedBy"": null,#(lf) ""referenceCount"": 0,#(lf) ""checklistItemCount"": 0,#(lf) ""activeChecklistItemCount"": 0,#(lf) ""appliedCategories"": {},#(lf) ""assignments"": {},#(lf) ""conversationThreadId"": null,#(lf) ""id"": ""GNkaqs4BDkmHthGXt7rRVmQAIRc7""#(lf) },#(lf) {#(lf) ""@odata.etag"": ""W/\""JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBARCc=\"""",#(lf) ""createdBy"": {#(lf) ""user"": {#(lf) ""displayName"": null,#(lf) ""id"": ""d3e9e991-14a4-4f9b-8b9d-4d5f25eac966""#(lf) }#(lf) },#(lf) ""planId"": ""-P7_IGBCtkiJyt2xBSqWemQAExwv"",#(lf) ""bucketId"": ""mxT1Zno0ZUa1WQ_Qzal8QGQAGh3U"",#(lf) ""title"": ""Task 2"",#(lf) ""orderHint"": ""8586854698136865161"",#(lf) ""assigneePriority"": """",#(lf) ""percentComplete"": 0,#(lf) ""startDateTime"": null,#(lf) ""createdDateTime"": ""2018-01-16T21:11:11.7910646Z"",#(lf) ""dueDateTime"": null,#(lf) ""hasDescription"": false,#(lf) ""previewType"": ""automatic"",#(lf) ""completedDateTime"": null,#(lf) ""completedBy"": null,#(lf) ""referenceCount"": 0,#(lf) ""checklistItemCount"": 0,#(lf) ""activeChecklistItemCount"": 0,#(lf) ""appliedCategories"": {},#(lf) ""assignments"": {},#(lf) ""conversationThreadId"": null,#(lf) ""id"": ""EuOGAEYGpUSogY-Kd_qilGQANw2y""#(lf) },#(lf) {#(lf) ""@odata.etag"": ""W/\""JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBAXCc=\"""",#(lf) ""createdBy"": {#(lf) ""user"": {#(lf) ""displayName"": null,#(lf) ""id"": ""d3e9e991-14a4-4f9b-8b9d-4d5f25eac966""#(lf) }#(lf) },#(lf) ""planId"": ""-P7_IGBCtkiJyt2xBSqWemQAExwv"",#(lf) ""bucketId"": ""EiG-2X7DMkCJS7u8_JucyGQALV_W"",#(lf) ""title"": ""Test task 1"",#(lf) ""orderHint"": ""8586855581914330690"",#(lf) ""assigneePriority"": ""8586855577804544375"",#(lf) ""percentComplete"": 50,#(lf) ""startDateTime"": null,#(lf) ""createdDateTime"": ""2018-01-15T20:38:14.0445117Z"",#(lf) ""dueDateTime"": null,#(lf) ""hasDescription"": false,#(lf) ""previewType"": ""automatic"",#(lf) ""completedDateTime"": null,#(lf) ""completedBy"": null,#(lf) ""referenceCount"": 0,#(lf) ""checklistItemCount"": 0,#(lf) ""activeChecklistItemCount"": 0,#(lf) ""appliedCategories"": {},#(lf) ""assignments"": {#(lf) ""1cbfdb94-5650-4a97-af52-f36880e7bb05"": {#(lf) ""@odata.type"": ""#microsoft.graph.plannerAssignment"",#(lf) ""assignedBy"": {#(lf) ""user"": {#(lf) ""displayName"": null,#(lf) ""id"": ""d3e9e991-14a4-4f9b-8b9d-4d5f25eac966""#(lf) }#(lf) },#(lf) ""assignedDateTime"": ""2018-01-17T18:50:51.1539215Z"",#(lf) ""orderHint"": """"#(lf) },#(lf) ""d3e9e991-14a4-4f9b-8b9d-4d5f25eac966"": {#(lf) ""@odata.type"": ""#microsoft.graph.plannerAssignment"",#(lf) ""assignedBy"": {#(lf) ""user"": {#(lf) ""displayName"": null,#(lf) ""id"": ""d3e9e991-14a4-4f9b-8b9d-4d5f25eac966""#(lf) }#(lf) },#(lf) ""assignedDateTime"": ""2018-01-15T20:45:05.0231432Z"",#(lf) ""orderHint"": """"#(lf) }#(lf) },#(lf) ""conversationThreadId"": null,#(lf) ""id"": ""863peSslZkurdOcNBmN9e2QAOSxn""#(lf) }#(lf) ]#(lf)}",
Custom1 = Json.Document(Source),
value = Custom1[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"@odata.etag", "createdBy", "planId", "bucketId", "title", "orderHint", "assigneePriority", "percentComplete", "startDateTime", "createdDateTime", "dueDateTime", "hasDescription", "previewType", "completedDateTime", "completedBy", "referenceCount", "checklistItemCount", "activeChecklistItemCount", "appliedCategories", "assignments", "conversationThreadId", "id"}, {"@odata.etag", "createdBy", "planId", "bucketId", "title", "orderHint", "assigneePriority", "percentComplete", "startDateTime", "createdDateTime", "dueDateTime", "hasDescription", "previewType", "completedDateTime", "completedBy", "referenceCount", "checklistItemCount", "activeChecklistItemCount", "appliedCategories", "assignments", "conversationThreadId", "id"}),
Cleanup = Table.SelectColumns(#"Expanded Column1",{"id", "assignments"}),
AddAssignmentsList = Table.AddColumn(Cleanup, "Custom", each Record.FieldValues([assignments])),
ExpandAssignmentList = Table.ExpandListColumn(AddAssignmentsList, "Custom"),
Expand = Table.ExpandRecordColumn(ExpandAssignmentList, "Custom", {"@odata.type", "assignedBy", "assignedDateTime", "orderHint"}, {"[email protected]", "Assignment.assignedBy", "Assignment.assignedDateTime", "Assignment.orderHint"}),
ExpandUser = Table.ExpandRecordColumn(Expand, "Assignment.assignedBy", {"user"}, {"user"}),
ExpandUserRecord = Table.ExpandRecordColumn(ExpandUser, "user", {"displayName", "id"}, {"user.displayName", "user.id"})
in
ExpandUserRecord