You need an "AllocationTable", that holds the names of the tables and the columns through which they are connected (see in example below).
This example flattens all tables that are connected to what you choose in "SelectedTable" from your local AdventureWorks-DB. Watch out: The "SelectedTable" needs to exist in column "FromTable" from "AllocationTable"!
let
// Parameters
Database = Sql.Databases("localhost", [CreateNavigationProperties=false]){[Name="AdventureWorksDW2012"]}[Data],
AllocationTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZPBboMwEET/hXN+oipNFPWQqukt4mC5q8oSXqNlfSBfH0iwsReIkvS4M/NGsAunU1Ea+2Gb2nUAxaY4qhraHyAy7Kj7hK7X+kQuL+aqzbXrQH8KzVmxcdjn3j0RoJ6agiC8gd4qzVuDCvXwJG9aO48cwXHOnTlWKobIDEOiLaShUcQWkHfkfDOBub6cnNelLx+7xEZkZt5y1ICKjJu2PwrCC+QeGQiBrzd5YedzvmVngRL+JghvjS89PHIFiX2R+/V6Ovg4584d2Lps7VGR7lrF/z79ofEbWqhroFcPIfkHFymx8DtHLvm/U2+Nf+oQC/Czh5AVYY4NQRBeVV0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FromTable = _t, FromName = _t, ToTable = _t, ToColumn = _t]),
SelectedTable = "FactInternetSales",
MainTable = "FromTable",
IDMain = "FromName",
LookupTable = "ToTable",
IDLookup = "ToColumn",
// Filter User Selection
FilterMainTable = Table.SelectRows(AllocationTable, each Record.Field(_, MainTable)=SelectedTable),
// Create row number for iterating
AddIndex = Table.AddIndexColumn(FilterMainTable, "Index", 0, 1),
// Expanded Columns will be prefixed with name of the table
ColumnNames = Table.Buffer(Table.AddColumn(AddIndex, "RenameHeaders", each {Table.ColumnNames(Database{[Name=Record.Field(_, LookupTable)]}[Data]),List.Transform(Table.ColumnNames(Database{[Name=Record.Field(_, LookupTable)]}[Data]), (ListItem)=> Record.Field(_, LookupTable) &"."& ListItem)})),
FlattenedTable =
// We only need the last element of the list created
List.Last(
// Generates a list of nested tabes, looping through the filtered AllocationTable
List.Generate(()=>
// StartValue
[Result = Database{[Name=SelectedTable]}[Data], Counter=-1],
// While-condition
each [Counter] < Table.RowCount(ColumnNames),
// Evaluated Expression: Lookup previous Result with new table and expand columns
each [ Lookup= Table.NestedJoin([Result],{Record.Field(FilterMainTable{Counter}, IDMain)}, Database{[Name=Record.Field(FilterMainTable{Counter}, LookupTable)]}[Data],{Record.Field(FilterMainTable{Counter}, IDLookup)},"LookupTable",JoinKind.LeftOuter),
Result= Table.ExpandTableColumn(Lookup, "LookupTable", Record.Field(ColumnNames{Counter}, "RenameHeaders"){0},Record.Field(ColumnNames{Counter}, "RenameHeaders"){1}),
Counter = [Counter]+1
]
))[Result]
in
FlattenedTable
