Forum Discussion
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
FYI: My last custom step --> = #"Sorted Rows"
How to resolve this. Send me the query code
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.
6 Replies
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?
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
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.
