Make New Table from columns of existing tables that have relationship

[NittanyBlu]

I'd like to make a new table based on the existings tables I currently have (which have relationships). How do I do this?

I have three current table... examples:

Table 1. Employee Information

Employee Name	User ID
Pete	ID1234
Kim	ID4321

      * UserID field from Employee Information Table has a 1-to-many relationship with the UserID field of Employee Assignment Table

Table 2. Employee Assignments

UserID	Assignment Code
ID1234	A001
ID4321	A001
ID4321	A002

     * Assignment Code field from the Employee Assignments table has many-to-many relationships with the Assignment code field from the Assignment to Customer Relationships table (Employee Assignment filters - Assigment to Customer Relationship).

Table 3. Assignment to Customer Relationships

Assignment Code	Customer Name
A001	Bob's Tackle
A001	Lisa's Cafe
A002	Joe's Garage
A002	Jack's Painting

My goal is to create a forth table based on the current tables and relationships. This new table would look like the following:

Employee Name	Customer Name
Pete	Bob's Tackle
Pete	Lisa's Cafe
Kim	Bob's Tackle
Kim	Lisa's Cafe
Kim	Joe's Garage
Kim	Jack's Painting

[v-lionel-msft]

Hi @NittanyBlu ,

let
    Source = Table.NestedJoin(Table, {"User ID"}, #"Table (2)", {"UserID"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(Source, "Table (2)", {"UserID", "Assignment Code"}, {"Table (2).UserID", "Table (2).Assignment Code"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Table (2)", {"Table (2).Assignment Code"}, #"Table (3)", {"Assignment Code"}, "Table (3)", JoinKind.LeftOuter),
    #"Expanded Table (3)" = Table.ExpandTableColumn(#"Merged Queries", "Table (3)", {"Assignment Code", "Customer Name"}, {"Table (3).Assignment Code", "Table (3).Customer Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table (3)",{"User ID", "Table (2).UserID", "Table (2).Assignment Code", "Table (3).Assignment Code"})
in
    #"Removed Columns"

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, merge table3 into table2 and then table2 into table1.