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

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

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

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

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

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

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

目錄

將 Excel 工作表導入 LuckyTemplates

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

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

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

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

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

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

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

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

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

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

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

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

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

將安全代碼從 Excel 帶出到 LuckyTemplates

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

刪除空行

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

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

該轉換生成以下語法:

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

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

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

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

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

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

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

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

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

複製查詢

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

為列分配數據類型

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

為債券查詢創建自定義列

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

結論

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

梅麗莎

Leave a Comment

在 Power Automate 中執行直到循環控制

在 Power Automate 中執行直到循環控制

了解有關 Do Until 循環控制如何在 Power Automate 流中工作的基礎過程,並熟悉所需的變量。

使用 Deneb 為 LuckyTemplates 製作自定義視覺對象

使用 Deneb 為 LuckyTemplates 製作自定義視覺對象

受限於 LuckyTemplates 中提供的標準視覺效果?學習使用 Deneb 和 Vega-Lite 為 LuckyTemplates 創建自定義視覺對象,提升您的數據可視化能力。

在 Power Automate Desktop 中將日期添加到文件名

在 Power Automate Desktop 中將日期添加到文件名

在此博客中,您將了解如何使用 Power Automate Desktop 自動將日期添加到文件名的開頭或結尾。

Power Query:如何快速添加註釋

Power Query:如何快速添加註釋

找出幾種不同的方法在 Power Query 中添加註釋,這對於不喜歡記筆記的人非常有幫助。

Power Apps – SharePoint 集成指南

Power Apps – SharePoint 集成指南

在此博客中,您將了解 MS Power Apps 界面的基礎知識並了解 Power Apps-SharePoint 集成的過程。

什麼是 Python 中的自我:真實世界的例子

什麼是 Python 中的自我:真實世界的例子

什麼是 Python 中的自我:真實世界的例子

如何在 R 中保存和加載 RDS 文件

如何在 R 中保存和加載 RDS 文件

您將學習如何在 R 中保存和加載 .rds 文件中的對象。本博客還將介紹如何將對像從 R 導入 LuckyTemplates。

回顧前 N 個工作日——DAX 編碼語言解決方案

回顧前 N 個工作日——DAX 編碼語言解決方案

在此 DAX 編碼語言教程中,了解如何使用 GENERATE 函數以及如何動態更改度量標題。

在 LuckyTemplates 中使用多線程動態視覺技術展示見解

在 LuckyTemplates 中使用多線程動態視覺技術展示見解

本教程將介紹如何使用多線程動態可視化技術從報告中的動態數據可視化中創建見解。

LuckyTemplates 篩選上下文簡介

LuckyTemplates 篩選上下文簡介

在本文中,我將貫穿過濾器上下文。篩選上下文是任何 LuckyTemplates 用戶最初應該了解的主要主題之一。