Forum Discussion

christopherocc's avatar
christopherocc
Frequent Visitor
1 year ago
Solved

How to Conditionally Replace Values in Power Query?

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 SubmissionTyp...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
Deelip
Resolver I
1 year ago

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!