☰

Excel 파워 쿼리를 사용하여 XML 파일에서 여러 값 반환

tag-icon • tag-icon microsoft-excel xml power-query
Excel 파워 쿼리를 사용하여 XML 파일에서 여러 값 반환

동일한 구조의 XML 파일이 약 50개 있습니다. XML 테이블의 서로 다른 "행"에 있는 값을 사용하여 각 값에서 여러 값을 추출하고 싶습니다.

지금은 파워 쿼리를 사용하여 파일 하나에 대해 XML 테이블의 "행" 하나를 가져올 수 있습니다. 다음과 같은 이유로 나에게 적합하지 않습니다.

  1. 여러 XML 파일에 대해 이 쿼리를 수행해야 합니다(각각 수동으로 수행하지 않음).
  2. 추출하려는 정보는 XML 테이블의 다른 "행"에 있으므로 한 행만 가져오면 다른 값 중 일부가 해당 행의 중첩 테이블에 숨겨집니다.

이를 위해 파워 쿼리를 사용할 수 있습니까, 아니면 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

데이터 가져오기 -> 파일에서 -> 폴더에서로 이동하여 폴더를 선택합니다. 열기를 클릭하고 팝업 창에서 결합 메뉴 버튼 아래의 데이터 결합 및 변환을 선택합니다. 그런 다음 왼쪽 창에서 마지막 노드를 선택합니다.

관련 정보