Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

NEW date column based on condition

  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...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
    KNP
    3 years ago

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

    Glad you got it worked out.

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Still am facing the issue after using the coalesce function

 

 

 

KNP's avatar
KNP
Super User
3 years ago

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"