Combining 2 columns in power query

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,