oData Dataflow, need to expand into 2 levels

[Don-Bot]

So I'm relatively new to odata connections and I'm trying to work through one with Freshdesk.  

The issue I have is the initial connection has a parent and a child struct(?) in it's return.  So I want to expand the child one (which works fine) in dataflow.  The issue i have is there is another level "AllWorkLogs" that i want to expand that has the data I need.  When I try to expand that one I get the error

ataFormat.Error: OData: We were unable to compute or parse a URL for this navigation property. Error: When writing a JSON response, a user model must be specified and the entity set and entity type must be passed to the ODataMessageWriter.CreateODataResourceWriter method or the ODataResourceSerializationInfo must be set on the ODataResource or ODataResourceSet that is being written.DetailsReason = DataFormat.Error
Detail = AllWorklogs

Here is my m code including the attempted second expand.  

Any ideas how i can resolve this?  I also tried to do this with python but was unable to do so.  There I didn't see the child area to expand...

let
  Source = OData.Feed("https://MYCOMPANY.timehub.7pace.com/api/odata/v3.2/", null, [Implementation = "2.0", Timeout=#duration(0,2,0,0),
  Query = [
    #"workItemsFilter" = "(System_WorkItemType eq 'Epic' or System_WorkItemType eq 'User Story' or System_WorkItemType eq 'Task' or System_WorkItemType eq 'Acceptance Criteria' or System_WorkItemType eq 'Bug' or System_WorkItemType eq 'Feature' or System_WorkItemType eq 'Shared Steps' or System_WorkItemType eq 'Sprint Bug' or System_WorkItemType eq 'Support Engagement' or System_WorkItemType eq 'Test Case' or System_WorkItemType eq 'Test Plan' or System_WorkItemType eq 'Test Suite')",
    #"workLogsFilter" = "Timestamp ge 2025-04-01T00:00:00Z and Timestamp lt 2025-04-30T00:00:00Z"
  ]
  ]),
  #"Navigation 1" = Source{[Name = "workItemsHierarchyAllLevels", Signature = "table"]}[Data],
  #"Choose columns" = Table.SelectColumns(#"Navigation 1", {"Budget", "System_Id", "TrackedItself", "TrackedItselfBillable", "TrackedTotal", "TrackedTotalBillable", "HasChildren", "Parent", "Children"}),
  #"Expanded Children" = Table.ExpandTableColumn(#"Choose columns", "Children", {"System_Id", "TrackedItself", "TrackedItselfBillable", "TrackedTotal", "System_Reason", "System_State", "System_TeamProject", "System_Title", "System_WorkItemType", "AllWorklogs"}, {"System_Id.1", "TrackedItself.1", "TrackedItselfBillable.1", "TrackedTotal.1", "System_Reason", "System_State", "System_TeamProject", "System_Title", "System_WorkItemType", "AllWorklogs"}),
  #"Renamed columns" = Table.RenameColumns(#"Expanded Children", {{"System_Id", "ParentID"}, {"System_Id.1", "ChildID"}, {"TrackedTotal.1", "ChildTrackedTotal"}, {"System_State", "ChildState"}, {"System_TeamProject", "ChildTeamProject"}, {"System_Title", "ChildTitle"}, {"System_WorkItemType", "ChildWorkItemType"}}),
  #"Expanded AllWorklogs" = Table.ExpandTableColumn(#"Renamed columns", "AllWorklogs", {"User"}, {"User"})
in
  #"Expanded AllWorklogs"

if it helps here is the postman https I tried running to get username as well.  WHat i want is the child workitem id with the associated username (and sum of tracked total) with the workitemid or "System_ID"

https://MYCOMPANY.timehub.7pace.com/api/odata/v3.2/workItemsHierarchy?$select=System_Id,System_WorkItemType,System_TeamProject,System_Title,TrackedTotal,TrackedItself,HasChildren,System_AssignedTo&$filter=HasChildren eq true&rollupFields=Microsoft.VSTS.Scheduling.Effort&childrenQuery=({hierarchyField})/AllWorklogs?$apply=groupby((User/Name),aggregate(PeriodLength with sum as TrackedTotal))

[v-pgoloju]

Hi @Don-Bot,

Just a gentle reminder  has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @Don-Bot,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @Don-Bot,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju · Accepted solution]

Hi @Don-Bot,

Thank you for reaching out to the Microsoft Fabric Forum Community.

For connecting to OData, I kindly suggest referring to the official Microsoft documentation. Additionally, you may find the following community discussions helpful for your scenario.

https://learn.microsoft.com/en-us/power-query/connectors/odata-feed

https://community.fabric.microsoft.com/t5/Power-Query/OData-feed-expand-does-not-display-results/m-p...

https://community.fabric.microsoft.com/t5/Desktop/ODATA-with-Or-operator/m-p/4641849

https://community.fabric.microsoft.com/t5/Power-Query/OData-Using-nested-system-queries-for-Table-Ex...

If you found this information helpful, please consider marking the response as accepted and giving it a thumbsup. This will help others who may have similar questions. If the issue persists, feel free to reach out to us, we are here to assist you.

Warm regards,
Prasanna Kumar