Forum Discussion
Custom Column
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"
9 Replies
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
Thanks for the feedback. Unfortunately, that changed all of them to "No Show" instead of "Appointment Completed"
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
Hi mgaut341 . Can you please provide a sample data where it is coming as "No Show". It would help us in identifying the problem.
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
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.
