Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
karimk
Helper III
Helper III

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

1 ACCEPTED SOLUTION

Hi,

You may download my solution file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
v-yanjiang-msft
Community Support
Community Support

Hi @karimk ,

According to your description, here's my solution.

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

vkalyjmsft_0-1644312509343.png

2.Merge Queries like this.

vkalyjmsft_1-1644312576751.png

3.Only expand the NAME column.

vkalyjmsft_2-1644312650042.png

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

vkalyjmsft_3-1644312706976.png

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.

vkalyjmsft_4-1644312952499.png

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
Resolver III
Resolver III

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

jaipal_1-1643771556591.png

 

you will get a new table. 

 

Now, go into the Power BI and do your visuals

jaipal_2-1643771680338.png

 

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.

 

Solution BI.PNG

amitchandak
Super User
Super User

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

 

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

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.

Solution BI.PNG

Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Solution BI.PNG

Hi,

You may download my solution file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.