How to loop through a list in a M query?

Question

I have a query in PowerQuery editor, which brings all projects in an Azure DevOps organization:

let
   Source = OData.Feed("https://analytics.dev.azure.com/someorg/_odata/v3.0-preview/Projects", null, [Implementation="2.0", OmitValues=ODataOmitValues.Nulls, ODataVersion=4]),
    ProjectName = Source[ProjectName]
in
    ProjectName

Also, I have this OData query which brings back all tasks for a single project:

let
   Source = OData.Feed ("https://analytics.dev.azure.com/someorg/{project}/_odata/v3.0-preview/WorkItems?"
        &"$filter=WorkItemType eq 'User Story' "
            &"and State ne 'Closed' "
            &"and startswith(Area/AreaPath,'{areapath}') "
        &"&$select=WorkItemId,Title,WorkItemType,State,Priority,Severity,TagNames,AreaSK "
        &"&$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath), "
                &"Links( "
                    &"$filter=LinkTypeName eq 'Related' "
                        &"and TargetWorkItem/WorkItemType eq 'User Story'; "
                    &"$select=LinkTypeName; "
                    &"$expand=TargetWorkItem($select=WorkItemType,WorkItemId,Title,State) "
                &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

I need to run the second query once per each project in the first list, and append them in a single table, replacing {project} for each element. Is this possible in PowerQuery?

Thanks in advance;

Answer 1

1. Create a custom function where result will be your table

In your case something like:

query name is GetDataForProject

(project) =>
let
   Source = OData.Feed ("https://analytics.dev.azure.com/someorg/{" & project & "}/_odata/v3.0-preview/WorkItems?"
        &"$filter=WorkItemType eq 'User Story' "
            &"and State ne 'Closed' "
            &"and startswith(Area/AreaPath,'{areapath}') "
        &"&$select=WorkItemId,Title,WorkItemType,State,Priority,Severity,TagNames,AreaSK "
        &"&$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath), "
                &"Links( "
                    &"$filter=LinkTypeName eq 'Related' "
                        &"and TargetWorkItem/WorkItemType eq 'User Story'; "
                    &"$select=LinkTypeName; "
                    &"$expand=TargetWorkItem($select=WorkItemType,WorkItemId,Title,State) "
                &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

2. Convert your list into table and add new column where you use your created function, something like:

    let
         Source = Table.FromList(YourListOfProjects)
        ,InvokedFunction = Table.AddColumn(Source, "GetDataForProject", each GetDataForProject([Column1]))
    in
        InvokedFunction
   

3. You can then expand tables in new created column:

In result you get appended tables.