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
Gari
New Member

Logic of a rows' conditional flag that is dependent on other rows.

Hello all,

 

I am new to this community. Let’s see if I can explain the things that I wanted to accomplish with your help, using Power Query.

 

My sample dataset looks like this (Currently displaying data of few Patients, for ease of understanding but the dataset contains multiple Patients):

PatientKey

OrganKey

OrganName

DispositionCodeDetail

344315

2

Right Kidney

530 Organ Transplanted in the U.S.

344315

3

Left Kidney

530 Organ Transplanted in the U.S.

344315

5

Pancreas

218 RULED OUT AFTER EVALUATION IN OR

344315

10

Liver

218 RULED OUT AFTER EVALUATION IN OR

344315

14

Intestine

216 ORGAN REFUSED BY ALL NATIONAL PROGRAM

344315

17

Heart

530 Organ Transplanted in the U.S.

344315

19

Right Lung

null

344315

20

Left Lung

null

344315

21

Double Lung

530 Organ Transplanted in the U.S.

344782

2

Right Kidney

530 Organ Transplanted in the U.S.

344782

3

Left Kidney

531 Recovered for Transplant: Discarded

344782

5

Pancreas

216 ORGAN REFUSED BY ALL NATIONAL PROGRAM

344782

10

Liver

216 ORGAN REFUSED BY ALL NATIONAL PROGRAM

344782

14

Intestine

205 DISEASED ORGAN

344782

17

Heart

220 EJECTION FRACTION < 50%

344782

19

Right Lung

null

344782

20

Left Lung

null

344782

21

Double Lung

216 ORGAN REFUSED BY ALL NATIONAL PROGRAM

346651

2

Right Kidney

null

346651

3

Left Kidney

null

346651

5

Pancreas

null

346651

10

Liver

null

346651

14

Intestine

null

346651

17

Heart

null

346651

19

Right Lung

null

346651

20

Left Lung

null

 

The snapshot of the data describes the outcome of all the Diseased Donor patient’s organs, while our organization tries to facilitate transplants for the ones in need.

 

A bit more about the data – A person can have only 8 transplantable organs (Right Kidney, Left Kidney, Pancreas, Liver, Intestine, Heart, Right Lung, Left Lung) but in the above data set, sometimes 9th organ (Double Lung) shows up with data in “DispositionCodeDetail” field. But in reality, this Double Lung is a combination of both the Left Lung & Right Lung recovered together.

 

I would like to see if we could identify the patients who have Double Lungs in the “OrganName” field and mark those patients’ individual Left Lung & Right Lung marked with a Delete Flag as no data in the “DispositionCodeDetail” field.

 

By the way, we couldn’t filter the records with “DispositionCodeDetails” to exclude the null, because, it would entirely eliminate that patient’s information. So, that isn’t a possibility.

 

The expected dataset should look like below

PatientKey

OrganKey

OrganName

DispositionCodeDetail

DeleteFlag

344315

2

Right Kidney

530 Organ Transplanted in the U.S.

0

344315

3

Left Kidney

530 Organ Transplanted in the U.S.

0

344315

5

Pancreas

218 RULED OUT AFTER EVALUATION IN OR

0

344315

10

Liver

218 RULED OUT AFTER EVALUATION IN OR

0

344315

14

Intestine

216 ORGAN REFUSED BY ALL NATIONAL PROGRAM

0

344315

17

Heart

530 Organ Transplanted in the U.S.

0

344315

19

Right Lung

null

1

344315

20

Left Lung

null

1

344315

21

Double Lung

530 Organ Transplanted in the U.S.

0

344782

2

Right Kidney

530 Organ Transplanted in the U.S.

0

344782

3

Left Kidney

531 Recovered for Transplant: Discarded

0

344782

5

Pancreas

216 ORGAN REFUSED BY ALL NATIONAL PROGRAM

0

344782

10

Liver

216 ORGAN REFUSED BY ALL NATIONAL PROGRAM

0

344782

14

Intestine

205 DISEASED ORGAN

0

344782

17

Heart

220 EJECTION FRACTION < 50%

0

344782

19

Right Lung

null

1

344782

20

Left Lung

null

1

344782

21

Double Lung

216 ORGAN REFUSED BY ALL NATIONAL PROGRAM

0

346651

2

Right Kidney

null

0

346651

3

Left Kidney

null

0

346651

5

Pancreas

null

0

346651

10

Liver

null

0

346651

14

Intestine

null

0

346651

17

Heart

null

0

346651

19

Right Lung

null

0

346651

20

Left Lung

null

0

 

 

In essence, whenever “OrganKey” 21 shows up for a patient with data in “OrganDispositionCodeDetail”, that patient’s corresponding records with “OrganKey” 19 & 20 should be marked for deletion.

 

What should be the logic/syntax for creating the “DeleteFlag” column? The logic needs to check every record of a patient to evaluate whether a Double Lung is available or not and go back to the records that have Left & Right Lungs and mark them for deletion.

 

Please let me know if you need any other information.

 

Thanks!

Gari.

6 REPLIES 6
Gari
New Member

Thank You @JoaoMarcelino,

 

Really appreciate you giving it a shot. Here is the link to a file: Organ Disposition requested. This is very little data. It can get too confusing with the bigger dataset. FYI: I have only explained the scenario of Double lung in my original request but it can happen with other organs as well. 

 

Just a thought:
Not sure if using max on OrganKey would be ideal. Fortunately, the double lung's key happens to be 21 being the max of all "OrganKey" here but it may not be the case with other organ combinations.

 

Thanks!

Gari

Any Success @JoaoMarcelino

Hi @Gari 🙂

I'm sorry for not providing any further solution attempts meanwhile 😞
This amazing Community relies on volunteers, and as such, not always we find the time needed to explore solutions after our regular workday.
I've made several different tests, but still have not gotten a fully satisfactory result.
I am in the middle of a very demanding project to deliver in which I've been working around the clock, but as soon as I have free time I promise to try to help you further 🙂

 

Hope I was of assistance!
Cheers
Joao Marcelino

Hi again, @Gari 🙂

Thanks for sending a data sample.
I am working on it, trying to find the proper rule for it.
Would you say that if there are two organs, that row will always have the expression "Double [organ]"
Can I assume that as a rule?


Hope I was of assistance!
Cheers
Joao Marcelino

Hi, again @JoaoMarcelino

 

I am afraid we couldn't club different organs "DeleteFlag" into one column as they may become cumbersome and complex. 

 

Let's say we are only writing the logic for Double Lung for now. The logic should be like this - When a record exists with "OrganName" = 'Double Lung' for a patient, then his/hers 'Left Lung' & 'Right Lung' records should be marked for deletion. 

 

I may have confused you because I was talking about "OrganKey" earlier. I was suggesting it may not be a good field to base our logic on if we are using that to identify 'Double Lung' with it on a max function.

Hope I did not confuse you any further.  😊

 

If you would like, you could give me a call at 1-510-330-3370 for more information.

 

Thank!

Gari.

JoaoMarcelino
Responsive Resident
Responsive Resident

Hi @Gari ,

Welcome to this amazing community and, btw, thank you for participating in the noble action of saving lives 🙂

 

Can you please provide me with that sample data in an excel or CSV so that I can help you with fixing the problem?

You probably just need to duplicate the query you have and in the new Query, you need to  Groupby the PatientKey and instead of sum, choose MAX, so that it only shows the 21 cases.
Then, go to the original table, merge it with this new Max table (by patientkey) and just expand the Column "count" with max values.
Then you need to test something like :
each if (not Text.Contains([OrganName],"Lung")) then "Keep" else if Text.Contains([OrganName],"Lung") and [Count] = "False" then "Delete" else "Keep")

Sorry for not helping more, but without a sample file to work it's hard 😞

Hope I was of assistance!
Cheers
Joao Marcelino
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors