Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kgubler
Frequent Visitor

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

2018-01-17_12-02-57.jpg

When I expand the columns

2018-01-17_12-03-11.jpg

Unique values are used in column names.

2018-01-17_12-17-58.jpg

 

 

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 6
v-yuezhe-msft
Employee
Employee

@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"

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft

 

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

2018-01-22_14-33-57.jpg

 

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft

 

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...2018-01-24_11-37-50.jpg

I expand out the list and get to here...

2018-01-24_11-39-17.jpg 

 

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:

 

image.png

 

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"}, {"Assignment.@odata.type", "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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.