Tips on speeding up Table.NestedJoin

Hello jasonyeung87 ,

I work with DevOps data quite a bit and encountered this challenge in the past. When working with joins in Power Query against Azure DevOps OData, I find that the shape of the data and the query design tends to have a greater impact on performance rather than specific join functions. I'm including a few different sections in my response so I can address the specific question asked and also provide solution options, since the two are not really the same thing in this scenario.

General best practices (foldable and non-foldable sources)

• When possible, use join keys that are non-calculated/non-concatenated scalar values like integers, text or dates, making sure that the data types match on both sides of the join.
• Limit the volume of data early by only querying the data needed and/or filtering rows AND selecting only the columns that are necessary for the join and downstream steps.
• When possible, using complex M logic, Table.Buffer, etc. prior to the join.
• After the join, delay expanding the resulting joined columns until after additional filtering (if needed) and then, only expanding the columns you actually need. 

Avoid joins altogether if a single entity or pre-shaped view will meet your requirement.

Recommendations for DevOps queries:
Model DevOps analytics views or OData queries to return the shape you need, or as close to it as possible.

• Use Analytics Views for standard reporting, reuse, and performance stability.
  Analytics Views are a curated, server‑side way to shape Azure DevOps data for Power BI. They let you select specific fields, teams/area paths, filters and have several historical data options. You can then connect to them using the Azure DevOps connector. This is usually the best option when you want stable schemas, predictable performance, and minimal Power Query logic. These solve for many data scenarios but not all of them. You can design the analytics views to include any project you have access to that is in the same organization.  You have to create a separate analytics view for each organization.  I frequently use these for work item lists of all kinds.  https://learn.microsoft.com/azure/devops/report/analytics/analytics-views-create 
• Use OData queries for advanced filtering, custom aggregations, or when Analytics Views don’t expose the shape you need.
  Use OData query options such as $filter, $select, and $apply so Azure DevOps performs filtering and aggregation server‑side which reduces the payload size and refresh time before Power Query needs to evaluate joins.  I frequently use these for calculations like velocity and returning dimensions like users.  https://learn.microsoft.com/azure/devops/report/powerbi/odataquery-connect

Analytics views simplify a lot of the work.  This is a query to return an analytics view which is saved in my favorites folder in DevOps.

Be sure to replace the variables with your actual values: {org name}, {project name}, {team name}. 

let
    Source = VSTS.AnalyticsViews("{org name}", "{project name}", []),
    Favorites_Folder = Source{[Id="Favorites",Kind="Folder"]}[Data],
    data = Favorites_Folder{[Id="eecf4346-71bf-4f93-815d-b663fd0c193c",Kind="Table"]}[Data]
in
    data

OData query for velocity by team based on stories:

Be sure to replace the variables with your actual values: {org name}, {project name}, {team name}.  I have also included lines that are commented which show you how to apply other types of filters and aggregations if needed.  I needed to show the velocity for each team separately which is why you see two separate queries (team_data1 and team_data2) which are then appended together.

let
    team_data1 = OData.Feed ( "https://analytics.dev.azure.com/{org name}/{project name}/_odata/v3.0-preview/Iterations?"
        &"$apply=filter( "
            &"Teams/any(t:t/TeamName eq '{team name}') "
            &"and StartDate lt now() "
//            &"and BoardName eq 'Stories'  "
// ------------------------------------------------------------------------
// Examples of additional filters if needed
//            &"and startswith(Area/AreaPath,'{team name}\{area path}') "
//            &"and DateValue ge Iteration/StartDate "
//            &"and DateValue le Iteration/EndDate "
//            &"and Iteration/StartDate le now()  "
//            &"and Iteration/EndDate ge now() "
//            &"and BoardName eq 'Stories'  "
//            &"and DateValue ge 2023-01-01Z "
//        &") "
//        &"/groupby( "
//            &"(DateValue,ColumnName,LaneName,State,WorkItemType,AssignedTo/UserName,Area/AreaPath),  "
//            &"aggregate($count as Count) "
//        &") "
// ------------------------------------------------------------------------
        &") &$orderby=IsEnded,StartDate desc,EndDate desc,IterationName "
        &"&$select=IterationSK,IterationName,StartDate,EndDate,IsEnded,IterationPath "
        &"&$top=100 ",
        null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4] ),
// repeat for every team
     team_data2 = OData.Feed ( "https://analytics.dev.azure.com/{org name}/{project name}/_odata/v3.0-preview/Iterations?"
        &"$apply=filter( "
            &"Teams/any(t:t/TeamName eq '{team name}') "
            &"and StartDate lt now() "
        &") &$orderby=IsEnded,StartDate desc,EndDate desc,IterationName "
        &"&$select=IterationSK,IterationName,StartDate,EndDate,IsEnded,IterationPath "
        &"&$top=100 ",
        null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4] ), 
Result = Table.Combine ( { team_data1, team_data2 } ),
    #"Extracted Date" = Table.TransformColumns(Result,{{"StartDate", DateTime.Date, type date}, {"EndDate", DateTime.Date, type date}}),
    #"Added Custom" = Table.AddColumn(#"Extracted Date", "DateRange", each Date.ToText ( [StartDate], "MM.dd.yy" ) & " - " & Date.ToText ( [EndDate], "MM.dd.yy" ), type text),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Added Custom", "Project", each Text.BeforeDelimiter([IterationPath], "\"), type text),
    #"Added Custom1" = Table.AddColumn(#"Inserted Text Before Delimiter", "ProjectIterationStartDate", each [Project] & "_" & Date.ToText ( [StartDate], "yyyyMMdd" ), type text)
in
    #"Added Custom1"

OData query to return Users as a dimension:

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{org name}/{project name}/_odata/v4.0-preview/Users?"
&"$count=true&$select=UserName,UserEmail"
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
    Source

If you must join locally, do so on as little data as possible.

• Reduce rows and columns at the source.
• Filter and select columns on both sides before the join.
• Use Table.NestedJoin for selective expansion.
• Use Table.Join with an explicit algorithm only when you know the join is non‑folding and one side is clearly smaller.

Please let me know if you have questions about this info.

If this post helps to answer your questions, please consider giving it a kudo and/or marking it as a solution so others can find it more quickly when faced with a similar challenge.

Proud to be a Microsoft Fabric Super User