Loop through OData Feed Query

[Anonymous]

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.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/PipelineRunActivityResults?"
        &"$apply=filter( "
                &"Pipeline/PipelineName eq '{pipelinename}' "
                &"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]) 
in
    Source

I have many pipelines in my project and would like to return results for each of them.  The problem I am encountering is that {pipelinename} is a mandatory field, and I cannot figure a way to return multiple results.  In other MSFT queries of this type you can leave out some values in these queries if you wish to receive results over a wider range.

For example, in some queries you can omit the {project} from the initial source URL and then results will be returned for the entire {organization} rather than being limited to a specific project.  I have tried removing line 4 of the query

                &"Pipeline/PipelineName eq 'Oms Database' "

but this creates an error:

What I would like to know is if there is a way to somehow reference a list of pipelines within this query? If so it would prevent me from having to have approx. 25 individual queries, (one for each pipeline) and then appending or merging them all into a single table.  It also protects me in the case that a new pipeline is added.  I have a list of the pipelines in my dataset as a list, if that is of any help for referencing as a parameter?

[PhilipTreacy]

Hi @Anonymous 

Yes, there's not much to change.  If the query that creates the list is called ListQuery then chnage this line in the code I supplied

Source = List.Transform(ListQuery, each ODataFeed(_) )

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]

Thanks @PhilipTreacy , I know I'm close.  I'm getting results only as null though?  🤔

To review my steps, I am:

ONE

Running this query provided by Microsoft:

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/_odata/v3.0-preview/PipelineRuns?"
               &"$apply=filter( "
	       &"CompletedDate ge {startdate} "
	       &")"
                &"/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

I name this query Pipelines - Outcome Summary for all Pipelines.  In this query I have removed the {project} filter from the OData URL; I get a list of pipelines returned as a column in the results.  One of the Pipeline.PipelineName values is null, so I remove this - filter the column.

TWO

Right-click the column Pipeline.PipelineName and choose Add as New Query.

THREE

Select this new query from the Query list on the left of PBI Desktop; Transform tab > Any Column group > Convert to List. Rename query to Pipeline List.

FOUR

New Source > Blank Query > Advanced Editor > paste your query above. I name it Stage wise failures - all.

• Make sure to enter the {organization} in this line, and remove the {proejct}:

OData.Feed ("https://analytics.dev.azure.com/{organization}/_odata/v3.0-preview/PipelineRunActivityResults?"

• Enter a date; I am using July 1

&"and PipelineRunCompletedOn/Date ge 2020-07-01Z "

• Update the source to reference the list created in step THREE above.

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

My final version of the query looks like this:

let
   ODataFeed = (PipeLine) => 

        OData.Feed ("https://analytics.dev.azure.com/{organization}/_odata/v3.0-preview/PipelineRunActivityResults?"
        &"$apply=filter( "
                &"Pipeline/PipelineName eq '{"& PipeLine & "}' "
                &"and PipelineRunCompletedOn/Date ge 2020-07-01Z "
                &"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(_) ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"PipelineJob", "PipelineRunCompletedOn", "FailedStageCount"}, {"PipelineJob", "PipelineRunCompletedOn", "FailedStageCount"})
in
    #"Expanded Column1"

This returns the error I noted previously:

Formula.Firewall: Query 'Stage wise failures-All' (step 'Expanded Column1') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

If I try to update the Stage wise failures - all query to reference the Pipelines - Outcome Summary for all query like this

let
   ODataFeed = (PipeLine) => 

        OData.Feed ("https://analytics.dev.azure.com/{organization}/_odata/v3.0-preview/PipelineRunActivityResults?"
        &"$apply=filter( "
                &"Pipeline/PipelineName eq '{"& PipeLine & "}' "
                &"and PipelineRunCompletedOn/Date ge 2020-07-01Z "
                &"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(#"Pipelines - Outcome Summary for all Pipelines", each ODataFeed(_) ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"PipelineJob", "PipelineRunCompletedOn", "FailedStageCount"}, {"PipelineJob", "PipelineRunCompletedOn", "FailedStageCount"})
in
    #"Expanded Column1"

I get an error:

Expression.Error: We cannot convert a value of type Table to type List.
Details:
    Value=[Table]
    Type=[Type]

If I recreate the list to be "static" by manually copying the values and entering them directly into Power Query (call this Pipeline List Static) and then referencing this, I can get "results" but everything is null.

The query I end up with for this is:

let
   ODataFeed = (PipeLine) => 

        OData.Feed ("https://analytics.dev.azure.com/{organization}/_odata/v3.0-preview/PipelineRunActivityResults?"
        &"$apply=filter( "
                &"Pipeline/PipelineName eq '{"& PipeLine & "}' "
                &"and PipelineRunCompletedOn/Date ge 2020-07-01Z "
                &"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 Static", each ODataFeed(_) ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"PipelineJob", "PipelineRunCompletedOn", "FailedStageCount"}, {"PipelineJob", "PipelineRunCompletedOn", "FailedStageCount"})
in
    #"Expanded Column1"

and my results look like this:

[PhilipTreacy]

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]

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.