cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Turn Start Date & End Date columns into one column with

Hey everyone,

 

I have 2 columns (Start Date and End date), each one specifying a different project, that I need to combine into one column containing all dates for each project. The column should include dates in between each project, eventually I'm creating a heat map calendar based off this. So far I have this for creating a new table:

 

Table 2 = CALENDAR( Table1[Start Date], Table1[End Date])

 

which is giving me 

"A single value for column 'Start Date' in Table1 cannot be determined" (I'd need to specify an aggregation)

 

Does anyone have an idea how to move forward from here?
Thanks,

Pierre

 

 

19988792_1444086522349568_1018085413_n[1].jpg

 

 

 

2 ACCEPTED SOLUTIONS

Hi @pierre415,

 

You can follow below steps to use the query:

 

1. Click on 'fx' to add custom steps.

1.PNG

 

Previous step name:

3.png

 

2. Replace bold part with your column names and step name.

 

=Table.AddColumn(#"Changed Type", "Custom", each List.Dates([StartDate], Duration.TotalDays([EndDate]-[StartDate]), #duration(1, 0, 0, 0)))
                 previous step name                          startdate                       enddate   startdate

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Hi @pierre415,

 

Click on the arrow button and choose 'expand to new row' option:

5.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

14 REPLIES 14
New Member

Spoiler

Dear Community, 
Thank you for these examples, they have helped me a lot!

I am trying to find a solution to a very similar problem:

I have a data source in which the start time (Date time) and end time (date time) of machine failures is registerd. (So a row consist of columns with machine specifics and then 2 columns with a start and end time of the failure.

 

I managed to expand the rows in such a way that i now have a row for each day that a machine is ofline (great!). However, I would like to take it one step further and take the start time and end time (hours) into account.

 

So the result I am looking for is a row for each day (with date) a machine is off-line, followed by a column with the Hours the machine was offline that day. E.G. if it was off-line the whole day it should say 24h, and when it starts to be off-line at 02:00 PM it should say 10 hours (same for the day it gets fixed).

I think this problem is very similar to the topic question, however, I have not succeeded to tune this example to a solution that works for me.

Any ideas on how i could approach this problem?

Hope to hear from you,
Stijn Hillenius

Community Support
Community Support

Hi @pierre415,

 
You can try to use unpivot columns function in power query to achieve your requirement.

 
Steps:
1. Open query editor.
2. Choose date columns and click on 'unpivot columns'.

9.PNG

 

10.PNG

 

In addition, you can also use union function(dax) to create new table with wanted records.

 

Union Table = UNION(SELECTCOLUMNS(Table,"Name",[Name],"Date",[start dt]),SELECTCOLUMNS(Table,"Name",[Name],"Date",[end dt]))

 

Regards,

XIaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hey Xlaoxin, 

 

This seems good but I also need a row for each day between the start & end dates. So if a project starts Dec 1 and ends Dec 10, I need a seperate row for Dec 1st,2nd,3rd...10th. Do you have any ideas?

Hi @pierre415,

 

You can add a custom column to store the date range, then expand the date list.

 

Sample:

Custom = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([StartDate], Duration.TotalDays([EndDate]-[StartDate]), #duration(1, 0, 0, 0)))

 

13.PNG

14.PNG

 

Reference:

Function Description
Duration.TotalDays Returns the total magnitude of days from a Duration value.
List.Dates Returns a list of date values from size count, starting at start and adds an increment to every value.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

If I wanted to copy those 4 columns into a new table, would you know how to do that?

Screenshot (6).pngScreenshot (7).png

Hi @pierre415,

 

You can follow below steps to use the query:

 

1. Click on 'fx' to add custom steps.

1.PNG

 

Previous step name:

3.png

 

2. Replace bold part with your column names and step name.

 

=Table.AddColumn(#"Changed Type", "Custom", each List.Dates([StartDate], Duration.TotalDays([EndDate]-[StartDate]), #duration(1, 0, 0, 0)))
                 previous step name                          startdate                       enddate   startdate

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Amazing - you made my day!! Thank you.

Thanks a lot Xiaoxin, I can't tell you how much this helps. Just one more question: how do I expand my date lists so that each row shows a date? 

 

Thanks
Pierre

Hi @pierre415,

 

Click on the arrow button and choose 'expand to new row' option:

5.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

I can't thank you enough Xiaoxin, you saved me. Thanks for everything...

 

Best,

Pierre

Ok thanks! I copied that code to create a custom column and keep getting errors though...

 

The only difference is I used a 'duration' column instead of [StartDate] - [EndDate] which has decimal numbers, maybe that could be the problem? Or is my syntax wrong?

 

Screenshot (3).pngScreenshot (4).png

 

Thanks,

Pierre

Helper I
Helper I

In order to use CALENDAR function you need to specify single values.

What you need to write is something in lines "Table 2 = CALENDAR(MIN(Table1[Start Date]), MAX(Table1[End Date]))"

 

But this gives you just one table (no duplicate dates). I think it's easier to write a M script that goes through each row in the query and adds dates between start and end date to new table with second column being the name column value.

Thanks, I wouldn't know what an M script is but as for specifying single values, surely couldn't I use something like SUMX to reference a single row?

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors