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

[Gari]

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.

[Gari]

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

[Gari]

Any Success @JoaoMarcelino? 

[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

[JoaoMarcelino]

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

[Gari]

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]

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!