Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Conditional merging two tables with New rows

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 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, Anonymous 

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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