使用查詢編輯器在 LuckyTemplates 中將日曆類型佈局轉換為表格格式

如果排列不正確，分析我們擁有的數據可能會很困難。在本教程中，我將解釋如何將垂直和水平重複數據轉換為表格格式。我們這樣做是為了更輕鬆地使用 LuckyTemplates 對其進行分析。您可以在本博客底部觀看本教程的完整視頻。

本教程旨在解決  成員的問題。該成員要求提供一種方法，使他可以將他的數據從日曆類型佈局轉換為表格格式。

在我們開始之前，讓我們先檢查一下我們擁有的數據。

在這裡，我們有一個數據佈局，其中日期是水平設置的，星期是垂直設置的。我們大多數人都熟悉這種排列方式，因為這是我們可以輕鬆理解數據的方式。

然而，這種類型的佈局更難分析，因為需要考慮許多列和行。

話雖如此，讓我們將數據轉換為適當的表格格式，其中我們最終得到一列日期、一列運動和另一列姓名。

目錄

• 清理數據以獲得正確的表格格式
• 打破桌子
• 確定新表格的列表以獲得正確的表格格式
• 確定哪些值屬於表
• 為適當的表格格式對數據進行分組
• 附加單獨的表
• 結論

清理數據以獲得正確的表格格式

為了使事情變得更容易，讓我們在Power Query 編輯器中轉換我們的數據。但在我們這樣做之前，讓我們確保公式欄在您的屏幕上可見。如果不是，請轉到“查看”選項卡，然後選中“公式欄”旁邊的框。

第一步是刪除數據中不必要的值。讓我們刪除數據的第一列和前兩行，因為我們並不真正需要它們。為此，請單擊“刪除行”選項卡，單擊“刪除頂行”，輸入 2，然後按“確定”。

現在要刪除第一列，請轉到“選擇列”選項卡，然後通過取消選中它旁邊的框來取消選擇第 1 列。

我們還刪除索引 14 到 16 的行，因為它們完全被空值填充。再次，轉到Remove Rows選項卡，然後單擊Remove Blank Rows。

我們遇到的另一個問題是日期值每次都會跳過一列。日期後面總是跟一個空值，如下所示。這不僅發生在頂行，也發生在下面的行。

為了填補這些空白，讓我們首先轉置我們的數據表，方法是轉到 “轉換”選項卡，然後單擊“轉置”按鈕。

執行該操作後，表格現在看起來應該是翻轉過來的。

接下來，選擇所有包含日期​​的列，右鍵單擊列標題，然後選擇向下填充。

這應該複製日期並填補所選列中的空白。

打破桌子

查看我們現在擁有的數據，我們看到有四個表彼此相鄰。第 1 至 7 列形成一個表，第 8 至 13 列形成另一個表，依此類推。

要將這些附加到一個表中，我們需要執行基本的逆透視操作以將其轉換為適當的表格格式。但由於表格彼此相鄰，我們不能簡單地這樣做。我們必須拆開這張桌子，然後將它們拼接成更小的桌子。

為此，我們將為本教程創建第一個公式。讓我們單擊fx按鈕，然後添加我們將要使用的公式。我們將使用Table.ToColumns操作。這將從表中創建列值的嵌套列表列表。每列將變成一個列表。

這就是我設置公式的方式。

該公式創建了我們之前擁有的列的列表。列表的第一個值包含第 1 列，第二個值包含第 2 列，依此類推。

確定新表格的列表以獲得正確的表格格式

現在，我們應該確定列表中的哪些值包含日期值。一旦我們知道這些日期的位置，我們就可以確定每個單獨表格的開始或結束位置。

為此，我們首先通過單擊 左上角的 To Table 按鈕將列表轉換回表格。

然後在它旁邊添加一個索引列。

接下來我們要做的是在索引列旁邊添加另一列。此列應幫助我們確定 Column1 上的哪些值包含日期值。我們將此列命名為 Index2。

為此，我們將在 Index2 中加入一個公式，如果它包含日期，它會返回 Column1 的索引值。

我們將使用，然後引用 Column1。括號將用作運算符；在這些括號之間添加 0 意味著我們從列表中獲取第一個值；然後我們確定它是否是一個日期。如果是，我希望公式返回索引號；如果不是，我根本不希望它返回任何值。

由於 Column1 中的第一個值包含日期，因此 Index2 中的第一個值應包含其索引號 0。此外，由於 Column1 中的第二個值不包含日期，因此 Index2 中的第二個值應僅包含空值。

這就是我為 Index2 設置公式的方式。只需確保您的公式中沒有語法錯誤。

這樣做之後，我們的 Index2 列現在應該具有 Column1 中包含日期的值的索引。由於我們設置的公式，它還將包含一些空值。

確定哪些值屬於表

現在，Index2 中的這些索引值指示我們擁有的單獨表的開始和結束。即，0 表示第一個表的開始，7 表示第一個表的結束，第二個表的開始，依此類推。

為了幫助我們識別 Column1 中的哪些值屬於同一個表，讓我們在新列中應用向下填充操作。

執行該操作後，空值現在應替換為零、七、十三和二十。這些數字表明它們屬於同一張表。也就是說，零點形成一張表，七點形成另一張表，依此類推。

為適當的表格格式對數據進行分組

由於我們已經知道 Column1 中的哪些值屬於同一個表，現在讓我們將它們組合在一起。為此，我們將使用Group By 操作。

通過這樣做，我們現在在 Index2 列中只有 4 個值。請注意，另一個名為 Count 的列位於我們的 Index2 列旁邊。此列計算 Index2 列中每個值的行數。

然而，這並不是我們真正想要做的。我們需要做的是使用我們擁有的列形成一個新表。

為此，我們將使用Table.FromColumns操作，引用包含列表值的列 Column1，然後將返回類型更改為表。

重要的是要確保我們在公式中引用了正確的列。檢查 Column1 是否真的包含列表值。

這會將 Column1 中 Index2 值為 0 的所有值拼接到一個表中，將 Index2 值為 7 的值拼接到另一個表中，依此類推。

我們檢查每條記錄的值為 0，然後從 Column1 中檢索列表值。最後，我們使用這個 M 代碼將這些列表拼接起來形成一個表格。

我們抓取前 7 列並將它們縫合到一個表中，抓取接下來的幾列，將它們縫合到另一個表中，依此類推。

附加單獨的表

現在我們已經將它們組合在一起，讓我們嘗試通過添加另一個步驟來附加所有表。同樣，讓我們按fx按鈕添加一個新公式。

Table.Combine操作合併一系列表，然後從中創建一個表。在這裡，我們將引用包含我們的表列表的列，即 Count 列。

我們現在有一個表，其中所有嵌套表都被追加回一個大的單個表中。

在追加所有表格後，我們現在可以執行最後幾個步驟，將我們的數據轉換為適當的表格格式。選擇表格的前兩列，然後單擊“轉換”選項卡中的“取消透視其他列”按鈕。

之後，刪除屬性列，因為我們並不真正需要它。轉到選擇列，然後取消選擇屬性列。

最後，設置所有列的正確類型並正確標記它們。

雙擊每列的列標題以相應地更改它們的名稱。

對於第一列，將其類型更改為日期，因為它包含我們的日期值。

然後，選擇第二列和第三列，並將它們的類型設置為文本。

全做完了！這就是我們正確的表格格式應該是什麼樣子的。

結論

在本教程中，我們將一些以日曆類型佈局呈現的數據轉換為適當的表格格式。我們首先清理我們的數據，將其分解成更小的表，將屬於一起的表分組，然後最後將它們全部附加起來。

本教程為您提供了大量有關如何正確建模數據以便於分析的知識。

理解此處使用的概念非常重要，因為它們也可以以許多其他方式應用。

如果您仍然不清楚這些概念，查看下面的鏈接可能會對您有所幫助。

享受這個工作吧！

梅麗莎