Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
venstart
Frequent Visitor

Power Query (BI) replace date from another column

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

venstart_0-1609870675201.png

 

 

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

 

How to resolve this. Send me the query code

 

 

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@venstart 

You may add a conditional column as below:

V-pazhen-msft_2-1610002119678.png

 

V-pazhen-msft_1-1610002025044.png

 

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

View solution in original post

6 REPLIES 6
V-pazhen-msft
Community Support
Community Support

@venstart 

You may add a conditional column as below:

V-pazhen-msft_2-1610002119678.png

 

V-pazhen-msft_1-1610002025044.png

 

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
Super User
Super User

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 

 

SU18_powerbi_badge

ToddChitt
Super User
Super User

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.

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

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?

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors