使用 Power Query 將日曆佈局轉換為表格格式

在本教程中，您將學習如何使用 LuckyTemplates 中的 Power Query 將多個日曆類型佈局轉換為表格格式。您將學習如何從文件夾中轉換多個文件而不是單個文件。您可以在本博客底部觀看本教程的完整視頻。

本教程還將教您如何填補數據中的空白以及如何在 LuckyTemplates 中收集它們。

目錄

• 在 Power Query 中準備文件和佈局
• 在 Power Query 中製定計算
• 填補日期中的空白
• 分組分隔表
• 結論

在 Power Query 中準備文件和佈局

這是水平和垂直排列的文件和周的典型佈局。由於行數的變化，不同文件的行數可能不同。

現在，轉到 Power Query 並獲取文件。選擇新來源並單擊更多。然後，單擊全部並選擇文件夾。

選擇參數並按確定。

它將向您顯示文件夾中的文件。您需要在單個結果查詢中將所有文件放在一起。單擊 Combine & Transform 加載數據並創建多個查詢。

接下來，選擇文件夾中的任何文件作為示例文件，然後選擇要啟動的任何項目。

查看 Query1，您可以看到來自 File1 和 File2 的數據。

在 Power Query 中製定計算

Query1 中的數據格式仍然不正確。您可以通過 Transform Sample File 查詢更改它。

如果將該查詢的格式更改為表格形式，它將作為文件夾中所有文件的藍圖。

刪除前 2 行和 Column1。

接下來，填補表格內的空白。轉到轉換選項卡並選擇轉置。選擇包含日期的列並單擊標題。單擊填充並選擇向下。

公式欄中帶有日期的列意味著如果每週的行數不同，結果將不會出現在這些列中。因此，刪除 Applied Steps 部分中的 Filled Down 步驟。

每組表都以包含日期的列開始。您必須執行基本操作才能將它們恢復為表格格式。

通過在公式欄中按f(x)添加手動步驟。然後，輸入Table.ToColumns。這會將每一列變成一個包含列表的列表。

將列表變成表格並添加索引號。您可以從 0 或 1 更改。

填補日期中的空白

您需要填寫的日期仍有差距。但是，向下填充操作不適用於列表。您需要將此列表轉換為表格。

選擇索引列並單擊分組依據。將 Count 替換為 ToTable 以更改列名稱。將 RowCount 更改為 FromColumns 並指向放置列表的 Column1。然後，把它變成一個類型表。

您現在可以看到列表變成了只有一列的表格。

現在，對每個表執行向下填充操作。首先，添加一個自定義列並將其命名為FillDown。

您必須訪問表中第一列的第一個值，因此對於公式，請使用IF。引用包含表的 ToTable 列。輸入 Column1 作為您要引用的列。接下來，在它們之間使用 null 從列表中檢索第一個值。

然後，檢查那是否是一個日期。如果是，則用 Table.Filldown 向下填充該列。在該函數內，輸入 ToTable 和 Column1。但如果不是日期，則輸入 ToTable 返回整個表。

這將為您提供一個包含表格的新列。

查找列日期的放置位置並將它們拆分為單獨的表。為此，您必須將表轉換為列列表。

在公式欄中的 Table.FillDown 和 Else 函數之後，引用 Column1。

這會將您的表格變成帶有一組日期的列表。

接下來，通過更新索引列將列表變回表格。請記住，分隔位置是日期列的位置。因此，添加一個自定義列並將其命名為Index2。然後，檢查列表是否包含日期。

使用IF並輸入 FillDown。您只需檢查列表中的第一個值。如果是日期，則返回 Index。如果不是，請輸入null。然後，填寫該欄。

分組分隔表

在 Index2 列上使用分組依據。這將具有相同 Index2 值的表分組為一個。

您不會計算行數。因此，將 RowCount 更改為 FromColumns 並添加 FillDown 列。然後，將類型更改為表格。

如果您單擊一個表，您將看到其中的列。

接下來，您必須將所有單獨的表附加到一個表中。創建手動步驟並使用 Table.Combine。然後，引用其中的 Count 列。

選擇 Column1 和 Column2。單擊轉換並選擇逆透視其他列。然後它會顯示 4 列。

刪除 Attribute 列，因為它不需要。然後，將列的名稱更改為Date、Movements和Name。將 Date 列的數據類型更改為 Date，並將 Name 列的數據類型更改為 Text。

您現在已將轉換更新為轉換示例文件。

如果轉到Query1，可以看到找不到表的Column1。因此，刪除 Applied Steps 部分中的 Changed Type 步驟。

最後，更改列的數據類型。將日期列的數據類型設置為日期。然後，將 Movements 和 Name 列的數據類型設置為 Text。

您現在已經將數據模型的格式更新並更改為表格形式。

LuckyTemplates 自定義日曆：計算月度變化 – 445 個日曆
LuckyTemplates 中的自定義條件格式設置技術
如何在 LuckyTemplates 中處理多個日期

結論

在您的數據報告中，您必須通過將日曆佈局轉換為表格形式來填補日期表中的空白。

您可以使用 LuckyTemplates 中的 Power Query 轉換多個列。這將使您的報告獲得正確且有價值的見解。

一切順利，

梅麗莎