LuckyTemplates Between Dates Working Hours 解決方案

在今天的博客中，我將演示 Access Analytic 的Start Stop Challenge方法，其中使用 LuckyTemplates 計算日期之間的總工作時間。您可以在本博客底部觀看本教程的完整視頻。

任務是計算員工在每個月和每個日期的總小時數。我提出了可以跨越數月的開始和結束日期，並考慮到處理可能的拼寫錯誤的狀態。 

目錄

• LuckyTemplates 日期間工作時間的數據集
• 過濾行
• 添加自定義列
• LuckyTemplates 日期間工作時間：變量
• LuckyTemplates 日期間工作時間：預測
• LuckyTemplates 日期間工作時間：聚合值
• 結論

LuckyTemplates 日期間工作時間的數據集

讓我們轉到 Power Query 編輯器。 

這是我們應對這一挑戰的數據。我們有一個表，其中包含員工姓名、開始日期、開始時間、停止日期、停止時間和狀態列。

過濾行

讓我們從根據行的狀態選擇行開始，查詢編輯器可以幫助我們處理這個邏輯。單擊狀態旁邊的下拉箭頭。選擇文本過濾器並選擇不以開頭。 

在將顯示的Filter Rows窗口中，寫入“e”。然後單擊“確定”。

什麼都不會發生，這很好，因為我們在數據中寫了一個小寫的“e”和以大寫的“E”開頭的“Exclude”。

為了解決這個問題，我們將使用Text.StartsWith的可選第三個參數。在公式欄中，轉到Text.StartsWith 函數的末尾。添加一個逗號，讓我們輸入Comparer.OrdinalIgnoreCase。單擊複選標記，這應該會刪除排除行。

當然，我們可能會有人錯誤地輸入了沒有首字母“E”的“Exclude”。在這種情況下，我們可以創建一些額外的邏輯來適應這種情況。 

我們可以通過擴展我們的函數並使用Text.Contains來測試單詞是否不包含“X” 。 為此，添加或 Text.Contains ([Status], “x”, Comparer.OrdinalIgnoreCase。

請注意，與第一個子句相比，我們用“x”替換了“e”。我們一直忽略大小寫，並在我們的兩個子句周圍加上一組括號。單擊複選標記，我們應該得到一個沒有包含Exclude或Xclude狀態的行的表。

添加自定義列

現在讓我們進入這個挑戰的主要部分。我們將使用 UI 添加自定義列，然後切換到編輯器。 

從表格左上角的迷你表格圖標開始。單擊它並在下拉列表中選擇Add Custom Column。 

在這一點上，我們只對帶來我們稍後需要的數據感興趣。因為我們將處理多個字段，所以讓我們使用記錄初始值設定項創建一條記錄，用方括號表示。 

讓我們創建變量並分配可用的列。首先鍵入SD，我們將其等同於開始日期。我們可以通過單擊右側列中的開始日期來完成此操作。

我們將對要分別分配給Start Time、Stop Date和Stop Time的變量ST、ED和ET執行相同的操作。然後，用右括號關閉記錄。單擊確定。

帶有記錄的自定義列將添加到表中。單擊Record旁邊的空白區域，將打開一個預覽窗格。這將引入該行中的所有值。

LuckyTemplates 日期間工作時間：變量

然後我們可以通過高級編輯器窗口格式化我們的變量。這就是高級編輯器窗口的外觀。 

然後，讓我們將變量放在單獨的新行中。

現在，我們可以擴展我們需要的邏輯。因此，我們也可以在這裡使用嵌套的 let 表達式來代替記錄。請注意，其中任何一個都可以工作，因為我們有多個變量或字段名稱，並為其分配了值。

我們的開始日期目前寫為文本值，我們需要將其轉換為適當的日期格式。為此，將函數Date.FromText包裹起來。 

對於同樣格式化為字符串的開始時間，我們需要將其轉換為數字。然後，我們可以將它除以一百並四捨五入以去除任何小數位。 

使用Number.From函數，將Start Date值除以100。然後，將它們包裝在下一個函數Number.Round中，並在末尾添加, 0以四捨五入，不帶小數位。 

對於結束日期和結束時間，我們將分別複製開始日期和開始時間中的相同函數，並用適當的變量替換它們。

一旦變量格式正確，在我們的記錄中創建另一個字段用於一些額外的邏輯。按Enter 鍵創建一個新行。 

我們需要構建一個從第一個日期到結束日期的日期列表。我們將這些日期稱為LD並且我們將使用函數List.Dates。 

此函數的第一個參數是開始日期，這將是我們的SD。然後，它需要一個數字計數 或開始日期和結束日期之間的差值。我們可以使用Number.From函數後跟 ( ED – SD) + 1來得到它。請注意，下一組屏幕截圖中未顯示“+1”，但應該有+1。

第三個參數稱為持續時間步長，我們希望增加一天。我們可以使用 Duration.From (1) 得到它。 

我們創建了一個列表，其中包含從第一個開始日期到停止日期的日期。我們接下來要做的是創建一個與之相符的時間列表。 

我們稱此LT為列表時間。LT 可以有一天的時間段，這與跨越多天的情況相比需要稍微不同的邏輯。因此，我們必須為此創造條件。 

我們希望條件說“如果我們的開始日期與結束日期相同，則結束時間減去開始時間。” 結果應該是格式列表，因此我們使用由{}表示的列表初始值設定項。 

現在，如果我們有一個跨越多天的日期範圍，那麼第一個日期將是 24 減去開始時間。我們將把它作為else語句添加到我們的條件中。 同樣，使用大括號將其格式化為列表。

在這一點上，我們還沒有考慮其他整天，所以我們將使用與符號 (&) 來附加多個列表。對於每一天，創建一個列表，其值 24 表示我們一天有 24 小時。我們將為此 使用List.Repeat 。

使用List.Repeat函數，創建一個包含 24 的列表，並通過計算LD中的天數重複多次。為此，請使用List.Count (LD)   ，然後減去 2，因為我們有一個單獨的列表作為開始日期，我們將為結束時間創建另一個列表。 

本質上，這將創建一個列表，其中每一天只有 24 小時。 

對於結束時間，我們可以使用＆符號將其再次附加為列表，然後，使用列表初始值設定項，讓我們調用 ET。 

到目前為止，我們有兩個大列表——一個日期列表和一個時間列表——它們的長度彼此相等。從這兩個列表中，我們可以構建一個表。 

在新的一行中，為我們將調用t的表創建另一個變量並使用函數Table.FromColumns。此功能需要列表作為列表，我們將使用我們的LD。 

我們的LD包含從開始日期到結束日期的各個日期。我們可以通過傳遞LD並調用Date.EndOfMonth函數將該日期列表轉換為月末日期值。然後在下一行中，讓我們也 獲取LT 。

按Enter 鍵創建另一行並將我們的表設置為table 類型。我們的表將有兩列，第一列是日期列。因此，創建一個Date列並調用Date.Type函數。

第二列將是我們的Hours列，它將是一個整數。因此，我們將創建一個Hrs列並調用Int8.Type。

我們用右括號來關閉我們的記錄，並用右括號來關閉我們的Table.AddColumn函數。單擊完成，我們將在自定義列下獲得記錄列表。 

再次單擊每個記錄旁邊的空白區域，表格應在屏幕底部打開。 

請注意，我們在最後一行有我們的t 表。讓我們嘗試深入研究其中一條記錄。右鍵單擊第一個結果並添加為新查詢。 

我們將獲得與之前相同的摘要，如果我們單擊 Table ，我們應該獲得Date和Hrs值。

但我們主要對最終輸出表感興趣。為了專注於此，讓我們通過右鍵單擊Custom並選擇Delete來刪除幫助查詢。通過在彈出窗口中單擊刪除來確認。

刪除後，我們可以返回到初始查詢並再次單擊Record 旁邊的空白區域。它將顯示與之前相同的結果。但是這一次，讓我們展開公式欄並在右括號旁邊調用[t] 。

在 Custom 列中，我們可以看到每個Record都更改為Table。 

LuckyTemplates 日期間工作時間：預測

從我們正在查看的表中，只有兩列感興趣——員工姓名和我們的自定義列。我們可以使用投影從該表中單獨保留這兩列。 

轉到公式欄，在我們的右括號旁邊，通過將它們放在方括號內來選擇我們要保留的字段。然後按複選標記。

我們應該得到這個結果。

我們可以通過單擊Custom 旁邊的箭頭圖標來展開嵌套表格。取消選中使用原始列名稱作為前綴，然後單擊確定。

我們的表應該是這樣的。

LuckyTemplates 日期間工作時間：聚合值

現在剩下要做的就是聚合這些值。為此，請選擇“員工姓名”列並轉到“轉換”。 

在彈出窗口中，選擇Values Column下的Hrs，因為這是我們要聚合的內容。然後，選擇聚合值函數下的總和。然後單擊“確定”。

我們的最終輸出將如下所示。 

在 LuckyTemplates 中創建日期表LuckyTemplates 中非標準日期表的最快時間比較
DateTime 值：如何去除秒

結論

在此博客中，您看到了一種應對 Access Analytic 的 Start Stop Challenge 的方法。您學習瞭如何計算每個員工一個月的總工作小時數，以及如何處理數據中可能出現的拼寫錯誤。 

使用此技術和 LuckyTemplates，可以在您自己的組織中輕鬆計算日期之間的工作時間，或者作為一種實踐來加深您的 LuckyTemplates 知識和技能。