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,
You're encountering the Formula.Firewall error, which typically occurs when you combine data from different sources improperly, or try to join queries with different privacy levels or that access different data contexts.
Go to File > Options and settings > Options > Privacy.
Click on Ignore the Privacy Levels
This is OK in a controlled or development environment but not recommended for sensitive data or production scenarios.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
Vinay Pabbu
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"