Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Ferrol40
Frequent Visitor

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

I have this table 

 

Equipment IDStart Date (dd/mm/yyyy)Start TimeEnd Date (dd/mm/yyy)End TimeWork TypeDowntime
LD105/06/202218:00:0006/06/20226:00:00PM12.0
JB205/06/202218:00:0006/06/20226:00:00PM12.0
JB204/03/20226:00:0130/05/20226:00:00BD2,088.0
TR105/06/20226:00:0005/06/202218:00:00PM12.0
GR220/05/20226:00:0030/05/20226:00:00PM240.0
TR201-02-2222:00:0002-02-222:00:00PM4
TR302-01-2218:00:0003-10-220:00:00BD870
TR401-02-2223:00:0002-02-220:30:00PM1.5
GR201-03-223:30:0001-03-226:00:00BU2.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 🙏

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

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

1.png2.png

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 )
        )
)

 

View solution in original post

tamerj1
Super User
Super User

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

1.png2.png

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"

 

View solution in original post

13 REPLIES 13
tamerj1
Super User
Super User

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

1.png2.png

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"

 

Awesome

v-kkf-msft
Community Support
Community Support

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

vkkfmsft_0-1657695346880.png

vkkfmsft_1-1657695361855.png

 

Best Regards,
Winniz

 

Ferrol40
Frequent Visitor

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

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

1.png2.png3.png

 

v-kkf-msft
Community Support
Community Support

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))

vkkfmsft_0-1655803440198.png

 

vkkfmsft_1-1655803497589.png

 

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.

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
Super User
Super User

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

1.png2.png

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 )
        )
)

 

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

@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

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 

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

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 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors