Forum Discussion
Azure DevOps OData Query Expand Column error : Teams & Iteration columns
- 1 year ago
Hi Anonymous ,
it was showing N+1 error...i removed some unwanted columns from the table and was able to load the data properly after that. Thank you so much for all the support 👍
Hi Anonymous
Till merging step it is working fine. But after the expansion i am getting the below error in ADO 1:
ADO 1 code:
let
Source = OData.Feed(#"URL"),
WorkItems_table = Source{[Name="WorkItems",Signature="table"]}[Data],
// unwanted column removed
#"Removed Columns" = Table.RemoveColumns(WorkItems_table,{"ProjectSK", "WorkItemRevisionSK", "AreaSK", "IterationSK", "AssignedToUserSK", "ChangedByUserSK", "CreatedByUserSK", "ActivatedByUserSK", "ClosedByUserSK", "ResolvedByUserSK", "InProgressDateSK", "CompletedDateSK", "ActivatedDateSK", "ChangedDateSK", "ClosedDateSK", "CreatedDateSK", "ResolvedDateSK", "StateChangeDateSK", "Watermark", "Microsoft_VSTS_CodeReview_AcceptedBySK", "Microsoft_VSTS_CodeReview_AcceptedDate", "Microsoft_VSTS_CodeReview_ClosedStatus", "Microsoft_VSTS_CodeReview_ClosedStatusCode", "Microsoft_VSTS_CodeReview_ClosingComment", "Microsoft_VSTS_CodeReview_Context", "Microsoft_VSTS_CodeReview_ContextCode", "Microsoft_VSTS_CodeReview_ContextOwner", "Microsoft_VSTS_CodeReview_ContextType", "Microsoft_VSTS_Common_ReviewedBySK", "Microsoft_VSTS_Common_StateCode", "Microsoft_VSTS_Feedback_ApplicationType", "Microsoft_VSTS_TCM_TestSuiteType", "Microsoft_VSTS_TCM_TestSuiteTypeId", "Project", "Teams", "Iteration"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"WorkItemId"}, #"ADO Data 2", {"WorkItemId"}, "ADO Data 2", JoinKind.LeftOuter),
#"Expanded ADO Data 2" = Table.ExpandTableColumn(#"Merged Queries", "ADO Data 2", {"TeamName", "Project", "ProjectName", "IterationName", "StartDate", "EndDate"}, {"TeamName", "Project", "ProjectName", "IterationName", "StartDate.1", "EndDate"})
in
#"Expanded ADO Data 2"
ADO 2 code:
let
Source = OData.Feed(#"URL"),
WorkItems_table = Source{[Name="WorkItems",Signature="table"]}[Data],
// unwanted column removed
#"Removed Other Columns" = Table.SelectColumns(WorkItems_table,{"WorkItemId", "Teams", "Project", "Iteration"}),
// Project column expanded
#"Expanded Project" = Table.ExpandRecordColumn(#"Removed Other Columns", "Project", {"ProjectName"}, {"ProjectName"}),
// Team column expanded
#"Expanded Teams" = Table.ExpandTableColumn(#"Expanded Project", "Teams", {"TeamName", "Project"}, {"TeamName", "Project"}),
// Iteration column expanded
#"Expanded Iteration" = Table.ExpandRecordColumn(#"Expanded Teams", "Iteration", {"IterationName", "StartDate", "EndDate"}, {"IterationName", "StartDate", "EndDate"}),
// Removed duplicate WorkItemId
#"Removed Duplicates" = Table.Distinct(#"Expanded Iteration", {"WorkItemId"})
in
#"Removed Duplicates"
Hi WinterGarden,
The error screenshot is not clear. Could you please reattach it?
Regards,
Vinay Pabbu