Combining 2 columns in power query

[RichOB]

Hi, using the table below, how can I achieve the Date_Ended column (FYI the Date ended column is not on my real data, this is what I need)?

I have the Date of Completion and Date of Cancellation, but I need them in the 1 Date ended column, please. I also need to take into account that if a status says "ongoing" that it is acceptable that there will be no end date yet.

ID	Status	Date_of_completion	Date_of_cancelation	Date_Ended
1	Completed	01/04/2024		01/04/2024
2	Completed	10/05/2024		10/05/2024
3	Cancelled		10/06/2024	10/06/2024
4	Completed	11/07/2024		11/07/2024
5	Cancelled		12/06/2024	12/06/2024
6	Completed	11/08/2024		11/08/2024
7	Cancelled		10/10/2024	10/10/2024
8	Ongoing

[v-tsaipranay]

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.

[v-tsaipranay]

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.

[v-tsaipranay]

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.

[v-tsaipranay]

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.

[Shahid12523]

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.

[DataNinja777 · Accepted solution]

Hi @RichOB ,

You can achieve your desired 'Date_Ended' column in Power Query by creating a new column that consolidates the 'Date_of_completion' and 'Date_of_cancelation' columns based on the 'Status' column. This process will correctly account for "Ongoing" statuses by leaving their end date blank.

One way to do this is by using a Conditional Column. In the Power Query Editor, you would go to the "Add Column" tab and select "Conditional Column". From there, you can set up the logic. You would instruct Power Query to check the "Status" column. If the status is "Completed", it should take the value from the "Date_of_completion" column. You would then add another clause stating that if the status is "Cancelled", it should take the value from the "Date_of_cancelation" column. For any other case, like "Ongoing", the result should be null, which will leave the cell blank. After creating the column, you should set its data type to "Date".

Alternatively, if you prefer writing formulas, you can use a Custom Column. You would again go to the "Add Column" tab but this time select "Custom Column". You would name the new column 'Date_Ended' and then enter the following Power Query M formula. This code accomplishes the same logic as the conditional column method.

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

After you click OK, the new column will be created. As with the first method, ensure you change the data type of this new column to "Date" to ensure proper formatting and functionality in your reports. Both approaches will yield the exact same result, giving you a single, clean 'Date_Ended' column.

Best regards,

[rohit1991]

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).

[Shubham_rai955]

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.

[srlabhe]

Hi

Create a calculated column as below, 

DateEnded = if('Table'[Status]="Ongoing",BLANK(),

if(ISBLANK('Table'[Date_of_cancelation])=TRUE(),'Table'[Date_of_completion],'Table'[Date_of_cancelation]))

[ChielFaber]

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.