Forum Discussion

Don-Bot's avatar
Don-Bot
Icon for Helper V rankHelper V
1 year ago
Solved

oData Dataflow, need to expand into 2 levels

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

4 Replies