Combining 2 columns in power query

I took your dummy data and managed to get a solution:

let
Source = Dummy,
AddDateEnded = Table.AddColumn(
Source,
"Date_Ended",
each
let
s = Text.Lower(Text.Trim([Status])),
candidates = List.RemoveNulls({[Date_of_completion], [Date_of_cancelation]})
in
if s = "ongoing" then null
else if List.Count(candidates) = 0 then null
else List.Max(candidates),
type date
)
in
AddDateEnded

Change the bold source step to your own data previous Powerquery step and add in the text. 

You will get the result

When in a strange occurence both completion and cancelled are filled it will take the most recent date.

Hope this is helpfull.

Hi RichOB , Use the below Mcode in the power query:

let
// Replace this with your actual source step/table
Source = PreviousStep,

// Add new column "Date_Ended"
// Logic:
// - If Status = "Ongoing" → null (no end date yet)
// - Else → use Date_of_completion if present
// - Else → use Date_of_cancelation
AddedDateEnded = Table.AddColumn(
Source,
"Date_Ended",
each if [Status] = "Ongoing" then null
else if [Date_of_completion] <> null then [Date_of_completion]
else [Date_of_cancelation],
type date
)
in
AddedDateEnded

Explanation of the mcode is below:

1. ChangedTypes step → ensures the Date_of_completion and Date_of_cancelation columns are recognized as proper date types (important to avoid errors).

2. AddedDateEnded step → creates a new column Date_Ended:

   • If status is Ongoing → returns null.

   • If there’s a completion date → uses that.

   • Otherwise, it falls back to the cancellation date.

This gives you the exact Date_Ended column shown in your example table.

If this helped. please hit “Accept as Solution” and leave a Kudos.

Hi RichOB 

Could you please follow below Power Query steps:

1. Open the file >> select the Data table >> Data tab >> From Table/Range (or in Power BI: Get data >> Excel >> Data sheet >> Transform data).
2. Make sure types are correct:
   • ID >> Whole Number
   • Status >> Text
   • Date_of_completion, Date_of_cancellation >> Date
3. Add Column >> Custom Column. Name it Date_Ended and use this formula (robust for nulls and keeps “Ongoing” blank):
4. if [Status] = "Ongoing" then null
5. else List.First( List.RemoveNulls( { [Date_of_completion], [Date_of_cancellation] } ) )

(This prefers completion; if that’s null, it uses cancellation.)

6. Set the new column’s type to Date.
7. Close & Load (Excel) or Close & Apply (Power BI).

Hi

Create a calculated column as below, 

Add a Custom Column in Power Query with this formula

= if [Status] = "Completed" then [Date_of_completion]
else if [Status] = "Cancelled" then [Date_of_cancelation]
else null

This creates your Date_Ended column.

Hi RichOB ,

Thank you for reaching out to the Microsoft fabric community forum. 

Thank you to all the community members for sharing your solutions and different approaches to achieve the required Date_Ended column. Each of the responses provides a valid way forward, whether through Power Query (using custom M code or conditional columns) or a calculated column in DAX, and they address the logic that the OP is looking for.

 RichOB did you get a chance to review and test the suggestions shared above? Please let us know if any of these approaches worked for your scenario or if you are still facing challenges. This will help us guide you further in the right direction.

Thank you.

Hi RichOB ,

I wanted to follow up on our previous suggestions regarding the issue. We would like to hear back from you to ensure we can assist you further.

Thank you.

Hi RichOB ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

Hi RichOB ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.