使用 Power Query 公式為每個日期創建值

在本教程中，您將學習如何在 LuckyTemplates 中使用Power Query公式為每一天創建值。

為日期創建值是在 LuckyTemplates 中學習的一項重要技術。它可以為您提供您想要在模型中全天查看的數據。該技術還涵蓋了列之間的間隙。

當您的日期表缺少信息時，這是一個很好的工具。

以下是有關如何操作的分步指南：您可以在本博客底部觀看本教程的完整視頻。

目錄

• 設置附加值
• 為 ID 創建過濾條件
• 為日期添加過濾條件
• 檢查並排序日期列
• 使用 Table.FirstN 函數
• 獲取兩個日期值之間的日差
• 確定權重步驟
• 填補空缺
• 獲取日期的重量投影
• 結論

設置附加值

對於包含 ID、日期和權重的數據集，您需要填補這些度量之間的空白。這可以通過創建一個日期範圍來分散幾天的差異來完成。

為此，請轉到 Power Query 查看您的表。

您需要將數據的副本加載到內存中。為此，您必須使用Table.Buffer度量。

如果單擊 TableBuffer，您將看到度量的作用。

接下來，返回您的數據集並單擊公式欄中的fx 。您需要在度量中添加 Table.Buffer。

然後，將此步驟重命名為BufferedTable。

接下來，添加自定義列。

為 ID 創建過濾條件

在此 Power Query 公式中，將引用的步驟從BufferedTable更改為RenameColumns，因為應首先檢查 BufferedTable。

要檢查 BufferedTable，請使用 Table.SelectRows ，它會返回一個與所選條件匹配的表。

因此，對於公式中的每個表達式，使用 Table.SelectRows，然後使用 BufferedTable。

作為篩選條件，使用自定義結束函數，並為 Buffered Table 輸入 BT。檢查 ID 列是否等於當前記錄中的 ID。接下來，使用下劃線作為訪問運算符訪問當前記錄，然後引用相同的 ID 列。

這將為您提供一個僅包含與當前記錄匹配的 ID 的過濾表。 

為日期添加過濾條件

您需要過濾日期列，因為您只需要等於當前記錄的日期。

使用這些函數檢查 BT 日期列：

此列需要大於或等於當前記錄的日期。

這樣，表格將包含兩行而不是三行：

檢查並排序日期列

您需要使用Table.Sort檢查日期列的排序是否正確。函數 Table.Sort 根據您想要的標准在一列或多列上對錶格進行排序。

此示例中的語法非常複雜，因此您必須對錶主題進行向下鑽取。

您需要右鍵單擊一個表並將其添加為新查詢。

單擊它會為您提供包含該表的自定義查詢。

接下來，單擊日期列，然後選擇升序排序。

複製整個公式：

返回您的主要查詢並將其粘貼到公式欄中：

您必須刪除 Table.Sort 的引用，因為需要傳遞參數的表應該是 Table.SelectRows 的結果。

完成後，將排序條件添加回去。應用更改後，您的表格現在已按日期正確排序。

使用 Table.FirstN 函數

對於此步驟，您只需要 2 條記錄而不是 3 條。

要解決此問題，請返回自定義查詢。單擊保留行，然後單擊保留頂行。

在空白處輸入 2 作為行數。

您會注意到 Table.FirstN 函數出現在公式欄中。Table.FirstN 根據 countOrCondition 的值返回表的第一行。

插入該函數，以及您在主查詢中需要的行數。在末尾包括函數類型表。

集合中的每個表對象現在將有 1 行或 2 行。

獲取兩個日期值之間的日差

下一步是獲取日期值之間的天數並提取第二條記錄上的日期。

從添加自定義列開始。將其命名為“天”。然後，遵循這個 Power Query 公式：

語法確保表中有 2 條記錄。如果只有 1，則天差為 0，因為它沒有下一條記錄。如果有 2，您可以從日期值中獲得差異。

接下來，像這樣在公式中添加類型編號。

如您所見，從 2017 年 9 月 14 日到 2018 年 10 月 4 日之間有 385 天的差異。

確定權重步驟

接下來，您必須計算每天的體重應該是多少。

您需要再次添加自定義列，將其命名為 WeightStep，並複制此 Power Query 公式：

語法檢查表是否有超過 1 行，從當前記錄中減去權重，並將差值除以天數。

同樣，在公式中添加類型數字，這樣您就可以看到每天的體重。

填補空缺

現在您已經有了天數，您可以創建一個日期列表來填補數據集中的空白。

首先，添加另一個自定義列並將其命名為DateKey。然後，遵循這個 Power Query 公式：

該公式檢查是否存在日期差異。如果有，它會在當前記錄的開始日期之間創建一個日期列表，並將其擴展天數。但如果沒有任何差異，它會返回日期。

這是結果：

沒有任何差異的表主題返回其日期；做的表，得到了日期列表。

如果將 DateKey 擴展到新行，您可以看到所有日期的記錄。

獲取日期的重量投影

創建日期列表後，您需要獲得日期的權重預測。

首先，添加一個自定義列，並將其命名為WeightProjection。然後輸入這個 Power Query 公式：

該語法獲取權重值，將其添加到 DateKey 值，從日期值中減去它，然後將其乘以 WeightStep。

同樣，將類型編號添加到公式的末尾。

您現在有了每個日期的體重預測。

使用下面的公式刪除輔助列：Custom、Days 和 WeightStep，並將 DateKey 列的格式更改為 Date。

結論

在本教程中，您學習瞭如何通過在每個日期列表中創建值來填補數據集中的空白。

這些日期值可幫助您從模型中獲得有價值的見解。如果您的表格缺少信息，您可以使用此技術使它們更加全面。

您的表中可能遺漏了重要數據，因此請始終最大化每一行和每一列。

一切順利，

梅麗莎