Forum Discussion
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 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?
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])
Yep, which is why I said above, you'll need to convert blanks to nulls.
Glad you got it worked out.
7 Replies
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
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])
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
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])
Yep, which is why I said above, you'll need to convert blanks to nulls.
Glad you got it worked out.
