Loop through OData Feed Query

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: