Custom Column

[mgaut341]

I created a custom column in my power query that looks at appointment slot status. When I run my code, the slot status for "2- Checked In", is being marked as "No Show" instead of "Appointment Completed". 

Any ideas where I am going wrong in my code? 

if [apptcancelreason]= "BLANK" and
[apptslotstatus] = "2 - Checked In" or
[apptslotstatus]= "3- Checked Out" or
[apptslotstatus]= "4 - Charge Entered" and
[apptdate] <DateTime.LocalNow()
then "Appointment Completed"
else

if [apptcancelreason] = "BLANK" and
[apptslotstatus] = "f - Filled" and
[apptdate] < DateTime.LocalNow()
then "No Show"
else
if [apptcancelreason] = "BLANK" and
[apptslotstatus] = "f - Filled" and
[apptdate] > DateTime.LocalNow()
then "Upcoming Appointment"
else
if [apptcancelreason] = "BLANK" and
[apptslotstatus] = "o - Open Slot"
then "Open Slot"
else
if [apptcancelreason] = "CANCELLED NOT RESCHEDULED"
then "Patient Cancellation"
else
if [apptcancelreason] = "RESCHEDULED"
then "Rescheduled"
else
if [apptcancelreason] = "Provider Cancellation"
then "Avoidable Cancellation"
else
if [apptcancelreason] = "ENTERED IN ERROR"
then "Avoidable Cancellation"
else
if [apptcancelreason] = "PATIENT EMERGENCY"
then "Patient Cancellation"
else
if [apptcancelreason] = "LACK OF INTERPRETERS"
then "Avoidable Cancellation"
else
if [apptcancelreason] = "APPOINTMENT MOVED"
then "Avoidable Cancellation"
else
if [apptcancelreason] = "PROVIDER NO SHOW"
then "Avoidable Cancellation"
else
if [apptcancelreason] = "CH CLOSED"
then "Unavoidable Cancellation"
else
if [apptcancelreason] = "PATIENT NO LONGER WANTS/NEEDS APPOINTMENT"
then "Patient Cancellation"
else
if [apptcancelreason] = "COVID Protocols"
then "Unavoidable Cancellation"
else
if [apptcancelreason] = "RESCHEDULED for TH VISIT"
then "Rescheduled"
else
if [apptcancelreason] = "LESS THAN 24 HR CANCELLATION-SPECIALTY"
then "Avoidable Cancellation"
else
if [apptcancelreason] = "CANCELLED BY SMS"
then "Patient Cancellation"
else
if [apptcancelreason] = "NO LAB SHIFT LEADER"
then "Avoidable Cancellation"
else
if [apptcancelreason] = "CANCELLED FROM PATIENT PORTAL"
then "Patient Cancellation"
else "No Show"

[AbhinavJoshi]

can you provide all the relevant columns that we have in the logic please? General advise here would be check the date field is of correct data type, trim and clean all the text based columns.

[AbhinavJoshi]

Hi @mgaut341 . Can you please provide a sample data where it is coming as "No Show". It would help us in identifying the problem.

[mgaut341]

[mussaenda]

We need the data that we can copy and where all the columns are present
[apptdate], [apptslotstatus], [apptcancelreason]

I am thinking if [apptdate] is the culprit for this since the value for this field will not be exactly the same as DateTime.LocalNow(). It is just my assumption since I cannot see the data for the field.

Hope this helps

[AbhinavJoshi]

HI @mgaut341 . The issue is likely due to grouping of your conditional statements.

Try this

if 
    [apptcancelreason] = "BLANK" and 
    (
        [apptslotstatus] = "2 - Checked In" or 
        [apptslotstatus] = "3- Checked Out" or 
        [apptslotstatus] = "4 - Charge Entered"
    ) and 
    [apptdate] < DateTime.LocalNow() 
then 
    "Appointment Completed"

 With these modifications, appointments with a status of "2 - Checked In" should now be correctly marked as "Appointment Completed" instead of "No Show", as long as the other conditions are met. The rest of the logic remains the same. 

Let me know if you have any more questions.

Thank you,

Abhinav

[mgaut341]

Thanks for the feedback. Unfortunately, that changed all of them to "No Show" instead of "Appointment Completed"

[mussaenda]

Hi @mgaut341 ,

@AbhinavJoshi is right with the condition grouping. 

One more thing we need to check are the values we are declaring in conditions.

is "BLANK" really a BLANK word in all caps? (Note that Power Query is case sensitive)

Or is it null as in a null value? Because if it is null then we need to do = null instead of = "BLANK".

Hope this helps  

[mgaut341]

I've tried changing it to "null" but the same issue arises. 2-Checked In still shows up as No Show, while 3 & 4 show up at Appointment Completed

[Anonymous]

Hi @mgaut341 ,

There's nothing wrong with your logic per se, and there's nothing wrong with measuring it on my end.

But if by [apptcancelreason] = “BLANK” you mean that there is nothing in the cell, then change the M code to [apptcancelreason] = “” with nothing in between the double quotes and no spaces and try again.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.