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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Lotam
Helper I
Helper I

Check errors between 2 excel files

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

 ParticipantParticipantParticipantParticipant
N° ProjectAlexMariePierreSophie
Project 1YESNOYESNO
Project 2NOYESNOYES
Project 3YESNOYESNO
Project 4NOYESNOYES
Project 5YESNOYESNO
Project 6NOYESNOYES

 

Alex Excel

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

 

Alex Excel

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

 

Marie Excel

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

 

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

Best regards,

Lotam

 

 

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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.

Check errors between 2 excel files.JPG

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.

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

View solution in original post

2 REPLIES 2
Lotam
Helper I
Helper I

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
Community Support
Community Support

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.

Check errors between 2 excel files.JPG

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.

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.