Comparing dates cross table

Anonymous — Thu, 10 Feb 2022 16:01:40 GMT

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.

Re: Comparing dates cross table

serpiva64 — Thu, 10 Feb 2022 16:07:50 GMT

Hi,

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

Re: Comparing dates cross table

v-yingjl — Tue, 15 Feb 2022 07:07:05 GMT

Hi Anonymous ,

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

topic Re: Comparing dates cross table in Power Query

Comparing dates cross table

Cross Table comparing date

Re: Comparing dates cross table

Re: Comparing dates cross table