Forum Discussion
Helper ITurn 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
Hi pierre415,
You can follow below steps to use the query:
1. Click on 'fx' to add custom steps.
Previous step name:
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 startdateRegards,
Xiaoxin Sheng
Hi pierre415,
Click on the arrow button and choose 'expand to new row' option:
Regards,
Xiaoxin Sheng
18 Replies
Z7-852In 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.
pierre415Thanks, 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?
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'.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
- pierre415
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)))
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
- 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