Merging 2 tables with complementing data

[karimk]

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

[v-yanjiang-msft]

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.

[jaipal]

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]

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.

[amitchandak]

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

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

[karimk]

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.

[Ashish_Mathur]

Hi,

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

[karimk]

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.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution file from here.

Hope this helps.

[karimk]

Thank you very much. Unfortunately, and I should have made it clear, each person is part of a specific department. The way you went about it made each person appear in more than one department.

[Ashish_Mathur]

You are welcome.  You should now be able to apply some filter to get to your end result.  If not, then post back with may be a smaller dataset and your exact expected result.