cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FlowViz
Helper III
Helper III

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

@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

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

@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

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
Microsoft

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

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

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.