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 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.

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.

     

     

     

2 Replies

  • 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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.