Forum Discussion

karimk's avatar
karimk
Helper III
4 years ago
Solved

Merging 2 tables with complementing data

Hi. I have an Excel file with 2 sheets:

1) List of people and the courses they have taken, as well as the department they belong to in our company;

2) List of departments and the courses available for each. 

 

I need to get to a table, either as a visual, or as a dataset, that shows the courses each person is entitled to, based on sheet #2, and the ones they actually took, based on sheet #1. The excel file is in the following link:

https://1drv.ms/x/s!AkGswA2psTvoh-4iVk9j7v_K1dhHwg?e=MW9FeI

 

Can someone help?

 

Thanks,

Karim

Need Help

10 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    Please create another tab in the same Excel file and show the expected result on that tab.

    • karimk's avatar
      karimk
      Helper III
      4 years ago

      I added a sheet, as requested. Below is a screenshot. I want to be able to see the courses taken, the ones that were not taken and if those are even offered, per person.

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    karimk , Create a column department - course in both table and join then to analyze

     

    key = [department] & "-" & [course]

    • karimk's avatar
      karimk
      Helper III
      4 years ago

      Hi. Not sure if it worked. I added a sheet to show the expected result. Below is a screenshot. I want to be able to see the courses taken, the ones that were not taken and if those are even offered, per person.

  • jaipal's avatar
    jaipal
    Resolver III
    4 years ago

    Hi karimk , go into power query and merge two tables this this

     

    you will get a new table. 

     

    Now, go into the Power BI and do your visuals

     

    • karimk's avatar
      karimk
      Helper III
      4 years ago

      Hi. Not sure if it worked. I added a sheet to show the expected result. Below is a screenshot. I want to be able to see the courses taken, the ones that were not taken and if those are even offered, per person.

       

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi karimk ,

    According to your description, here's my solution.

    1.In Power Query, create a duplicate table of Course available.

    2.Merge Queries like this.

    3.Only expand the NAME column.

    4.Select all columns at the same time, then remove duplicate rows.

    5.Create a calculated column in the new table.

    TAKEN？ = 
IF (
    MAXX (
        FILTER (
            ALL ( 'Courses taken' ),
            'Courses taken'[NAME] = EARLIER ( 'Courses available (2)'[Courses taken.NAME] )
                && 'Courses taken'[MODULE TAKEN] = EARLIER ( 'Courses available (2)'[MODULES] )
        ),
        'Courses taken'[DEPARTMENT]
    )
        = 'Courses available (2)'[DEPARTMENT],
    "Yes",
    "No"
)

    6.Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

     

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