Reply
FlowViz
Helper III
Helper III
Partially syndicated - Outbound

OData query with missing column

Hey guys,

 

I have a query I use for Azure DevOps:

 

https://analytics.dev.azure.com/{Organization}/{Project}/_odata/v3.0-preview/WorkItemRevisions?$select=WorkItemId,Title,AreaSK,Blocked,ProjectSK,CycleTimeDays,ChangedDate,ChangedDateSK,TagNames,CompletedDateSK,CompletedDate

 

The problem is, some projects contain the blocked column in the data table, some do not. I want to have one query I can use regardless of project, albeit I want to just bring in the necessary columns rather than them all.

 

Anyone have any advice?

2 ACCEPTED SOLUTIONS
V-pazhen-msft
Community Support
Community Support

Syndicated - Outbound

@FlowViz 

Try add a query step to your code, for example:

#"Column"=Table.SelectColumns(Users_table,{"ID","Date","Type","Amount"})
in
#"Column"

 

 

Best regards
Paul

View solution in original post

Syndicated - Outbound

Thanks - this is what I think I'm using atm:

#"Removed Columns" = Table.SelectColumns(Source,{"WorkItemId", "AreaSK", "Blocked", "InProgressDate", "InProgressDateSK", "State", "TagNames","Title", "WorkItemType", "ChangedDate", "ProjectSK","StateCategory"}, MissingField.UseNull),

View solution in original post

5 REPLIES 5
V-pazhen-msft
Community Support
Community Support

Syndicated - Outbound

@FlowViz 

Try add a query step to your code, for example:

#"Column"=Table.SelectColumns(Users_table,{"ID","Date","Type","Amount"})
in
#"Column"

 

 

Best regards
Paul

Syndicated - Outbound

Thanks - this is what I think I'm using atm:

#"Removed Columns" = Table.SelectColumns(Source,{"WorkItemId", "AreaSK", "Blocked", "InProgressDate", "InProgressDateSK", "State", "TagNames","Title", "WorkItemType", "ChangedDate", "ProjectSK","StateCategory"}, MissingField.UseNull),
mahoneypat
Microsoft Employee
Microsoft Employee

Syndicated - Outbound

If you get an error when you make a call with the Blocked column in the $select but it doesn't exist for that project, you could use try ... otherwise ... and check first with the Blocked version otherwise use the one w/o it.  Multiple web calls in some cases, but it should work.

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


FlowViz
Helper III
Helper III

Syndicated - Outbound

Previously I've used 

https://analytics.dev.azure.com/{Organization}/{Project}/_odata/v3.0-preview/WorkItemRevisions?

And brought in all columns then used MissingField.UseNull to filter...

edhans
Super User
Super User

Syndicated - Outbound

Can you bring in the query name vs having the columns listed in the URL like that?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)