Forum Discussion

karimk's avatar
karimk
Icon for Helper III rankHelper 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 ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    You may download my solution file from here.

    Hope this helps.

v-yanjiang-msft's avatar
v-yanjiang-msft
Icon for Community Support rankCommunity 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.