Comparing dates cross table

[wsm]

Cross Table comparing date

For a project, i'd like to check the difference between dates. I want the answer in hours and hours in the weekend shouldn't count.

It's for orderstatus checking. I'd like to know if my supplier is on time. therefor i'd like to know if they confirmed my order.

An order is confirmed when the BuyOrderRow[ExpectedDeliverydate] isn't blank or isn't equal to the Order[CreatedAt]. 

I only want the orders that are older than 72hours. BuyOrder[CreatedAt] > 72Hours 

My tables - BuyOrder, BuyOrderRow are connected by BuyOrderId in BuyOrderRow.

BuyOrderBuyOrderRow

[v-yingjl · Accepted solution]

Hi @wsm ,

Have created a sample table query that you can try it:

BuyOrder table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLUNzTQN7JUMLEysbQyMVUI8FWK1YlWMkKVNbUyNLQyMoTJGuOVNcEmawGTNcUia2wElo0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BuyOrderId = _t, CreatedAt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BuyOrderId", Int64.Type}, {"CreatedAt", type datetime}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"BuyOrderId"}, BuyOrderRow, {"BuyOrderId"}, "BuyOrderRow", JoinKind.LeftOuter),
    #"Expanded BuyOrderRow" = Table.ExpandTableColumn(#"Merged Queries", "BuyOrderRow", {"ExpectedDeliverydate"}, {"ExpectedDeliverydate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded BuyOrderRow", "Confirmed", each if [ExpectedDeliverydate] <> null and [ExpectedDeliverydate] <> [CreatedAt] then 1 else 0,Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "HourDiff", each if Number.RoundDown(Duration.Days(Duration.From([ExpectedDeliverydate]-[CreatedAt]))) * 24+ Duration.Hours(Duration.From([ExpectedDeliverydate]-[CreatedAt])) - 48 > 72 then 1 else 0, Int64.Type),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom1", {{"HourDiff", 0}}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([Confirmed] = 1) and ([HourDiff] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"ExpectedDeliverydate", "Confirmed", "HourDiff"})
in
    #"Removed Columns"

BuyOrderRow table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLUNzTQN7JUMLEysbQyMVUI8FWK1YlWMgLKghnGcGVG+sYKhkZWBgZApOAIUWYClzXUNzLDkDZFlkaVjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BuyOrderId = _t, ExpectedDeliverydate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BuyOrderId", Int64.Type}, {"ExpectedDeliverydate", type datetime}})
in
    #"Changed Type"

Best Regards,
Community Support Team _ Yingjie Li

[serpiva64]

Hi,

can you supply some sample data? the exemple you posted is good but is not possible to get data