NEW date column based on condition

[Anonymous]

Hi, 

I have 2 dates column and I want to create  a new date column based on the Last PODdate and FirstPOD date. 

The logic is as follows: 

New PoDDateColumn= If the FirstPOD date has a value use FirstPod Date as a column. If there is no value in the first POD date use the Last PodDate. Any Help would be appreciated? 

[Anonymous · Accepted solution]

I solved this problem on my own using a very simple trick. I trimmed the dates and then inserted null in the blanks. After inserting "null" in place blanks. I was able to get what I wanted. using the same coalesce function using ??

= Table.AddColumn(#"Changed Type", "Final_PodDate", each [FirstPODDate]??[LastPODDate]) 

[Anonymous · Accepted solution]

I solved this problem on my own using a very simple trick. I trimmed the dates and then inserted null in the blanks. After inserting "null" in place blanks. I was able to get what I wanted. using the same coalesce function using ??

= Table.AddColumn(#"Changed Type", "Final_PodDate", each [FirstPODDate]??[LastPODDate]) 

[KNP · Accepted solution]

Yep, which is why I said above, you'll need to convert blanks to nulls. 

Glad you got it worked out.

[hannibalmads]

Add a custom column where you concatenate the two fields and then extract the first 8 characters (assuming that the data types are text)

Text.Start(Text.Combine({[FirstPODDate], [LastPODDate]}, ""), 8 )

If the data types are numeric then the coalesce should work

[mahoneypat]

You can add a custom column in the query editor to do that. You could use if ... then ... else syntax, but it is easier to use the coalesce syntax in this case. Just click on the Add Column tab, then Custom Column, and enter the expression below into the pop-up box.

= [FirstPODdate] ?? [LastPODdate]

Pat

[Anonymous]

Okay I did that and it gives me only the FirstPodDates as shown below and Power Query is also given 

Dates not being read properly is that going to be an issue? 

i will use the modified date column as the date connector for the date dimmension table. 

Any hints would be great! 

= Table.AddColumn(#"Changed Type", "Final_PodDate", each [FirstPODDate]??[LastPODDate])

[Anonymous]

Still am facing the issue after using the coalesce function

[KNP]

You'll likely need to convert blanks to nulls first for the coalesce to work.

You should be able to convert to date from this format fine. 

See example code below. If this works for you, accept @mahoneypat's answer as this is just supplemental to his.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjAEIiUdJaVYHTjfCI1vDORDmIZGYHEE3xiNb6IUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FirstPODDate = _t, LastPODDate = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"LastPODDate", "FirstPODDate"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each [FirstPODDate]??[LastPODDate]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}})
in
    #"Changed Type"