cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mostvp123 Frequent Visitor
Frequent Visitor

Custom Column to Separate Dates Help

Hi!

I am currently working in the query editor with some 'date from' 'date to' data which records activities that people have to do in the future in hours. A sample for the data is as follows:

Ex1.JPG
The data in this format is hard to use, so based on reccomendations from a different forum, I added a custom column which splits up the Date from, Date to columns into separate days as follows (see Date column):

Ex2.JPG

This custom column is added by clicking on "Add Column" and then "Custom Column" in the query editor, with the code: ={Number.From([Date from])..Number.From([Date to])}
This works fine, however as can be seen above, the Hours column is simply repeated and is not divided by the number of new rows. Since the Dates have been split up, the hours must be divided. This is the desired result:Ex3.JPG
I can't seem to figure out a way to do this. Thank you for helping me out Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
AnthonyTilley Established Member
Established Member

Re: Custom Column to Separate Dates Help

you could create a calculated colunm to divide the hours by a count of the activity id 

Column =
Var hours = Table1[Hours]
var a = Table1[Activity]
 Var ca = CALCULATE(count(Table1[Activity]),FILTER(Table1,Table1[Activity] = a))
Var ret = DIVIDE(hours,ca)
Return ret

 
Alternativly if you would prefer to have this already cacluated in teh query then add a second custom colunm before your split down step to divid the hours by the total days (you need to add 1 day to your date to as your times are all midnight so this would mean teh number fo days between 01/01/2019 and 02/01/2019 would be 1 day not 2.
Formula below
= [Hours] / Duration.Days(Date.AddDays([Date to],1)-[Date From])
 
insert this add colunm between your added sutom and expanded date steps
1 REPLY 1
AnthonyTilley Established Member
Established Member

Re: Custom Column to Separate Dates Help

you could create a calculated colunm to divide the hours by a count of the activity id 

Column =
Var hours = Table1[Hours]
var a = Table1[Activity]
 Var ca = CALCULATE(count(Table1[Activity]),FILTER(Table1,Table1[Activity] = a))
Var ret = DIVIDE(hours,ca)
Return ret

 
Alternativly if you would prefer to have this already cacluated in teh query then add a second custom colunm before your split down step to divid the hours by the total days (you need to add 1 day to your date to as your times are all midnight so this would mean teh number fo days between 01/01/2019 and 02/01/2019 would be 1 day not 2.
Formula below
= [Hours] / Duration.Days(Date.AddDays([Date to],1)-[Date From])
 
insert this add colunm between your added sutom and expanded date steps

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 276 members 3,189 guests
Please welcome our newest community members: