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 ,
I am still working on this issue..
After the merging step, the preview was loaded,
when i tried to expand the columns, it is taking way too much of time...
It has taken more than 5 hours to load 2.3 GB of data and is still in progress.
I will let you know once the loading is complete.
Hi WinterGarden,
As we haven't heard back from you, At this time we are closing this thread. If you have any further issues, please start a new thread in the community forum, and we are here to assist you. Thankyou for your understanding and continuous support.
Thank you for being part of the Microsoft Fabric Community.
Regards,
Vinay Pabbu
- WinterGarden1 year agoResolver I
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"- Anonymous1 year agoNot applicable
Hi WinterGarden,
The error screenshot is not clear. Could you please reattach it?
Regards,
Vinay Pabbu
- WinterGarden1 year agoResolver I
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 👍