The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
BuyOrder
BuyOrderRow
Solved! Go to Solution.
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
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
Hi,
can you supply some sample data? the exemple you posted is good but is not possible to get data