Forum Discussion

DanielHolland's avatar
DanielHolland
Frequent Visitor
4 years ago
Solved

Working Days Between Two dates in two Tables

Hello Guys,    I have two Tables, Table 1 contains an "Open Date" ,  Table 2 Contains "Escalated Date"     I would like to calculate the workings days which have passed between the "Open Date" a...
Need Help
Tips and Tricks
  • jennratten's avatar
    jennratten
    4 years ago

    Here is another file that is simplified with the table and column names matching your examples so it should be easier for you to follow.  I have also included step-by-step instructions of what to do to integrate this with your script (text in green font shown in the snip below).  Below is a snip from the Advanced Editor view of Table2.  Essentially you will copy/paste into your query and then make a couple of edits.

     

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

Code for Table1 (Make sure that you name this query to Table1 as it gets referred with this name in Table2)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU1UtJRMtE3NNU3MlKK1YlWMjc3A4sYG4BFYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket ID" = _t, #"Opened Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket ID", Int64.Type}, {"Opened Date", type date}})
in
    #"Changed Type"

Code for Table2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU1UtJRMtE3tNA3MjAyUorViVYyNzcDixkbQMViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket ID" = _t, #"Escalated Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket ID", Int64.Type}, {"Escalated Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Opened Date", each Table1{[Ticket ID=[Ticket ID]]}[Opened Date]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Working Days Passed", each List.Sum(List.Transform(List.Dates([Opened Date],Duration.Days([Escalated Date]-[Opened Date]),#duration(1,0,0,0)),each (1-Number.From(Text.Contains("SatSun",Text.Start(Date.DayOfWeekName(_),3))))))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Opened Date"})
in
    #"Removed Columns"