☰

使用 Excel Power Query 從 XML 檔案傳回多個值

tag-icon • tag-icon microsoft-excel xml power-query
使用 Excel Power Query 從 XML 檔案傳回多個值

我有大約 50 個結構相同的 XML 檔案。我想從每個值中提取多個值，這些值位於 XML 表的不同「行」中。

現在，我可以使用 Power Query 提取一個檔案的 XML 表的一「行」。這對我不起作用，因為：

  1. 我需要對多個 XML 文件執行此查詢（不是手動對每個文件執行此查詢）
  2. 我嘗試提取的資訊位於 XML 表的不同「行」上，因此，如果我只提取一行，則其他一些值將隱藏在該行的巢狀表中。

我可以使用 Power Query 來實現此目的還是必須使用 VBA？是否有可用於此類專案的資源？

答案1

您正在尋找的魔法Table.Combine(MyTable[ColumnOfTables])不是MyTable{0}[ColumnOfTables].

對於給定的 XML 檔案（假設它們都是相同的結構），請嘗試建立一個查詢以從一個檔案中取得所需的所有內容。我建議不要點擊表格單元格中的“表格”一詞進行導航。相反，請手動鍵入每個步驟並命名所需的行，而不是假設它始終採用相同的順序。從查詢開始

let
    Source = Xml.Tables(File.Contents("C:\Temp\YourFile.xml"))
in
    Source

然後看看它是什麼樣子的。在我正在使用的 XML 檔案中，我看到一個包含「名稱」和「表」列的表格。我只想要“Name”列值為“Body”的一行，因此我將查詢更改為

let
    Source = Xml.Tables(File.Contents("C:\Temp\YourFile.xml")),
    Body = Source{[Name="Body"]}[Table]
in
    Body

由於某種原因我還不明白，我看到的是一個類似的表，其中一行包含名稱空間。很容易。重複相同的步驟。

let
    Source = Xml.Tables(File.Contents("C:\Temp\YourFile.xml")),
    Body = Source{[Name="Body"]}[Table],
    #"namespace url here" = Body{[Name="namespace url here"]}[Table]
in
    #"namespace url here"

假設這給了我一個帶有「案例」列的表格，每個表格都包含一個帶有「參與者」列的表格，我想要的是所有案例參與者的列表

let
    Source = Xml.Tables(File.Contents("C:\Temp\YourFile.xml")),
    Body = Source{[Name="Body"]}[Table],
    #"namespace url here" = Body{[Name="namespace url here"]}[Table],
    Cases = Table.Combine(#"namespace url here"[Cases]),
    Participants = Table.Combine(Cases[Participants])
in
    Participants

一旦您獲得特定文件的類似內容，請複製查詢，將其重新命名為“fGetParticipants”（或任何對您有意義的名稱），並將前兩行替換為以下內容：

(record as record) as table =>
let
    FilePath = record[File Path],
    Source = Xml.Tables(File.Contents(FilePath)),

此查詢將傳回一個函數，該函數將表行作為參數，取得檔案路徑，然後執行與先前相同的操作。

假設您已經或可以建立檔案路徑的查詢（從資料夾的查詢開始並新增的「檔案路徑」列[Folder Name] & [Name]，或其他什麼，您只需要一個包含名為「檔案路徑」的資料列的查詢包含您想要的文件路徑），現在您可以進行以下查詢

let
    Source = #"Name of your query containing a column called File Path",
    #"Added Participants" = Table.AddColumn(Source, "Participants", fGetParticipants),
    // Not all had participants, so some of the files resulted in errors...
    #"Removed Errors" = Table.RemoveRowsWithErrors("#Added Participants", {"Participants"}),
    // Combine them all into one huge table
    #"All Participants" = Table.Combine(#"Removed Errors"[Participants])
in
    #"All Participants"

我確信這不會為你帶來你所需要的一切。您提到有一些嵌套，您沒有提供太多細節，並且您的資料中可能存在一些上述未觸及的邊緣情況。但是，如果您想透過 PowerQuery 執行此操作，這應該可以幫助您繼續前進。

答案2

前往取得資料 -> 從檔案 -> 從資料夾，然後選擇資料夾。點擊“開啟”，然後在彈出視窗中選擇“組合”選單按鈕下的“組合和轉換資料”。之後從左側窗格中選擇最後一個節點。

相關內容