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 ...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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,
Lydia

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