How to Conditionally Replace Values in Power Query?

[christopherocc]

There is some jumbled data in this table I'm working on. I have two columns in question. SubmittedDate and SubmissionType. I want to do the following:

- If SubmittedDate is null and SubmissionType is not 'Initial', I want to fill SubmittedDate with the value from SubmissionType.

- If SubmittedDate is null and SubmissionType is 'Initial', I want to leave SubmittedDate as it is.

Is there a way to do achieve this? Is there a different approach? I would prefer to keep cleaning in Power BI.

[v-jialongy-msft · Accepted solution]

Hi @christopherocc 

Please try the following code:

let
    Source = YourDataSource,
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"SubmittedDate"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "NewSubmittedDate", each if [SubmittedDate] = null and [SubmissionType] <> "Initial" then [SubmissionType] else [SubmittedDate]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"SubmittedDate"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewSubmittedDate", "SubmittedDate"}})
in
    #"Renamed Columns"

                 

  

Best Regards,

Jayleny

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

[Deelip]

Hi @christopherocc 

There is a work around for that by adding conditional column with your logic and after that you can remove your original submission date column and keeping your conditional column as submission date. 

Or you can use the below query in a new custom column as 

each if [SubmittedDate] = null and [SubmissionType] <> "Initial" then [SubmissionType]
else [SubmittedDate]

Thanks!