Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Loop through OData Feed Query

I am trying to use a Microsoft-provided query to return results from Azure DevOps, regarding Pipelines.  The page from MSFT is here (Stage Wise Failures) and the query is:   let Source = OData....
Need Help
PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

Hi Anonymous 

With your list of piplines contained in Pipeline List you can use this query to load data for each one

 

let
   ODataFeed = (PipeLine) => 

        OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/PipelineRunActivityResults?"
        &"$apply=filter( "
                &"Pipeline/PipelineName eq '{"& PipeLine & "}' "
                &"and PipelineRunCompletedOn/Date ge {startdate} "
                &"and PipelineRunOutcome eq 'Failed' "
        &"and TaskOutcome eq 'Failed' "
        &") "
            &"/groupby( "
                &"(PipelineRunCompletedOn/Date, PipelineRunId, PipelineJob/StageName ), "
                &"aggregate (FailedCount with sum as FailedCount)) "
            &"/groupby( "
                &"(PipelineRunCompletedOn/Date, PipelineJob/StageName ), "
            &"aggregate "
        &"(cast(FailedCount gt 0, Edm.Int32) with sum as FailedStageCount)) "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) ,

   Source = List.Transform(#"Pipeline List", each ODataFeed(_) )
in
    Source

 

Regards

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you PhilipTreacy  - that seems work for me. On first attempt I encountered an error message

 

which I resolved by creating a second list as a 'static' list. (I used 'Enter Data') I apologize, I had not mentioned that the list I was trying to reference was populated by one of the other queries above.

 

So I can get it working with a static list; would you suppose there's a way to reference a list which is created from a query?  My query for that list is:

let
    Source = let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/_odata/v3.0-preview/PipelineRuns?"
               &"$apply=filter( "
	       &"CompletedDate ge 2020-07-01Z "
	       &")"
                &"/groupby( "
        &"(Pipeline/PipelineName), "
        &"aggregate( "
            &"$count as TotalCount, "
                &"SucceededCount with sum as SucceededCount, "
                &"FailedCount with sum as FailedCount, "
            &"PartiallySucceededCount with sum as PartiallySucceededCount, "
                &"CanceledCount with sum as CanceledCount "
            &")) "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source,
    #"Expanded Pipeline" = Table.ExpandRecordColumn(Source, "Pipeline", {"PipelineName"}, {"Pipeline.PipelineName"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Pipeline",{{"TotalCount", Int64.Type}}),
    #"Pipeline PipelineName1" = #"Changed Type"[Pipeline.PipelineName]
in
    #"Pipeline PipelineName1"

 Thank you so much.