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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Sharkybu
Helper I
Helper I

Comparing info between rows and columns

 

Hello,

I have a very complicated table that I'm trying to sort -find errors.

I've done multiple attempts to do this, and I always feel like I'm doing this too complicated. Plus I built it to find problems I saw, I don’t know if there are others.

So, I'm asking for help.

This is a sample of one person out of over a 1000. The number of contracts and locations can vary.

 

ID

date

Status

Contract

Located

1

30/06/2003

open

B

There

1

01/10/2009

Close

A

There

1

01/10/2020

open

B

Here

1

01/10/2009

close

A

Here

 

No matter how many locations a person has the contracts are always equal between them.

Mistakes in this example alone:

Located "there"- contract B start date is before contract A

And Contract B has different dates in "Here" and "There".

 

I have over 1000 people, over 100 different units and 10+ contracts.

My latest attempt was to compare rows by ID (creating 16 comparisons in this case)

And creating different "if" columns.

 

The original data is an excel file I get sent periodically. And the problem shown here is new even though the last change for this person was done in 2020.

 

I have so many other problems with the data, but this is the one I'm finding the hardest to tackle.

 

If what I want is not possible and or doesn't make sense, then I'm sorry.

Thank you.

2 REPLIES 2
Sharkybu
Helper I
Helper I

The expected output is the same file with the errors marked. So I can send it back and ask them to fix it.

types of error that can happen are:

A person can be connected to either one or multiple locations but they always have the same contracts.

Only one contract can be in open status at a time, and it has to be open in all locations.

A contract can only appear once per location(I have a case that someon has contract A twice- once open, once close)

The date for a contract is the same in all locations.

an open contract can't have an earlier date then a closed one. 

"null" values are an error.

Contracts have order- Contract B can only happen when contract A closes, not the other way around. 

That's all that I can think of now.

 

What do you mean by " What are we doing to identify what is an error?"

 

Thank you

 

 

 

 

 

spinfuzer
Super User
Super User

What is your expected output?  What are we doing to identify what is an error?  What are all the types of errors that could occur?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors