Spread Hours across days and link to date table to do filters

[Ferrol40]

I have this table 

Equipment ID	Start Date (dd/mm/yyyy)	Start Time	End Date (dd/mm/yyy)	End Time	Work Type	Downtime
LD1	05/06/2022	18:00:00	06/06/2022	6:00:00	PM	12.0
JB2	05/06/2022	18:00:00	06/06/2022	6:00:00	PM	12.0
JB2	04/03/2022	6:00:01	30/05/2022	6:00:00	BD	2,088.0
TR1	05/06/2022	6:00:00	05/06/2022	18:00:00	PM	12.0
GR2	20/05/2022	6:00:00	30/05/2022	6:00:00	PM	240.0
TR2	01-02-22	22:00:00	02-02-22	2:00:00	PM	4
TR3	02-01-22	18:00:00	03-10-22	0:00:00	BD	870
TR4	01-02-22	23:00:00	02-02-22	0:30:00	PM	1.5
GR2	01-03-22	3:30:00	01-03-22	6:00:00	BU	2.5

I want to be able to spread the downtime hours between days (each day is 24hrs) and how do i link it to a date table to do different filters.

I also have tables for Equipment ID and Work type

I have a Date table that i would like to link to this table so that i can filter by weeks, months, calendar days, fiscal periords

Please I want a power query solution 🙏

[tamerj1 · Accepted solution]

Hi @Ferrol40 
My solution is a calculated table using dax. I think is much more simple than power query (at least in this case). Please let me know if that helps. Here is a sample file with the solution https://www.dropbox.com/t/2xPot0KF3QA5mABP

Down Hours = 
GENERATE (
    Data,
    VAR StartDate = Data[Start Date (dd/mm/yyyy)]
    VAR EndDate1 = Data[End Date (dd/mm/yyy)] 
    VAR EndDate2 = EndDate1 + Data[End Time]
    VAR EndDate = IF ( StartDate = EndDate1, EndDate2, EndDate1 )
    VAR Period = DATEDIFF ( StartDate, EndDate, DAY )
    VAR DownHours = Data[Downtime]
    RETURN
        ADDCOLUMNS (
            FILTER ( 'Date', 'Date'[Date] >= StartDate && 'Date'[Date] < EndDate ),
            "Down Hours", COALESCE ( DIVIDE ( DownHours, Period ), DownHours )
        )
)

[tamerj1 · Accepted solution]

Hi @Ferrol40 
This query seems to be able to spliyt the hours perfectly even better than the DAX. You can use the same in excel. Please have a look and let me know if you still need any further assetance. https://we.tl/t-11nFLQMqN7

let
    Source = Excel.Workbook(File.Contents("your_File_Location\downtime_Online.xlsx"), null, true),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Plt No] <> null)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Start Date", type date}, {"Start Time", type datetime}, {"End Date", type date}, {"End Time", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Date", 
each List.Dates([#"Start Date"], 
if Duration.Days([#"End Date"] - [#"Start Date"]) = 0 then 1 
else Duration.Days([#"End Date"] - [#"Start Date"]) + 1, #duration(1, 0, 0, 0))),
    #"Expanded All Dates" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Added Custom1" = Table.AddColumn(#"Expanded All Dates", "DownHours", 
each if [#"Start Date"] = [#"End Date"] then [Downtime]
else if [Date] = [#"Start Date"] then Duration.TotalSeconds (  #datetime (1900,01,01,00,00,00) - [#"Start Time"] )/3600
else if [Date] = [#"End Date"] then Duration.TotalSeconds([#"End Time"] - #datetime( 1899,12,31,00,00,00 ))/3600
else  24 ),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([DownHours] <> 0)),
    #"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows1",{{"DownHours", type number}, {"Date", type date}, {"Downtime", type number}, {"Plt No", type text}})
in
    #"Changed Type2"

[Ferrol40]

Awesome

[v-kkf-msft]

Hi @Ferrol40 ,

Please add the custom column in Power Query.

= let sd = [#"Start Date (dd/mm/yyyy)"], ed = [#"End Date (dd/mm/yyy)"],
starttime = DateTime.From([#"Start Date (dd/mm/yyyy)"] & [Start Time]), endtime = DateTime.From([#"End Date (dd/mm/yyy)"] & [End Time]),datediff= Duration.Days(ed-sd),firhours = Duration.TotalHours(DateTime.From(sd & #time(24,0,0)) - starttime), lasthours = Duration.TotalHours(endtime - DateTime.From(ed)),
mylist = List.Generate(()=>[x=sd,y=starttime,w=1,result=[date = sd, hours =firhours]],each [w] <= datediff,each [z=[x], x=Date.AddDays(z,1) ,y=Duration.TotalHours(x -z) ,w=[w]+1, result =[date = x,hours = y]
],each [result]) & {[date = ed, hours = lasthours]}
in if ed = sd then {[date = ed, hours = Duration.TotalHours(endtime - starttime)] } else mylist

Best Regards,
Winniz

[Ferrol40]

I would really like a power query solution so that i can also use in it power pivot in excel

[tamerj1]

Hi @Ferrol40 
Yes in case of power pivot the power query is your only option. Please refer to sample file with the solution using power query https://we.tl/t-Vu4S2Po9Nw

[v-kkf-msft]

Hi @Ferrol40 ,

Please try adding the custom column in Power Query. Then expand to rows.

= List.Dates([#"Start Date (dd/mm/yyyy)"], Duration.Days([#"End Date (dd/mm/yyy)"] - [#"Start Date (dd/mm/yyyy)"]) + 1, #duration(1, 0, 0, 0))

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @v-kkf-msft 
Greetings and Respect 🙂
This is actually good but in my openion is not so accurate besides we still can see 2088 down hours per day!
I still prefer DAX 😅

[tamerj1 · Accepted solution]

Hi @Ferrol40 
My solution is a calculated table using dax. I think is much more simple than power query (at least in this case). Please let me know if that helps. Here is a sample file with the solution https://www.dropbox.com/t/2xPot0KF3QA5mABP

Down Hours = 
GENERATE (
    Data,
    VAR StartDate = Data[Start Date (dd/mm/yyyy)]
    VAR EndDate1 = Data[End Date (dd/mm/yyy)] 
    VAR EndDate2 = EndDate1 + Data[End Time]
    VAR EndDate = IF ( StartDate = EndDate1, EndDate2, EndDate1 )
    VAR Period = DATEDIFF ( StartDate, EndDate, DAY )
    VAR DownHours = Data[Downtime]
    RETURN
        ADDCOLUMNS (
            FILTER ( 'Date', 'Date'[Date] >= StartDate && 'Date'[Date] < EndDate ),
            "Down Hours", COALESCE ( DIVIDE ( DownHours, Period ), DownHours )
        )
)

[Ferrol40]

Your solution does not make use of the time. only the dates. some equipment could have downtimes of just 1 hour 

[tamerj1]

@Ferrol40 
The downtimes are pre-calculated and available why do we need to recalculate them? Whether it is a 12 hours or one 1 hour downtime, how does that make any difference? Please provide relevant sample of data and accurately provide the expected results based on the same sample of data. Thank you

[Ferrol40]

Thank you for your response. For a Dax solution, that works just fine. Your solution helps. But as I indicated I would like to use this in powerpivot in excel so a powerquery solution would also be really appreciated.

https://1drv.ms/x/s!Arbedp-azDVh9DyMbKoTPeg40SVZ?e=FvmnxM 

Regards

Francis 

[tamerj1]

@Ferrol40 
Did you check my power queru solution above? It gives the same result I guess.

[Ferrol40]

The power Query Solution does not work for the data. there is still 2088 in there. There is no logic for the rest . https://1drv.ms/x/s!Arbedp-azDVh9DyMbKoTPeg40SVZ?e=FvmnxM  

Create a Date table and then link it. I need to be able to just find downtimes for any number of days i select from the date table