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 having a hard time pulling in a date column. Can someone help me modify the query so I have the 'DakeSK' column pulled in? Thank you so much!

 

 

 

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 Columns" = Table.RenameColumns(#"Expanded TestSuite",{{"TestSuite.TitleLevel1", "Title"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"TotalCount", Int64.Type}, {"PassedCount", Int64.Type}, {"FailedCount", Int64.Type}, {"NotRunCount", Int64.Type}, {"RunCount", Int64.Type}, {"RunPercentage", type number}, {"NotRunPercentage", type number}, {"PassedPercentage", type number}, {"FailedPercentage", type number}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "TestPlanId", "TestPlanId - Copy"),
    #"Inserted Literal" = Table.AddColumn(#"Duplicated Column", "Literal", each "https://dev.azure.com/" & Organization & "/" & Project & "/_testPlans/execute?planId=", type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Inserted Literal",{"TestPlanId", "Title", "TotalCount", "PassedCount", "FailedCount", "NotRunCount", "RunCount", "RunPercentage", "NotRunPercentage", "PassedPercentage", "FailedPercentage", "Literal", "TestPlanId - Copy"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"TestPlanId - Copy", type text}}, "en-GB"),{"Literal", "TestPlanId - Copy"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"URL")
in
    #"Merged Columns"

 

 

 

1 Reply

  • 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