Power Query (BI) replace date from another column

[venstart]

I need help with Power Query (BI) replace date from another column if the not NULL

So i got two columns in my table on Power BI, I will add a custom FX

First Column : Date

Second Column : TransformedDate

Now on the " TransformedDate " column there are dates and NULL values, I want to copy the values of the " TransformedDate " which are NOT NULL and replace it to the " Date " column

FYI: My last custom step --> = #"Sorted Rows"

How to resolve this. Send me the query code

[Anonymous · Accepted solution]

@venstart 

You may add a conditional column as below:

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[AlB]

Hi @venstart 

Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA3MAQiJR0lpVgdJBEjoAiYY2ikb2SEKmeModoErhrIN1eKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, TransformedDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"TransformedDate", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),

    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each if [TransformedDate]<> null then [TransformedDate] else [Date], type date),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "TransformedDate"})
in
    #"Reordered Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[ToddChitt]

You could do this by inserting a new Custom Conditional Column.

IF [TransformedDate] equals null THEN Date

Else TransformedDate

The M code looks like this:

#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Custom", each if [TransformedDate] = null then [Date] else [TransformedDate])

But use the GUI for adding a new Conditional Column, it's easier.

Hope that helps.

[venstart]

Hi @ToddChitt 

Thank you for your reply

However, I dont want to replace the TransformedDate.

But want to replace/copy values of the " TransformedDate " which are NOT NULL (meaning with date values only from TransforedDate) and replace it to the " Date " column

My last custom step --> = #"Sorted Rows"

How to resolve this. Send me the query code

[ToddChitt]

Have you looked at the Conditional Column options? You can still do this.

What is the Condition? (the "IF" part)? [TransformDate] IS NULL 

What is the Output if the Condition is evaluated to TRUE? (The THEN part)?

What is the Output if the Condition is evaluated to FALSE (The ELSE part)?

One way or another, you are goig to need to 1) Create a new Conditional column, 2) remove one or both of the originating columns, and 3) re-name the new column so it takes the place of one of the original ones.

Try writing out your logic in your native language:

IF [Transform date] IS NULL, then I want [Date], otherwise I want [Transform Date]

Or is it something else?

[venstart]

Can I send you the power bi file so that you can check the 2 column in the table