Help with Tree Traversal in Power Query

Hi isg,

Hope you're doing well!

I had to solve this problem before.I recommend you to Use List.Generate to iteratively expand the set of matched PKs by checking which rows have a ForeignKey that's already in your matched set. Stop when no new rows are added (i.e., the count stabilizes).

So, paste this as a new blank query (M):

let

    // Your source tables — adjust names as needed

    Source = YourDataTable,

    InputKeys = YourInputTable, // single column: [SearchPrimaryKey]

    // Convert input to a list

    SeedKeys = List.Transform(InputKeys[SearchPrimaryKey], Text.From),

    // Iteratively expand the key set

    ExpandedKeys = List.Last(

        List.Generate(

            () => SeedKeys,

            (current) => 

                List.Count(

                    List.Union({

                        current,

                        List.Transform(

                            Table.SelectRows(

                                Source, 

                                each List.Contains(current, Text.From([ForeignKey]))

                            )[PrimaryKey],

                            Text.From

                        )

                    })

                ) > List.Count(current),

            (current) =>

                List.Union({

                    current,

                    List.Transform(

                        Table.SelectRows(

                            Source, 

                            each List.Contains(current, Text.From([ForeignKey]))

                        )[PrimaryKey],

                        Text.From

                    )

                })

        )

    ),

    // Filter source to only matched rows

    Result = Table.SelectRows(Source, each List.Contains(ExpandedKeys, Text.From([PrimaryKey])))

in

    Result

For large datasets, this can be slow since each iteration re-scans the full table. If performance is a concern, converting the source to a list of records before the loop will help significantly.

Assisted by AI for clarity of wording.

Hope this helps! Don't forget to accept as solution ✅ and like it 👍 in order to keep helping others.

Best regards,

Oussama (Data Consultant - Expert Fabric & Power BI)

Hi

Another solution

let
Source = YourSource,
Keys = Record.FromList(Source[ForeignKey], Source[PrimaryKey]),
Search = {"0001","0009","0015"},
Test = (Actual) as logical=> 
  if Actual = null then false 
  else if List.Contains(Search, Actual) then true 
  else @Test(Record.FieldOrDefault(Keys, Actual, null)),
Result = Table.SelectRows(Source, each Test([PrimaryKey]))
in
Result

Stéphane