Conditional merging two tables with New rows

[Amardeep100115]

Hi All, 

i wish to perform conditional merging on one or two columns, i have give samle data in which i Table 1 and table 2 there and also Output result . 

i want to merge two table on basis of ID Column and get a count of Emp. if employee has not used assigned ID then that merging row would show zero and next row it should show which id he has used and number of time  he used 

Sample data 

Please help me with same in power query (M-code) or DAX  but mostly M-code Please 

Please share same in Power BI file. or in attached excel sheet also work for me 

[v-zhangti]

Hi, @Amardeep100115 

You can try the following methods. You need to add an index column to Power Query first.

Dax New Table:

Table = 
Var _Table1=SUMMARIZE(Table1,Table1[ID],Table1[Emp Name],Table1[Index],"Type","Emp")
Var _Table2=SUMMARIZE(Table2,Table2[ID],Table2[Emp Name],Table2[Index],"Type","Guest")
Return
UNION(_Table1,_Table2)

Column:

Emp Name2 = CALCULATE(MAX(Table1[Emp Name]),FILTER(Table1,[ID]=EARLIER('Table'[ID])))

Can you explain the logic of the other 2 columns?

Best Regards,

Community Support Team _Charlotte

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

[Amardeep100115]

Hi @v-zhangti 

Please find the data that i am using, Please help me with merging table with Green tab data. Merge Visit data with Custmoer data and try to achive sample result data.  Please help me with m-code. 

Please let me know if you have any queirs 
New Sample data