Creating an intermediate table from two existing tables

[twowaysyellow]

I want to create an intermediate table based on the relationship between two existing tables.

I have two existing tables; Tickets and Events.

Table: Tickets

ticket_id	guest_id	event_id
1	1	1
2	2	2

Table: Events

event_id	host_id
1	10
2	10

I want to create a table called TicketsToUser that ends up being like this:

ticket_id	guest_id	user_id	event_id
1	guest	1	1
1	host	10	1
2	guest	2	2
2	host	10	2

Thanks in advance.

[parry2k · Accepted solution]

@twowaysyellow add following expression to create the table

Table = 
VAR __Table = 
SELECTCOLUMNS ( Tickets, "Ticket_Id", Tickets[ticket_id],  "User_Id", Tickets[guest_id], "Event_Id", Tickets[event_id], "Host_Id", RELATED ( Events[host_id] ) )
RETURN
UNION (
    ADDCOLUMNS ( 
        SELECTCOLUMNS (
            __Table,
            [Ticket_Id], [User_Id], [Event_ID]
        ), "Guest_Id", "Guest" 
    ),
    ADDCOLUMNS ( 
        SELECTCOLUMNS (
            __Table,
            [Ticket_Id], [Host_Id], [Event_ID]
        ), "Guest_Id", "Host" 
    )
)