在 Excel 中附加多個工作表以支持 BI

本教程將討論如何將包含多個工作表的 Excel 文件導入和打開到一個 LuckyTemplates 表中。

此數據 Excel 文件將用於本教程。

該文件包含 60 張具有相同佈局的數據。

本教程的目的是將所有 Excel 工作表加載到 LuckyTemplates，並將債券信息作為一個表檢索。您需要獲取證券代碼並將其添加到單獨的列中。然後，將所有這些附加到一個表中，該表利用 UI 生成您需要的代碼。

目錄

• 將 Excel 工作表導入 LuckyTemplates
• 將安全代碼從 Excel 帶出到 LuckyTemplates
• 刪除空行
• 複製查詢
• 為列分配數據類型
• 將 Excel 中的證券代碼列添加到 LuckyTemplates
• 為債券查詢創建自定義列
• 結論

將 Excel 工作表導入 LuckyTemplates

首先要做的是打開Power Query 編輯器並使用“管理參數”選項創建一個參數來存儲文件位置。

在 Manage Parameters 框中，單擊 New 並輸入FileLocation作為參數名稱。接下來，將 Data Type 設置為Text並將 Suggested Values 設置為Any Value。複製 Excel 文件的文件路徑並將其粘貼到當前值字段中。

按 OK 後，單擊New Source並選擇Excel Workbook。

接下來，選擇並打開 Excel 文件。完成後，導航器屏幕將打開並列出 Excel 文件中的所有工作表。

選擇第一張紙，然後按確定。之後，您將在 Power Query 編輯器中看到該表。

接下來要做的是將硬編碼文件路徑更改為文件參數。打開 Advanced Editor 窗口並將 Source code 中的文件路徑更改為FileLocation。

最好將數據放在 Excel 表格中，因為邊界或數據范圍是在將它們引入Power Query之前定義的。由於該文件包含工作表而沒有表格，因此存在引入空列和空行的風險，因此您需要對其進行排序。

將安全代碼從 Excel 帶出到 LuckyTemplates

了解工作表的佈局是固定的有助於構建解決方案，尤其是當您想要最大化並使用用戶界面生成代碼時。例如，一個要求是添加一個包含證券代碼的列。如果你查看數據，你可以看到代碼。

用戶界面可以幫助您提取代碼的值。右鍵單擊該單元格並選擇添加為新查詢。

在公式欄中，您會看到表名後跟大括號之間從零開始的行號。花括號稱為位置索引運算符。您還會看到用方括號括起來的字段名稱，稱為字段訪問運算符。

使用這些代碼，您現在可以提取值。返回 Bond 表並獲取 Bond 數據。首先，刪除前 8 個八行。單擊刪除行並選擇刪除頂部行。

接下來，在“行數”字段中輸入8 ，然後按“確定”。

然後，單擊“將第一行用作標題”以設置標題。

完成後，債券表現在將如下所示。

在標題的正下方，您會看到一條代表列質量的細線。從那裡，您可以看到列中有相當多的空白。這意味著該文件引入了很多空行。

刪除空行

要移除空行，請點擊移除行並選擇移除空白行。

該轉換生成以下語法：

Record.FieldValues正在獲取表中當前行的所有值作為列表。List.RemoveMatchingItems刪除第一個列表中與第二個列表匹配的所有值。第二個列表僅包含空文本字符串或 null。這些是將從第一個列表中排除的值。

如果所有空文本字符串和 null 都已從包含記錄字段值的列表中刪除，則該列表應該為空，並且 List.IsEmpty 的計算結果為 True。然後，Table.SelectRows將保留 Trues。

您不應該以只有空白行的表格結束。這就是為什麼在List.IsEmpty之前添加關鍵字not的原因。然後返回一個包含非空行的表。

除了空白行，您還需要刪除空白列。但在此之前，請查看刪除列時 Power Query 生成的內容。選擇第四列並單擊Remove columns。

執行轉換後，您將在公式欄中看到此語法。

它調用Table.RemoveColumns函數，然後引用並傳遞 Applied Steps 窗格中的上一步作為第一個參數。轉換進一步傳遞一個列表，其中包含要刪除的列的列名。

複製查詢

現在，複製查詢並在 Applied Steps 窗格中選擇 Promote Headers 步驟。接下來，右鍵單擊該步驟並選擇Delete Until End。

請記住，您可以使用位置索引運算符在一組大括號中傳遞從零開始的行號。因此，通過在公式欄的兩個大括號內輸入0來僅保留第一行。

完成後，債券表將如下所示。

從那裡，重複使用 Removed Blank Rows 步驟創建的部分邏輯，將記錄轉換為列表並刪除 null。返回到 Bond 查詢並選擇 Remove Blank Rows 步驟。接下來，複製此 M 代碼。

返回到重複的查詢並將代碼粘貼到公式欄中。然後，排列一些字符串，使公式看起來像這樣。

您現在已經創建了一個包含要保留的列名的列表。重命名查詢Column Names。

然後，返回債券查詢。由於您已經創建了一個包含所有要保留的列的查詢，因此您需要在Table.RemoveColumns函數中列出要排除的所有列。

使用從 Removed Blank Rows 步驟複製的相同語法更改{Column4} 。也將{“”, null}更改為ColumnNames。

然後，您需要傳遞一個列表，其中包含 Bond 表中的實際列名。將Record.FieldValues(_)更改為Table.ColumnNames()。在括號內輸入#”Removed Blank Rows”以傳遞表引用參數。

為列分配數據類型

接下來要做的是為列分配適當的數據類型。對於日期列，單擊標題旁邊的圖標並選擇日期。

對於 PX_LAST 和 YLD_YTM_MID 列，選擇小數數據類型。

通過這三個查詢，您已經創建了所有構建塊，您可以使用這些構建塊來設計解決Excel文件中所有工作表的解決方案。為此，您需要復制 Bond 查詢並刪除 Applied Steps 窗格中除 Source 步驟之外的所有步驟。

將 Excel 中的證券代碼列添加到 LuckyTemplates

在源步驟中，您可以看到 Excel 文件中的所有數據。您可以使用 Bond 查詢並轉換 Data 列中的嵌套表，而不是為每個工作表創建單獨的查詢。

首先，添加證券代碼。如果單擊任何表格單元格內的空白區域，您將看到該表格內容的預覽。

語句創建一個邏輯來獲取證券代碼。轉到 Column2 查詢並從公式欄中復制證券代碼的地址。

接下來，返回 Bond 查詢並添加一個自定義列。

將列命名為 Security Ticker 並編寫以下 M 代碼。

該公式有一個IF語句，表示如果在 Column1 中找到單詞 Security，它將提供與其相鄰的 Column2 中的單元格的值。否則，將提供 null。

按確定後，一個帶有證券代碼的新列將被添加到表格中。

單擊 Security Ticker 列的下拉按鈕並取消選擇 null 以刪除列中的所有空值。

之後，您將獲得所有工作表中的所有債券信息。您需要做的就是重複在 Bond(2) 查詢中執行的轉換，並將它們應用於數據列中的嵌套表。

為債券查詢創建自定義列

轉到 Bond(2) 查詢，打開“高級編輯器”窗口，然後復制以下代碼：

接下來，返回 Bond 查詢並創建另一個自定義列。由於您需要跨多個步驟應用多個轉換，因此您必須使用let語句。因此，輸入let並粘貼高級編輯器中的代碼。

然後，將Bond_Sheet更改為Data以轉換 Data 列中的表。

按確定後，表格中將添加一個新列。

如果單擊該列任何單元格內的空白區域，您將看到來自 Bond(2) 查詢的數據。

您只需要在 Bond 查詢的最後兩列中。因此，選擇證券代碼和自定義列，然後從移除列功能區中單擊移除其他列。

展開自定義列並取消選中使用原始名稱作為前綴框。

完成後，驗證表。在表下方，選擇基於整個數據集的列分析選項。

在該轉換之後，數據列中將出現錯誤。

因此，通過單擊“數據”列並在“保留行”功能區中選擇“保留錯誤”來檢查錯誤。

如果單擊數據列中的錯誤值，您可以閱讀以下錯誤消息：

要解決這些問題，請先刪除 Kept Errors 步驟。單擊數據列並從移除行功能區中選擇移除錯誤。

然後，將列分析選項設置回前 1000 行。就是這樣！

結論

這種用戶界面驅動的解決方案可以幫助您將 Excel 文件中的多個工作表附加到 LuckyTemplates。該解決方案無需創建 60 個單獨的查詢並重複執行所有轉換，而是可以創建一個執行所有轉換的查詢。利用並最大限度地利用此解決方案來構建出色的數據報告。

梅麗莎