Forum Discussion

ShreySharma's avatar
ShreySharma
Frequent Visitor
2 years ago

Merge 2 tables with Power BI Desktop

Hi All,

 

I am trying to merge two tables within Power BI; however, I am not getting the correct result.

 

First data set has the total time spent by an agent on a particular activity during the day and the second data set is a breakup of that time spent per application during that period.

 

So, the first table has around 10k rows, and the second table has 60k rows.

 

Ex - Table 1 is:

Employee ID EmployeeActivityDateStart Time  End Time
123JeffCall12-12-202010:00 AM10:15 AM
123JeffBreak12-12-202010:15 AM10:30 AM
234EricCall12-12-202010:15 AM10:30 AM
234EricCall12-12-202010:30 AM11:00 AM
345MichaelCall12-12-202010:00 AM10:45 AM
345MichaelBreak12-12-202010:45 AM11:15 AM

 

Ex – Table 2:

Employee ID EmployeeApplicationDateStart Time  End Time
123JeffChrome12-12-202010:00 AM10:03 AM
123JeffOutlook12-12-202010:04 AM10:09 AM
123JeffTeams12-12-202010:10 AM10:15 AM
123JeffExcel12-12-202010:16 AM10:25 AM
123JeffOutlook12-12-202010:26 AM10:30 AM
234EricOutlook12-12-202010:15 AM10:20 AM
234EricChrome12-12-202010:21 AM10:30 AM
234EricExcel12-12-202010:31 AM10:40 AM
234EricTeams12-12-202010:41 AM11:00 AM
345MichaelChrome12-12-202010:00 AM10:10 AM
345MichaelTeams12-12-202010:11 AM10:20 AM
345MichaelOutlook12-12-202010:21 AM10:30 AM
345MichaelExcel12-12-202010:31 AM10:45 AM
345MichaelChrome12-12-202010:46 AM10:55 AM
345MichaelTeams12-12-202010:56 AM11:05 AM
345MichaelOutlook12-12-202011:06 AM11:15 AM

 

Final Output should look like :

 

Employee ID EmployeeActivityDateStart Time  End TimeEmployee ID EmployeeApplicationDateStart Time  End Time
123JeffCall12-12-202010:00 AM10:15 AM123JeffChrome12-12-202010:00 AM10:03 AM
123JeffCall12-12-202010:00 AM10:15 AM123JeffOutlook12-12-202010:04 AM10:09 AM
123JeffCall12-12-202010:00 AM10:15 AM123JeffTeams12-12-202010:10 AM10:15 AM
123JeffBreak12-12-202010:15 AM10:30 AM123JeffExcel12-12-202010:16 AM10:25 AM
123JeffBreak12-12-202010:15 AM10:30 AM123JeffOutlook12-12-202010:26 AM10:30 AM
234EricCall12-12-202010:15 AM10:30 AM234EricOutlook12-12-202010:15 AM10:20 AM
234EricCall12-12-202010:15 AM10:30 AM234EricChrome12-12-202010:21 AM10:30 AM
234EricCall12-12-202010:30 AM11:00 AM234EricExcel12-12-202010:31 AM10:40 AM
234EricCall12-12-202010:30 AM11:00 AM234EricTeams12-12-202010:41 AM11:00 AM
345MichaelCall12-12-202010:00 AM10:45 AM345MichaelChrome12-12-202010:00 AM10:10 AM
345MichaelCall12-12-202010:00 AM10:45 AM345MichaelTeams12-12-202010:11 AM10:20 AM

Would any of you have any ideas to do it please?

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ShreySharma 

    You can add a custom column in Table 1 in power query

    Table.SelectRows(#"Table 2",(x)=>x[Employee ID]=[Employee ID] and x[Start Time]>=[Start Time] and x[End Time]<=[End Time])

    Then expanded the column 

    Output

    You can also refer to the attachment.

     

    Best Regards!

    Yolo Zhu

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

  • ShreySharma's avatar
    ShreySharma
    Frequent Visitor
    2 years ago

    Thanks for this Anonymous. I used the formula to create a new column, however, when I try and expand the column to get the columns from Table 2, I always get an error "No columns were found"

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi ShreySharma 

      Did your table name is the same as i provided? the step i proveded, its table name is 'Table 2' .

      Table.SelectRows(#"Table 2"（it should be your table name of the second table),(x)=>x[Employee ID]=[Employee ID] and x[Start Time]>=[Start Time] and x[End Time]<=[End Time])

      the  did you add the custom column in table1?  can you provide the step you created, the code can work in my sample, or your colum name is the same as i provided?

       

      Best Regards!

      Yolo Zhu