Forum Discussion
Distributing Hours over Months using START/END dates/time.
I think it's because you are adding this as a custom column. Not as a function like I told you.
From New Source select Blank Query. Name this query on the left box (like changing the name of the source table). Then open Advanced Editor (it's a button next to the Refresh Preview)and paste this:
let getParameters = (StartDate, EndDate) => let NullStart = StartDate = "" or StartDate = null, Start = if NullStart then null else Date.From(StartDate), NullEnd = EndDate = "" or EndDate = null, End = if NullEnd then null else Date.From(EndDate), CountDays = if NullEnd or NullStart then 1 else Duration.Days(End-Start) + 1, DateList = if NullStart then {null} else List.Dates(Start, CountDays, #duration(1, 0, 0, 0)), #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error), #"Add Custom Start DateTime" = Table.AddColumn(#"Converted to Table", "Start datetime", each if NullStart then null else if [Date] = Start then StartDate else DateTime.From([Date])), #"Add Custom End DateTime" = Table.AddColumn(#"Add Custom Start DateTime", "End datetime", each if NullEnd then null else if [Date] = End then EndDate else [Date] & #time(23,59,59)), #"Change Types" = Table.TransformColumnTypes(#"Add Custom End DateTime",{{"End datetime", type datetime}, {"Start datetime", type datetime}}), #"Duration" = Table.AddColumn(#"Change Types", "Duration", each Duration.TotalMinutes([End datetime]-[Start datetime])/60, type number), #"Remove Date" = Table.RemoveColumns(#"Duration",{"Date"}) in #"Remove Date" in getParametersAfter doing that you should see something like this:
Then go to your table and from ribbon select new column > invoke custom function:
Select column name, query name that you have provided in previous steps and the start and end columns (example on screenshot).
And you should know the rest. 🙂
Hi & Hello ShaneL79,
I can't help you with spliting it with eom, but I can help with spliting into days with counting durations.
Step 1. New Source > Blank Query
Step 2. Create a funtion splitting_datetime_to_datetime_with_nullend
= (StartDate as datetime, EndDate) =>
let
Start = Date.From(StartDate),
NullEnd = EndDate = "" or EndDate = null,
End = if NullEnd then null else Date.From(EndDate),
CountDays = if NullEnd then 1 else Duration.Days(End-Start) + 1,
DateList = List.Dates(Start, CountDays, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
#"Add Custom Start DateTime" = Table.AddColumn(#"Converted to Table", "Start datetime", each if [Date] = Start then StartDate else DateTime.From([Date])),
#"Add Custom End DateTime" = Table.AddColumn(#"Add Custom Start DateTime", "End datetime", each if NullEnd then null else if [Date] = End then EndDate else [Date] & #time(23,59,59)),
#"Change Types" = Table.TransformColumnTypes(#"Add Custom End DateTime",{{"End datetime", type datetime}, {"Start datetime", type datetime}}),
#"Duration" = Table.AddColumn(#"Change Types", "Duration", each Number.RoundUp(Duration.TotalMinutes([End datetime]-[Start datetime])/60), type number),
#"Remove Date" = Table.RemoveColumns(#"Duration",{"Date"})
in
#"Remove Date"
HERE COME BACK TO YOUR TABLE THAT YOU WANT TO SPLIT DATES.
Step 3. In your data make sure that START TIME and END TIME is datetime type.
Step 4. From a Ribbon Add Column select Invoke Custom Function
Name of the new column is optional. Select created function and set up parameters with your START TIME and END TIME columns.
Step 5. From a SplittingDates column expand all columns with disabled option "Use original column name as prefix"
Step 6. Remove old columns. And this is what you get:
Step 7. Create year and month column from Start datetime. 🙂
I made it most of the way so far. I have a couple of questions though.
I did not modify the query at all. So the start/end dates are not linked to my data. Is this correct?
When creating the query it asks me to define start time and an optional end time. I created a start time of 1/1/2017 but left the end time blank. In the table that was created only a single row was created. I then tried entering an end date into the future 12/31/2030 and the table populated by day. Do I need to specify an end date?
Once the steps you provided are complete, how do I link in the actual data/measures so those start/end dates are used for durations?
Hi ShaneL79,
I think you've missed the Step 3 or I didn't write it correctly. You have a table (let's call it fact_data) so in that fact_data with fields that you already have prom a Ribbon > Add Column select Invoke Custom Function and provide a columns that represents your startdate and enddate (enddate can be nullable).
Try with that and let me know if you will find more issues with description that I've provided.
You were right. I missed the "YOUR TABLE" part of step 3.
Now doing it that way I get an error when trying to expand the table (re: Step #5). The error is:
Expression.Error: We cannot convert the value null to type DateTime.
Details:
Value=
Type=[Type]Any ideas? I did confirm that my START TIME and END TIME were date/time types.
All events where there is no [START TIME] values show as "Error". The others show "Table" prior to performing that step. However, when I do that step the above error pops up.
Lastly, do I need to remove the old columns? They are still connected to several dozen other measures and reports, so I would ideally like to keep them and just have the new ones added to that is possible.
Getting closer though.. I appreciate your help walking me through, and I apologize for my beginner level understanding of PowerBI.
