Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 startdate
Regards,
Xiaoxin Sheng
Hi @pierre415,
Click on the arrow button and choose 'expand to new row' option:
Regards,
Xiaoxin Sheng
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
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
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
If I wanted to copy those 4 columns into a new table, would you know how to do that?
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 startdate
Regards,
Xiaoxin Sheng
= Table.AddColumn(#"Renamed Columns", "Custom", each List.Dates([Start], Duration.TotalDays([End]-[Start]), #duration(1, 0, 0, 0)))
= Table.AddColumn(#"Renamed Columns", "Custom", each if [Start]=[End] then {[Start]} else List.Dates([Start], Duration.TotalDays([End]-[Start]), #duration(1, 0, 0, 0)))
Hi, how about excluding weekends?
@Jakinta Thank you so much. I really appreciate that this is what I exactly want.
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:
Regards,
Xiaoxin Sheng
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?
Thanks,
Pierre
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.