@WolfBiber isn't Power Query the part before the datamodel?
Below 2 queries and a video to show how it looks like. Tables are joined and expanded.
Remark: the tables that are joined, must have different column names (except for "PrimaryKey").
Should you have further requirements, please provide a link to a file with representative test data.
Query Start:
let
Source = #table(type table[TableType = number,TableColumn = table,joinKind = number],
{{1,Table1,null},
{1,Table2,JoinKind.Inner},
{2,Table3,null},
{2,Table4,JoinKind.FullOuter},
{2,Table5,JoinKind.FullOuter},
{3,Table6,null},
{3,Table7,JoinKind.FullOuter}})
in
Source
Query Result:
let
Source = Table.Buffer(Start),
JoinTables = (Table as table) as table =>
let
RemovedTableType = Table.RemoveColumns(Table,{"TableType"}),
TableRows = Table.ToRows(RemovedTableType),
JoinTables = List.Accumulate(List.Skip(TableRows),TableRows{0}{0},(jt,t) =>
let
Joined = Table.NestedJoin(jt,{"PrimaryKey"},t{0},{"PrimaryKey"},"JoinedTableColumn",t{1}),
ColumnNames = List.Difference(Table.ColumnNames(t{0}),{"PrimaryKey"}),
Expanded = Table.ExpandTableColumn(Joined,"JoinedTableColumn",ColumnNames)
in
Expanded)
in
JoinTables,
#"Grouped Rows" = Table.Group(Source, {"TableType"}, {{"AllRows", JoinTables, type table}})
in
#"Grouped Rows"
Video:
Specializing in Power Query Formula Language (M)
