Would someone be able to provide me with confirmation on the following when using the 'Select Related Tables' option in the Navigator screen when getting data:
1) If I had the table tbl_Customer that had relationships with tblAddress and tblEmployment, and in turn tblAddress joined to other tables, and likewise for tblEmployment - and I chose tblCustomer ONLY in the navigator pane and then clicked 'Select Related Tables' would:
a) ONLY the tblAddress and tblEmployment be returned along with tblCustomer?
b) tblAddress and all its joined tables, and tblEmployment and all its joined tables, and the joined tables joined tables, etc... be returned with the tblCustomer?
2) If I have 3 tables (lets say tblWage, tblService, tblCatering) and I choose those tables with the navigator pane - is there a way for Power BI to auto select the tables needed to join tblWage, tblService, and tblCatering together (how many there may be), and no other/additional joined tables?
Through further experimenting, it seems that the 'Select Related Tables' option brings in tables directed related to the chosen (originally selected) tables only. If tblCustomer has relationships with tblAddress and tblOrders, the 'Select Related Tables' option will only bring in tblCustomer and tblOrders. It will not bring in tables that have relationships with tblAddress and tblOrders.
To put it another way, lets say this is the set-up.
tblA joins to tblB, which joins to tblC, which joins to tblD, which joins to tblE.
tblA >> tblB >> tblC >> tblD >> tblE
If you chose tblA and tblE in the Get Data Navigator and check the 'Select Related Tables' box tblA, tblB, tblD, and tblE will be returned. The linking table tblC will not be returned. So you cannot rely on 'Select Related Tables' to return all the required tables even though the database you are connecting to has all the relationtionships mapped for Power BI to be able to see. Ultimately you will be required to do some manual work (bringing in the linking tables and joining them).