what is the best way to relate these 2 tables ?

[vidya9924]

Table 1 : 

 and Table 2:

I want to combine both like this 

how to establish relationship between the 2 tables to achieve this ?

[CupidC]

Table =

    VAR Table1 =    GENERATE('Table 1',FILTER('Table 2','Table 2'[kactid]='Table 1'[kactid1]))

    VAR Result =

    SELECTCOLUMNS(Table1,

        "Event ID",[kactid],

        "Attending Worker", [AttendingWorker],

        "Service Unit", [itemname],

        "Kinvlineid",[kactlineid])

    RETURN

    Result

[PijushRoy]

Hi @vidya9924 

Please share sample data in excel format for both table

[vidya9924]

@PijushRoy Table 1 Sample : 

kbookitemid	kactid	slogin	slogmod	AttendingWorker
6241	1	11/05/2019 20:51	11/05/2019 20:51	Titheridge Karen - 1129
6255	5	24/07/2019 16:03	24/07/2019 16:15	Chan Crystal - 1142
25642	5	24/07/2019 16:15	24/07/2019 16:16
6255	7	24/07/2019 16:18	24/07/2019 16:18	Chan Crystal - 1142
6255	13	30/07/2019 10:53	30/07/2019 10:53	Chan Crystal - 1142
6255	16	30/07/2019 13:13	30/07/2019 13:13	Chan Crystal - 1142
6231	16	30/07/2019 13:13	30/07/2019 13:13	Perris Kane - 1120
6191	27	30/07/2019 14:10	30/07/2019 14:10	Kanu Jemila - 1080
25517	27	30/07/2019 14:10	19/09/2019 16:36
40397	819698	25/02/2024 11:56	25/02/2024 11:56
46231	819698	25/02/2024 11:56	25/02/2024 11:56	Levacic Karlo - 1453
42151	819698	25/02/2024 11:56	25/02/2024 11:56	Palmer Tyne - 1388
39466	819698	25/02/2024 11:56	25/02/2024 11:56	Humbler Dion - 1321

Table 2 Sample :

kactlineid	kitemid	itemname	lineqty	linefee	linetotal	kactid
28612	76	Individual Therapy - Face to Face	1.5	193.99	290.99	27
712188	26	GD Service - Non Face to Face	0.75	0	0	819698
712187	23	GD Service - Face to Face	0.75	0	0	819698
712189	27	GD Service - Reports/Notes	0.17	0	0	819698

[v-yifanw-msft]

Hi @vidya9924 ,

Depending on the information you provided, you can refer to the following steps:

1.Merge in Power Query.

2.Expand the table.

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[vidya9924]

Hey, its a very huge table with more than 2 million rows of data so merging consumes a lot of time.