Forum Discussion

wodraeve's avatar
wodraeve
Frequent Visitor
9 years ago
Solved

Nested join / join multiple tables in Power Query

Hello,   I have the following situation in Power Query. A fact table that has foreign keys to multiple dimensions.   eg.   ID_Fact ID_FK1 ID_FK2 value 1 2 3 4 1 FK1_1 FK2_1 100 ...
Need Help
  • wodraeve's avatar
    wodraeve
    9 years ago

    Thanks for the reply, I'm happy to say I managed to obtain the result I needed, using Evaluate.Expression to retrieve the tables from the text names of the tables linked...

     

     

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

wodraeve

 

No, it's not possible to dynamically nested join tables. Even we can substring the values from FK columns, it's still not supported to get a table or list with its name in Power Query. So for your requirement, it can't be achieved with recursive function.

 

Regards,

  • wodraeve's avatar
    wodraeve
    Frequent Visitor
    9 years ago

    Thanks for the reply, I'm happy to say I managed to obtain the result I needed, using Evaluate.Expression to retrieve the tables from the text names of the tables linked...

     

     

    • v-sihou-msft's avatar
      v-sihou-msft
      Microsoft Employee
      9 years ago

      wodraeve

       

      Can you share your solution if possible?

       

      Regards,

      • wodraeve's avatar
        wodraeve
        Frequent Visitor
        9 years ago

         

         

         

         

         

         

         

         

        See code above. Basically I'm using a classic recursive function looping until a condition (in this case for this table no columns are listed as being a foreign key) is met.

        For the basetable I get the list of foreign keys from a external table. For every foreign key I join with the table that has the name associated with that foreign key (hence the Expression.Evaluate).

         

        It probably can go faster / cleaner using List.Generate but will try that one later.