How to merge the results from two tables and flatten them for each row from the second?

[WorkHard]

What is the easiest way to flatten two tables and merge them into one while retaining all the rows from the first.

For example:

I have a Projects table like this:

Project Table	Project ID	...Other fields
Project 1	1	data
Project 2	2	data
Project 3	3	data

And an employee table like this:

Employee Table	Assigned to Project ID	Employee Role
John	1	Tech
John	2	Tech
Andy	2	Tech
John	1	Manager
Andy	2	Manager
Mike	2	Manager

How do I merge the two and create a row for each employee but also retain the rows from the Projects table that do not have a match in the employee table: (bonus if I can also retain the Employees that are not in the Project List). I know, the data is a mess.

Results table	Project ID	Employee Name	Employee Role	...Other Fields
Project 1	1	John	Tech	data
Project 1	1	John	Manager	data
Project 2	2	John	Tech	data
Project 2	2	Andy	Tech	data
Project 2	2	Andy	Manger	data
Project 2	2	Mike	Manager	data
Project 3	3

[ryan_mayu]

@WorkHard 

maybe you can try merge in pq

expand columns

[WorkHard]

Hi @ryan_mayu , sorry, this table is a calculated UNION table and I can't access pq. Can this be done using DAX?

[ryan_mayu · Accepted solution]

@WorkHard 

here is a DAX workaround for you

Table = 
VAR pro=DISTINCT('project'[Project ID])
VAR emp=DISTINCT(employee[Assigned to Project ID])
VAR tbl=EXCEPT(pro,emp)
VAR tbl2= FILTER(project,project[Project ID]=tbl)
VAR tbl3=ADDCOLUMNS( tbl2,"Emplyee Name",blank(),"Other Field",blank())
VAR tbl4 =SELECTCOLUMNS( FILTER( ADDCOLUMNS( CROSSJOIN(project,employee),"check",if(project[Project ID]=employee[Assigned to Project ID],1,0)),[check]=1),"Project Table",project[Project Table],"Project ID",project[Project ID],"Employee Name",employee[Employee Table],"Employee Role",employee[Employee Role],"other Fields",project[...Other fields])
return UNION(tbl3,tbl4)