Check errors between 2 excel files

[Lotam]

Greetings !

I have made a PowerBI with a core file Excel with numerous projects.

For approx 50 managers, each one of them receive an excel file that they complete with charges for each projects.
Some of them will modify the projects in their file (adding, getting rid of) while my core Excel file will not be modified at the same time.

For example in the files bellow, lets say I have a corefile with 50 names (of managers) and for each project, there is a YES indicating that they participate in it and a NO if they don't.

In the "Marie Excel" or "Alex Excel", in red appears the mismatch with my core file : marie and alex added a project while in my core file it was not notified as YES.

It could also be the contrary, they deleted a project in their file but it was not modified in the core file.

I would like to have a table listing the n°projects with a mismatch so I can do a checkup.

How could I configure it in PowerBI?

I tought about this calculated column as seen in another post, but it seems it does not work, as I have two other sources even if I have the correct relationships? :

Match = 
var _cname = 'Table'[Customer Name]
var _iname = 'Table'[Invoice Customer]
var _firstname = LEFT(_cname,SEARCH(" ",_cname)-1)
var _lastname = MID(_cname,SEARCH(" ",_cname)+1,LEN(_cname))
return
IF(
    EXACT([Customer Name],[Invoice Customer]),
    "Match",
    IF(
           NOT(CONTAINSSTRING(_iname,_firstname))&&NOT(CONTAINSSTRING(_iname,_lastname)),
           "Not Match",
           IF(
               CONTAINSSTRING(_iname,_firstname)||CONTAINSSTRING(_iname,_lastname),
               "Partial Match"
           )
    )
)

Bellow is the link to the 3 excels files used as examples.

https://www.swisstransfer.com/d/a19a25c4-4e60-4ca9-9bdc-77d9dd0305cehttps://www.swisstransfer.com/d/...

or like this :

PowerBI Sourcedata

	Participant	Participant	Participant	Participant
N° Project	Alex	Marie	Pierre	Sophie
Project 1	YES	NO	YES	NO
Project 2	NO	YES	NO	YES
Project 3	YES	NO	YES	NO
Project 4	NO	YES	NO	YES
Project 5	YES	NO	YES	NO
Project 6	NO	YES	NO	YES

Alex Excel

N°Project	Alex
Project 1	YES
Project 3	YES
Project 4	NO
Project 5	YES

Alex Excel

N°Project	Alex
Project 1	YES
Project 3	YES
Project 4	NO
Project 5	YES

Marie Excel

N°Project	Marie
Project 2	YES
Project 4	YES
Project 5	NO
Project 6	YES

Thank you for your help. I will update this post if in my continuing efforts I find a solution.

Best regards,

Lotam

[Lotam]

Good morning from Paris !

I am deeply sorry to answer so late.. I suffered from an excessive amount of work lately.

Thank you for your time and of course for your solution ! This is what I was looking for as it matches perfectly my needs. Thank you again, you make it seem so simple and elegant.

I wish you a very good day !
Best regards,
Lotam

[v-yiruan-msft · Accepted solution]

Hi @Lotam ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. If no, please provide some sample data with text format and your desired result with backend logic. Thank you.

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.