Azure DevOps OData Query Expand Column error : Teams & Iteration columns

[WinterGarden]

Hi All,
I am trying to load Azure DevOps data into PBI and i am getting the below error when i tried to expand the "Teams" column:

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 column expanded
#"Expanded Project" = Table.ExpandRecordColumn(#"Removed Columns", "Project", {"ProjectName"}, {"ProjectName"}),
#"Expanded Teams" = Table.ExpandTableColumn(#"Expanded Project", "Teams", {"TeamName", "Project"}, {"TeamName", "Project"})
in
#"Expanded Teams"

Expanded columns from iteration contains error :

when i clicked on the "Error" in the column this is what i am getting:

Can someone help identify the issue?

[v-vpabbu]

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

[v-vpabbu]

Hi @WinterGarden,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[v-vpabbu]

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

[WinterGarden]

Hi @v-vpabbu ,

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.

[v-vpabbu]

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

[WinterGarden]

Hi @v-vpabbu 

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"

[v-vpabbu]

Hi @WinterGarden,

The error screenshot is not clear. Could you please reattach it?

Regards,

Vinay Pabbu

[WinterGarden · Accepted solution]

Hi @v-vpabbu ,
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 👍

[v-vpabbu]

Hi @WinterGarden,

Glad your issue has been resolved!
I suggest you to accept your own post as the solution — it will help other community members facing similar problems to find the answer faster.

Regards,

Vinay Pabbu

[WinterGarden]

hi @v-vpabbu ,
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:

[bhanu_gautam]

@WinterGarden , Try using below m code

m
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 column expanded
#"Expanded Project" = Table.ExpandRecordColumn(#"Removed Columns", "Project", {"ProjectName"}, {"ProjectName"}),
// Expand the Teams column
#"Expanded Teams" = Table.ExpandRecordColumn(#"Expanded Project", "Teams", {"TeamName", "Project"}, {"TeamName", "Project"})
in
#"Expanded Teams"

[WinterGarden]

Hi @bhanu_gautam ,

applied this mcode, but the expanded column contains error:

this is what i am getting when i clicked on the error

[v-vpabbu]

Hi @WinterGarden,

you're trying to expand the Teams column as if it's a record, but it's actually a table (list of records).

you need to first expand the list/table of teams into individual rows, then expand the columns inside each.

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

[WinterGarden]

hi @v-vpabbu ,

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:

[v-vpabbu]

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

[WinterGarden]

Hi @v-vpabbu ,

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"