Replace date based on multiple conditions of other columns

[SammiF1244]

First, I am a beginner, so excuse my lack of knowledge.

I have this working formula in termination date to replace null values that is working fine: = Table.ReplaceValue(#"Added Custom",null,"12/31/9999",Replacer.ReplaceValue,{"TERMINATION_DATE"})  

but I need to expand it to include another condition. the 2nd condition, I think I can do it one of two ways: 

1.  if [RELEASE_DATE] > [CODE_EFFECTIVE_DATE] then Date.AddDays ([RELEASE_DATE] -1) else keep existing value

2. if [CATEGORY_AT_CREATION] = "TRUE" then Date.AddDays ([RELEASE_DATE] -1) else keep existing value

but I do not know how to expand the conditions. I get an error with every way I've tried. 

[SammiF1244]

Hi ronrsnfld,

Thank you for the offer. I have searched for a way and have not found one that works. 

There are 93,000+ rows in the original data and only a few meet the 2nd condition, so here is a sample of 25 rows. Row 10 meets the 2nd condition.  I hope this is what you needed.

FULL_DESCRIPTION CODE_EFFECTIVE_DATE CHANGE_EFFECTIVE_DATE TERMINATION_DATE RELEASE_DATE CREATION_DATE VALIDITY CATEGORY_AT_CREATION

				Osteomyelofibrosis	10/1/2015	10/1/2015	null	10/1/2023	7/17/2023	C	FALSE
				Neoplasm of uncertain behavior of lymphoid, hematopoietic and related tissue, unspecified	10/1/2008	10/1/2015	null	10/1/2023	7/17/2023	C	FALSE
				Other specified neoplasms of uncertain behavior of lymphoid, hematopoietic and related tissue	10/1/2009	10/1/2015	null	10/1/2023	7/17/2023	I	FALSE
				Post-transplant lymphoproliferative disorder (PTLD)	10/1/2009	10/1/2015	null	10/1/2023	7/17/2023	C	FALSE
				Castleman disease	10/1/2016	10/1/2016	null	10/1/2023	7/17/2023	C	FALSE
				Other specified neoplasms of uncertain behavior of lymphoid, hematopoietic and related tissue	10/1/2009	10/1/2015	09/30/2023	10/1/2023	7/17/2023	C	FALSE
				Neoplasm of uncertain behavior of other and unspecified sites	10/1/2015	10/1/2015	null	10/1/2023	7/17/2023	I	FALSE
				Neoplasm of uncertain behavior of bone and articular cartilage	10/1/2008	10/1/2015	null	10/1/2023	7/17/2023	C	FALSE
				Neoplasm of uncertain behavior of connective and other soft tissue	10/1/2015	10/1/2023	09/30/2020	10/1/2023	7/17/2023	I	FALSE
				Desmoid tumor	10/1/2023	10/1/2023	null	10/1/2023	7/17/2023	I	TRUE
				Desmoid tumor of head and neck	10/1/2023	10/1/2023	null	10/1/2023	7/17/2023	C	FALSE
				Desmoid tumor of chest wall	10/1/2023	10/1/2023	null	10/1/2023	7/17/2023	C	FALSE
				Desmoid tumor, intrathoracic	10/1/2023	10/1/2023	null	10/1/2023	7/17/2023	C	FALSE
				Desmoid tumor of abdominal wall	10/1/2023	10/1/2023	null	10/1/2023	7/17/2023	C	FALSE
				Desmoid tumor, intraabdominal	10/1/2023	10/1/2023	null	10/1/2023	7/17/2023	C	FALSE
				Desmoid tumor of upper extremity and shoulder girdle	10/1/2023	10/1/2023	null	10/1/2023	7/17/2023	C	FALSE
				Desmoid tumor of lower extremity and pelvic girdle	10/1/2023	10/1/2023	null	10/1/2023	7/17/2023	C	FALSE
				Desmoid tumor of back	10/1/2023	10/1/2023	null	10/1/2023	7/17/2023	C	FALSE
				Desmoid tumor of other site	10/1/2023	10/1/2023	null	10/1/2023	7/17/2023	C	FALSE
				Desmoid tumor of unspecified site	10/1/2023	10/1/2023	null	10/1/2023	7/17/2023	C	FALSE
				Other specified neoplasm of uncertain behavior of connective and other soft tissue	10/1/2023	10/1/2023	null	10/1/2023	7/17/2023	C	FALSE
				Neoplasm of uncertain behavior of peripheral nerves and autonomic nervous system	10/1/2008	10/1/2015	null	10/1/2023	7/17/2023	C	FALSE
				Neoplasm of uncertain behavior of retroperitoneum	10/1/2008	10/1/2015	null	10/1/2023	7/17/2023	C	FALSE
				Neoplasm of uncertain behavior of peritoneum	10/1/2015	10/1/2015	null	10/1/2023	7/17/2023	C	FALSE
				Neoplasm of uncertain behavior of skin	10/1/2008	10/1/2015	null	10/1/2023	7/17/2023	C	FALSE

[ronrsnfld]

Your 2nd condition does not make sense to me.

1. I assume you are replacing values in TERMINATION_DATE as with the nulls
2. When you write "I can do this one of two ways" I assume you mean either way would work
3. But the two "ways" are not equivalent. 
   1. [RELEASE_DATE] > [CODE_EFFECTIVE_DATE]  is TRUE for the first 9 rows
   2. [CATEGORY_AT_CREATION] = "TRUE" is TRUE only for row 10
   3. Since the two rules are NOT equivalent, what would you want if both were true?

Please clarify

[SammiF1244]

The CATEGORY_AT_CREATION (true false) column is an added custom column based on this criteria: = Table.AddColumn(#"Replaced Value", "CATEGORY_AT_CREATION", each if [VALIDITY] = "I" and [RELEASE_DATE]<= [CODE_EFFECTIVE_DATE]
then "TRUE"
else "FALSE")

UPDATE-- sorry the two condition would be this:   if[VALIDITY] = "I" and [RELEASE_DATE]<= [CODE_EFFECTIVE_DATE] then Date.AddDays ([RELEASE_DATE] -1) else keep existing value

[ronrsnfld]

That doesn't clarify what you want to do with TERMINATION_DATE.

Perhaps if you wrote it out in words instead of pseudo-formulas.

Something like:

   if [RELEASE_DATE] > [CODE_EFFECTIVE_DATE] or [CATEGORY_AT_CREATION] = TRUE

then replace [TERMINATION DATE] with the day before [RELEASE_DATE]

else if [TERMINATION_DATE] = null then replace it with 12/31/9999 else leave it alone.

or whatever you actual logic is.

[SammiF1244]

That's it...you got it right with your logic 🙂 I just missed on my explanation. I can't use the or [CATEGORY_AT_CREATION] = 

This is what I need:

   if [VALIDITY] = "I" and [RELEASE_DATE] > [CODE_EFFECTIVE_DATE] 

then replace [TERMINATION DATE] with the day before [RELEASE_DATE]

else if [TERMINATION_DATE] = null then replace it with 12/31/9999 else leave it alone.

[ronrsnfld]

OK, here is code that implements that logic.

By the way, row 10 of the data does not seem to meet the criteria since RELEASE_DATE = CODE_EFFECTIVE_DATE

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"FULL_DESCRIPTION", type text}, {"CODE_EFFECTIVE_DATE", type date}, {"CHANGE_EFFECTIVE_DATE", type date}, 
        {"TERMINATION_DATE", type date}, {"RELEASE_DATE", type date}, {"CREATION_DATE", type date}, 
        {"VALIDITY", type text}, {"CATEGORY_AT_CREATION", type logical}}),
    
    #"Replace Termination Date" = Table.ReplaceValue(
        #"Changed Type",
        each [TERMINATION_DATE],    
        each    if [VALIDITY] = "I" and [RELEASE_DATE] > [CODE_EFFECTIVE_DATE] 
                then Date.AddDays([RELEASE_DATE],-1)
                else if [TERMINATION_DATE] = null then #date(9999,12,31) 
                else [TERMINATION_DATE],
        Replacer.ReplaceValue,
        {"TERMINATION_DATE"}),
    
    #"Changed Type1" = Table.TransformColumnTypes(#"Replace Termination Date",{{"TERMINATION_DATE", type date}})
in
    #"Changed Type1"

RESULTS Termination Date Only

[SammiF1244]

I tried your code, but it threw an error---Expression.SyntaxError: Invalid identifier.  -- I know this is user error due to me lack of knowing how to add all the correct elements based on my sources, file names, etc.

This is my full code prior to applying the term date conditions. 

let
Source = Folder.Files("C:\Users\SFowler\OneDrive - Evolent Health, Inc\Continuous Improvement - Code Governance\Artifacts\Source Files\Optum ICD Base Coverage"),
#"C:\Users\SFowler\OneDrive - Evolent Health, Inc\Continuous Improvement - Code Governance\Artifacts\Source Files\Optum ICD Base Coverage\_OPTUM_ICD10CM_BASE_Coverage_2024 TAB" = Source{[#"Folder Path"="C:\Users\SFowler\OneDrive - Evolent Health, Inc\Continuous Improvement - Code Governance\Artifacts\Source Files\Optum ICD Base Coverage\",Name="OPTUM_ICD10CM_BASE_Coverage_2024.TAB"]}[Content],
#"Imported CSV" = Csv.Document(#"C:\Users\SFowler\OneDrive - Evolent Health, Inc\Continuous Improvement - Code Governance\Artifacts\Source Files\Optum ICD Base Coverage\_OPTUM_ICD10CM_BASE_Coverage_2024 TAB",[Delimiter="#(tab)", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CODE", type text}, {"STATUS", type text}, {"SHORT_DESCRIPTION", type text}, {"LONG_DESCRIPTION", type text}, {"FULL_DESCRIPTION", type text}, {"CODE_EFFECTIVE_DATE", type date}, {"CHANGE_EFFECTIVE_DATE", type date}, {"TERMINATION_DATE", type date}, {"RELEASE_DATE", type date}, {"CREATION_DATE", type date}, {"VALIDITY", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",".","",Replacer.ReplaceText,{"CODE"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "CATEGORY_AT_CREATION", each if [VALIDITY] = "I" and [RELEASE_DATE]<= [CODE_EFFECTIVE_DATE]
then "TRUE"
else "FALSE")
in
#"Added Custom"

[ronrsnfld]

Which "Applied Step" is the first one that is showing the error?

ALSO, your posted code is not implementing my suggestion at all and it refers to a table I do not have access to, so it is hard to tell.

[SammiF1244]

I revised it some and got closer (I think) now it is this: 

let
Source = Folder.Files("C:\Users\SFowler\OneDrive - Evolent Health, Inc\Continuous Improvement - Code Governance\Artifacts\Source Files\Optum ICD Base Coverage"),
#"C:\Users\SFowler\OneDrive - Evolent Health, Inc\Continuous Improvement - Code Governance\Artifacts\Source Files\Optum ICD Base Coverage\_OPTUM_ICD10CM_BASE_Coverage_2024 TAB" = Source{[#"Folder Path"="C:\Users\SFowler\OneDrive - Evolent Health, Inc\Continuous Improvement - Code Governance\Artifacts\Source Files\Optum ICD Base Coverage\",Name="OPTUM_ICD10CM_BASE_Coverage_2024.TAB"]}[Content],
#"Imported CSV" = Csv.Document(#"C:\Users\SFowler\OneDrive - Evolent Health, Inc\Continuous Improvement - Code Governance\Artifacts\Source Files\Optum ICD Base Coverage\_OPTUM_ICD10CM_BASE_Coverage_2024 TAB",[Delimiter="#(tab)", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CODE", type text}, {"STATUS", type text}, {"SHORT_DESCRIPTION", type text}, {"LONG_DESCRIPTION", type text}, {"FULL_DESCRIPTION", type text}, {"CODE_EFFECTIVE_DATE", type date}, {"CHANGE_EFFECTIVE_DATE", type date}, {"TERMINATION_DATE", type date}, {"RELEASE_DATE", type date}, {"CREATION_DATE", type date}, {"VALIDITY", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",".","",Replacer.ReplaceText,{"CODE"}),
#"Replace Termination Date" = Table.ReplaceValue(
#"Changed Type",
each [TERMINATION_DATE],
each if [VALIDITY] = "I" and [RELEASE_DATE] > [CODE_EFFECTIVE_DATE]
then Date.AddDays([RELEASE_DATE],-1)
else if [TERMINATION_DATE] = null then #date(9999,12,31)
else [TERMINATION_DATE],
Replacer.ReplaceValue,
{"TERMINATION_DATE"}),

[ronrsnfld · Accepted solution]

You really need to answer my question, if the same thing is happening. Or explain what you mean by "got closer".  In your posted code, there is an error. You are missing the in statement. And you are also missing the Changed Type1 step.

    #"Changed Type1" = Table.TransformColumnTypes(#"Replace Termination Date",{{"TERMINATION_DATE", type date}})
in
    #"Changed Type1"
    

[SammiF1244]

I DID it!! this code worked: 

let
Source = Folder.Files("C:\Users\SFowler\OneDrive - Evolent Health, Inc\Continuous Improvement - Code Governance\Artifacts\Source Files\Optum ICD Base Coverage"),
#"C:\Users\SFowler\OneDrive - Evolent Health, Inc\Continuous Improvement - Code Governance\Artifacts\Source Files\Optum ICD Base Coverage\_OPTUM_ICD10CM_BASE_Coverage_2024 TAB" = Source{[#"Folder Path"="C:\Users\SFowler\OneDrive - Evolent Health, Inc\Continuous Improvement - Code Governance\Artifacts\Source Files\Optum ICD Base Coverage\",Name="OPTUM_ICD10CM_BASE_Coverage_2024.TAB"]}[Content],
#"Imported CSV" = Csv.Document(#"C:\Users\SFowler\OneDrive - Evolent Health, Inc\Continuous Improvement - Code Governance\Artifacts\Source Files\Optum ICD Base Coverage\_OPTUM_ICD10CM_BASE_Coverage_2024 TAB",[Delimiter="#(tab)", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CODE", type text}, {"STATUS", type text}, {"SHORT_DESCRIPTION", type text}, {"LONG_DESCRIPTION", type text}, {"FULL_DESCRIPTION", type text}, {"CODE_EFFECTIVE_DATE", type date}, {"CHANGE_EFFECTIVE_DATE", type date}, {"TERMINATION_DATE", type date}, {"RELEASE_DATE", type date}, {"CREATION_DATE", type date}, {"VALIDITY", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",".","",Replacer.ReplaceText,{"CODE"}),
#"Replace Termination Date" = Table.ReplaceValue(
#"Changed Type",
each [TERMINATION_DATE],
each if [VALIDITY] = "I" and [RELEASE_DATE] > [CODE_EFFECTIVE_DATE]
then Date.AddDays([RELEASE_DATE],-1)
else if [TERMINATION_DATE] = null then #date(9999,12,31)
else [TERMINATION_DATE],
Replacer.ReplaceValue,
{"TERMINATION_DATE"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replace Termination Date",{{"TERMINATION_DATE", type date}})
in
#"Changed Type1"

[SammiF1244]

 The code I posted is not exactly what I need, but with your guidance on how to apply the condition, I think I can get it from here 🙂 Thanks again! 

[SammiF1244]

You've been so patient and helpful. I really appreciate it. My brain is overloaded, and I have no clue where to begin changing your code to meet my sources.

When I tried to modify your full code the first time, I got the Expression.SyntaxError: Invalid identifier.  So, I tried a 2nd time by keeping my code that was working and appending the new line of codes from yours. Obviously, still missing something. 

This is too much to ask of you. If you do consult services, I am happy to pay you to assist me further. 

Thanks again!! YOU have been great! 

[ronrsnfld]

You can use the arguments of the Table.ReplaceValue function to encompass those conditions. There are examples online that you can find with a web search.

I would be happy to help you further if you provide a data sample of your initial starting data as text or a link.