Creating an intermediate table from two existing tables

Question

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 Answer

twowaysyellow&nbsp;add following expression to create the table
&nbsp;
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" 
    )
)
&nbsp;

&nbsp;

Forum Discussion

Creating an intermediate table from two existing tables

1 Reply

Related content

Multiple Intermediate step

Cannot create liquid clustering on existing unpartitioned table

Create Data Pipeline with existing definition using Terraform

Create a new table from columns from existing tables

Creating ICMs via Activator