Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Pioneer
Regular Visitor

How to combine two columns dates into one column

Hi,

How to combine two dates columns of a table  into one  new column .please check below sample data for reference.

 

Pioneer_0-1668596866441.png

 

expecting output:

Pioneer_1-1668596999041.png

 

Sample excelreport:

In_Date                     Out_date                 
01-11-201420-08-1999
15-04-199819-01-1998
16-11-2022 
20-08-199921-08-1999
21-01-200120-09-1993
21-09-199421-01-2001
30-01-199701-11-2014

 

Thanks,

Pioneer

 

1 ACCEPTED SOLUTION

Hi, @Pioneer 

 

This can only be done in Power Query.

Copy two more tables, delete one of the columns from each table, change the column names to [In and Out date] and use Append.

vzhangti_0-1668753749148.pngvzhangti_1-1668753763065.png

Select Remove duplicates.

vzhangti_2-1668753804640.png

Join the initial table and the Append table to the right.

vzhangti_4-1668754052089.png

Result:

vzhangti_5-1668754090115.png

Click in the top right corner to expand.

vzhangti_6-1668754142904.png

vzhangti_7-1668754204803.png

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
v-zhangti
Community Support
Community Support

Hi, @Pioneer 

 

You can try the following methods.
New Table:

Table 2 = DISTINCT(UNION(VALUES('Table'[In_Date]),VALUES('Table'[Out_date])))

vzhangti_0-1668677527148.png

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-zhangti ,

 

Thanks for reply , My requirement is to create another column in the same table instead of creating  new table.

that New  column should able to use in slicer  visual aswell.

 

Thanks,

Pioneer

Hi, @Pioneer 

 

This can only be done in Power Query.

Copy two more tables, delete one of the columns from each table, change the column names to [In and Out date] and use Append.

vzhangti_0-1668753749148.pngvzhangti_1-1668753763065.png

Select Remove duplicates.

vzhangti_2-1668753804640.png

Join the initial table and the Append table to the right.

vzhangti_4-1668754052089.png

Result:

vzhangti_5-1668754090115.png

Click in the top right corner to expand.

vzhangti_6-1668754142904.png

vzhangti_7-1668754204803.png

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I have 9 date columns . Can you help plz?i tried the above solution but no luck. Is there anyway i can define date filter combining all the 9 date columns? Attached sample data. Kindly help.

Task#UserTask NamePlanned StartPlanned EndActual StartActual EndIT ApprovalInitial ApprovalTechnical ApprovalFinal ApprovalStatus
1JohnTask 110/2/2023 0:0010/8/2023 0:0010/2/2023 0:0010/8/2023 0:0012/26/2023 0:0011/4/2023 0:0012/13/2023 0:00 In Progress
2JohnnyTask29/12/2023 0:009/27/2023 0:009/12/2023 0:0012/2/2023 0:00    On-hold
3ClaireTask3        On-hold
4VennaTask412/3/2023 0:00 12/3/2023 0:0012/13/2023 0:00    In Progress
5SierraTask59/30/2023 0:0010/4/2023 0:00     1/20/2024Canceled
6AdamTask6      1/1/2024 Completed
7JohnTask79/24/2023 0:009/25/2023 0:009/25/2023 0:009/25/2023 0:00    Completed
8JohnnyTask811/6/2023 0:0011/8/2023 0:0011/6/2023 0:0011/11/2023 0:00 11/12/2023 0:0011/19/2023 0:00 Completed
9ClaireTask99/9/2023 0:009/11/2023 0:009/9/2023 0:009/11/2023 0:00 10/1/2024 0:0015/1/2024  12:00:00 AM Completed
10VennaTask1012/23/2023 0:001/3/2024 0:0012/23/2023 0:001/3/2024 0:00 1/14/2024 0:001/7/2024 0:001/17/2024 0:00Completed
Luis98
Resolver II
Resolver II

Hi @Pioneer ,

 

What is the logic you follow to get the combine column?

Hi @Luis98,

Iam looking for the Dax logic to achieve the expected output column in the same table.

 

Thanks,

Pioneer

 

Yes, but its needed to know the logic how you want obtain the output column,

 

is In_date at top and Out_date at bottom and remove duplicates?

 @Luis98 

We need to get all the dates, including blank values if existed from both the columns in  one new column and need to maintain unique data.

 

Thanks 

pioneer

 

I think the best is do it in Power Query. You can duplicate the table and then append, and you will have something like this:

Luis98_0-1668599478102.png

And you just should to combine both columns and remove duplicates.

Hi @Luis98 

 

I have tried this method not working as expected,looking for any DAX column  

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.