Power BI merge one column from one table to another

[bombom]

Hello!

I have two tables. They are joined together by TicketId column already.

Table 1.

TicketId	StartTimeOfLoading	EndTimeOfLoading
133580	15.11.2022 12:16:59	15.11.2022 12:25:37

Table 2.

CreatedAt	TicketId	Step	Result
14.11.2022 13:02:56	133580	2	Successfull
14.11.2022 13:05:15	133580	4	Successfull
14.11.2022 13:05:29	133580	8	Error
14.11.2022 13:11:23	133580	2	Successfull
14.11.2022 13:13:38	133580	4	Successfull
14.11.2022 13:13:52	133580	8	Error
14.11.2022 13:24:03	133580	2	Successfull
14.11.2022 13:26:21	133580	4	Successfull
14.11.2022 13:26:35	133580	8	Error
15.11.2022 12:16:58	133580	2	Successfull
15.11.2022 12:19:28	133580	4	Successfull
15.11.2022 12:25:36	133580	5	Successfull
15.11.2022 12:25:37	133580	6	Successfull
15.11.2022 12:25:37	133580	7	Successfull

StartTimeOfLoading column from Table 1 is the date from Table 2 with the Step = 2 and maximum date from the CreatedAt column. The same for EndTimeOfLoading column, but it's Step = 7.

I need to merge Steps 4, 5, 6 with max date condition on column from Table 2 to Table 1.

TicketId	StartTimeOfLoading	EndTimeOfLoading	4	5	6
133580	15.11.2022 12:16:59	15.11.2022 12:25:37	15.11.2022 12:19:28	15.11.2022 12:25:36	15.11.2022 12:25:37

[v-yangliu-msft · Accepted solution]

Hi  @bombom ,

Here are the steps you can follow：

1. Create calculated column.

4 =
MAXX(FILTER(ALL(Table2),
'Table2'[Step]=4&&'Table2'[TicketId]=EARLIER('Table1'[TicketId])),[CreatedAt])

5 =
MAXX(FILTER(ALL(Table2),
'Table2'[Step]=5&&'Table2'[TicketId]=EARLIER('Table1'[TicketId])),[CreatedAt])

6 =
MAXX(FILTER(ALL(Table2),
'Table2'[Step]=6&&'Table2'[TicketId]=EARLIER('Table1'[TicketId])),[CreatedAt])

2. Result:

Best Regards,

Liu Yang

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