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.

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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'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"