Forum Discussion

einzelganger's avatar
einzelganger
Regular Visitor
3 years ago

OData feed to Azure DevOps URL Parameter for a Date column

Hello,   I'm doing some reporting in Power BI on our Azure DevOps projects, specifically around testing (what tests have been run, thier status, etc). I have the following query I am using, but hav...
Need Help
RobertPaegelow's avatar
RobertPaegelow
Helper I
3 years ago

DOes this work?

 

let
    Source = OData.Feed(#"Http/Https" & "://" & #"Analytics URL/Azure DevOps Server" & "/" & Organization & "/" & Project & "/_odata/v3.0-preview/TestPoints?%20$apply=filter((TestSuite/IdLevel1%20ne%20null))%20/groupby(%20(TestSuite/TitleLevel1,TestPlanId),%20aggregate(%20$count%20as%20TotalCount,%20cast(LastResultOutcome%20eq%20%27Passed%27,%20Edm.Int32)%20with%20sum%20as%20PassedCount,%20cast(LastResultOutcome%20eq%20%27Failed%27,%20Edm.Int32)%20with%20sum%20as%20FailedCount,%20cast(LastResultOutcome%20eq%20%27Blocked%27,%20Edm.Int32)%20with%20sum%20as%20BlockedCount,%20cast(LastResultOutcome%20eq%20%27NotApplicable%27,%20Edm.Int32)%20with%20sum%20as%20NotApplicableCount,%20cast(LastResultOutcome%20eq%20%27None%27,%20Edm.Int32)%20with%20sum%20as%20NotRunCount,%20cast(LastResultOutcome%20ne%20%27None%27,%20Edm.Int32)%20with%20sum%20as%20RunCount%20)%20)%20/compute(%20RunCount%20mul%20100%20div%20TotalCount%20as%20RunPercentage,%20NotRunCount%20mul%20100%20div%20TotalCount%20as%20NotRunPercentage,%20iif(TotalCount%20gt%20NotRunCount,%20PassedCount%20mul%20100%20div%20RunCount,0)%20as%20PassedPercentage,%20iif(TotalCount%20gt%20NotRunCount,%20FailedCount%20mul%20100%20div%20RunCount,0)%20as%20FailedPercentage,%20iif(TotalCount%20gt%20NotRunCount,%20BlockedCount%20mul%20100%20div%20RunCount,0)%20as%20BlockedPercentage,%20iif(TotalCount%20gt%20NotRunCount,%20NotApplicableCount%20mul%20100%20div%20RunCount,0)%20as%20NotApplicablePercentage%20)%20&$orderby=RunPercentage%20desc", null, [Implementation="2.0"]),
    #"Expanded TestSuite" = Table.ExpandRecordColumn(Source, "TestSuite", {"TitleLevel1"}, {"TestSuite.TitleLevel1"}),
    #"Renamed