Forum Discussion

kgubler's avatar
kgubler
Frequent Visitor
8 years ago

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 ...
kgubler's avatar
kgubler
Frequent Visitor
8 years ago

Anonymous

 

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!!

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

kgubler,

In your first post, you only post part of the JSON file. Could you please post the full JSON code here?

Regards,
Lydia

  • kgubler's avatar
    kgubler
    Frequent Visitor
    8 years ago

    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.

      

    • Apala's avatar
      Apala
      New Member
      8 years ago

      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..

       

       

       

       

    • ImkeF's avatar
      ImkeF
      Community Champion
      8 years ago

      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