Forum Discussion
Azure DevOps OData Query Expand Column error : Teams & Iteration columns
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 WinterGarden,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
Vinay Pabbu
hi Anonymous ,
Not yet...
As you mentioned, i tried to expand Projects,Teams,Iteration in a separate table.
then i tried to merge it with workitemid into the main ADO table.But it is giving error.
below is the mcode for the expanded table:
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"
Below is the mcode for the main ADO table:
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"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"WorkItemId"}, #"ADO Data 2", {"WorkItemId"}, "ADO Data 2", JoinKind.LeftOuter),
//pulling long-text fields
#"Retrieve VSTS Data" = Table.AddColumn(#"Merged Queries","VSTS", each Json.Document(VSTS.Contents(#"VSTS fields URL" &Number.ToText([WorkItemId])))),
#"Expanded VSTS" = Table.ExpandRecordColumn(#"Retrieve VSTS Data", "VSTS", {"fields"}, {"VSTS.fields"}),
#"Expanded VSTS.fields" = Table.ExpandRecordColumn(#"Expanded VSTS", "VSTS.fields", {"System.Description", "Microsoft.VSTS.Common.AcceptanceCriteria"}, {"VSTS.fields.System.Description", "VSTS.fields.Microsoft.VSTS.Common.AcceptanceCriteria"}),
// Cleaning up the long-text fields to remove HTML components
#"Add bullets for bulletlist" = Table.ReplaceValue(#"Expanded VSTS.fields","<li>", "<li>- ",Replacer.ReplaceText,{"VSTS.fields.System.Description","VSTS.fields.Microsoft.VSTS.Common.AcceptanceCriteria"} ),
#"Add lf for divs" = Table.ReplaceValue(#"Add bullets for bulletlist","<div>", "#(lf)<div>",Replacer.ReplaceText,{"VSTS.fields.System.Description","VSTS.fields.Microsoft.VSTS.Common.AcceptanceCriteria"} ),
#"Add lf for li" = Table.ReplaceValue(#"Add lf for divs","</li>", "</li>#(lf)",Replacer.ReplaceText,{"VSTS.fields.System.Description","VSTS.fields.Microsoft.VSTS.Common.AcceptanceCriteria"} ),
#"Remove HTML from System Description" = Table.AddColumn( #"Add lf for li","System Description", each if [VSTS.fields.System.Description] = null then null else Text.Combine(Html.Table([VSTS.fields.System.Description],{{"D",":root"}})[D],"#(lf)")),
#"Remove HTML from Acceptance Criteria" = Table.AddColumn( #"Remove HTML from System Description","Acceptance Criteria", each if [VSTS.fields.Microsoft.VSTS.Common.AcceptanceCriteria] = null then null else Text.Combine(Html.Table([VSTS.fields.Microsoft.VSTS.Common.AcceptanceCriteria],{{"H",":root"}})[H],"#(lf)"))
in
#"Remove HTML from Acceptance Criteria"
in the merged step i am getting the below error: