여러 데이터 원본의 데이터 결합(파워 쿼리)

참고:  사용자 언어로 가능한 한 빨리 가장 최신의 도움말 콘텐츠를 제공하고자 합니다. 이 페이지는 자동화를 통해 번역되었으며 문법 오류나 부정확한 설명을 포함할 수 있습니다. 이 목적은 콘텐츠가 사용자에게 유용하다는 것입니다. 이 페이지 하단의 정보가 도움이 되었다면 알려주세요. 쉽게 참조할 수 있는 영어 문서 가 여기 있습니다.

참고: 파워 쿼리가져오기 및 변환 Excel 2016에서 다른 이름으로 알려져 있습니다. 여기에 제공 된 정보를 모두 적용 됩니다. 자세한 내용은 가져오기 및 Excel 2016에서 변형를 참조 하십시오.

이 자습서에서는 제품 정보를 포함 하 고 odata 피드에서 있는 제품 주문 정보가 들어 있는 Excel 파일을 로컬에서 데이터를 가져오는 파워 쿼리의 쿼리 편집기를 사용 합니다. 변환 및 집계 단계를 수행 하 고 제품 및 연간 총 판매량 보고서를 생성 하기 위해 두 원본의 데이터 결합 합니다.

이 자습서를 수행 하기 위해 제품, 주문 통합을 해야 합니다. 다른 이름으로 저장 대화 상자에서 Products and Orders.xlsx 파일을 이름을 지정 합니다.

이 자습서의 내용

작업 1: Excel 통합 문서로 제품 가져오기

1단계: Excel 통합 문서에 연결

2단계: 첫 번째 행을 테이블 열 머리글로 승격

3단계: 다른 열을 제거하여 관심 있는 열만 표시

생성된 파워 쿼리 단계

4단계: Products 쿼리 가져오기

작업 2: OData 피드에서 주문 데이터 가져오기

1단계: OData 피드에 연결

2단계: Order_Details 테이블 확장

Order_Details 테이블 링크 확장

3단계: 다른 열을 제거하여 관심 있는 열만 표시

선택한 열 제거

4단계: 각 Order_Details 행의 총계 계산

각 Order_Details 행의 총계 계산

5단계: OrderDate 연도 열 변환

6단계: ProductID 및 Year를 기준으로 행 그룹화

7단계: 쿼리 이름 바꾸기

최종 쿼리 결과

생성된 파워 쿼리 단계

8단계: 쿼리를 Excel 통합 문서로 다운로드하지 않도록 설정

쿼리를 다운로드하지 않도록 설정

작업 3: Products 및 Total Sales 쿼리 결합

1단계: Total Sales 쿼리에 ProductID 병합

2 단계: 병합 열 확장

NewColumn 테이블 링크 확장

생성된 파워 쿼리 단계

3단계: Excel 데이터 모델에 Total Sales per Product 쿼리 로드

Excel 데이터 모델에 Total Sales per Product 쿼리 로드

최종 Total Sales per Product 쿼리

작업 1: Excel 통합 문서로 제품 가져오기

이 작업에서는 Excel 통합 문서에 Products and Orders.xlsx 파일에서 제품을 가져옵니다.

1단계: Excel 통합 문서에 연결

  1. Excel 통합 문서를 만듭니다.

  2. POWER QUERY 리본 메뉴 탭에서 파일 > Excel에서를 클릭합니다.

  3. Excel 찾아보기 대화 상자에서 Products and Orders.xlsx 경로를 찾거나 입력하여 파일을 가져오거나 연결합니다.

  4. 탐색 창에서 Products 워크시트를 두 번 클릭하거나 Products를 클릭하고 쿼리 편집을 클릭합니다. 쿼리를 편집하거나 새 데이터 원본에 연결할 때 쿼리 편집기 창이 나타납니다.

    참고: 이 문서 뒷부분에 나오는 매우 간략한 쿼리 편집기 표시 방법 관련 비디오를 참조하세요.

2단계: 첫 번째 행을 테이블 열 머리글로 승격

쿼리 미리 보기 표에서 테이블의 첫 번째 행에는 테이블 열 이름이 없습니다. 첫 번째 행을 테이블 열 머리글로 승격하려면 다음을 실행합니다.

  1. 데이터 미리 보기의 왼쪽 위 모서리에 있는 테이블 아이콘 ( 테이블 아이콘 )를 클릭 합니다.

  2. 첫 행을 머리글로 사용을 클릭합니다.

첫 번째 행을 테이블 열 머리글로 승격

3단계: 다른 열을 제거하여 관심 있는 열만 표시

이 단계에서는 ProductID, ProductName, CategoryID, QuantityPerUnit을 제외한 모든 열을 제거합니다.

  1. 쿼리 미리 보기 표에서 ProductID, ProductName, CategoryID, QuantityPerUnit 열을 선택합니다(Ctrl+클릭 또는 Shift+클릭 사용).

  2. 쿼리 편집기 리본 메뉴에서 열 제거 > 다른 열 제거를 클릭하거나 열 머리글을 마우스 오른쪽 단추로 클릭하고 다른 열 제거를 클릭합니다.

    다른 열 숨기기

생성된 파워 쿼리 단계

파워 쿼리에서 쿼리 작업을 수행할 때 쿼리 단계가 만들어지고 쿼리 설정 창의 적용된 단계 목록에 나열됩니다. 각 쿼리 단계에는 해당하는 파워 쿼리 수식이 있으며 이를 "M" 언어라고도 합니다. 파워 쿼리 수식 언어에 대한 자세한 내용은 파워 쿼리 수식에 대한 정보를 참고하세요.

작업

쿼리 단계

수식

Excel 통합 문서에 연결

원본

원본{[Name="Products"]}[Data]

첫 번째 행을 테이블 열 머리글로 승격

FirstRowAsHeader

Table.PromoteHeaders

(Products)

다른 열을 제거하여 관심 있는 열만 표시

RemovedOtherColumns

Table.SelectColumns

(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

4단계: Products 쿼리 가져오기

이 단계에서는 Products 쿼리를 Excel 통합 문서로 가져옵니다.

  1. 쿼리 편집기 리본 메뉴에서 적용 및 닫기를 클릭합니다. 새 Excel 워크시트에 결과가 나타납니다.

맨 위로 이동

작업 2: OData 피드에서 주문 데이터 가져오기

이 작업에서는 http://services.odata.org/Northwind/Northwind.svc에 있는 샘플 Northwind OData 피드의 데이터를 Excel 통합 문서로 가져옵니다.

1단계: OData 피드에 연결

  1. POWER QUERY 리본 메뉴 탭에서 기타 원본에서 > OData 피드에서를 클릭합니다.

  2. OData 피드 대화 상자에 Northwind OData 피드의 URL을 입력합니다.

  3. 확인을 클릭합니다.

  4. 탐색 창에서 Orders 테이블을 두 번 클릭하거나 Orders를 클릭하고 편집을 클릭합니다.

참고: 테이블 위에 마우스를 올리면 테이블 미리 보기 플라이아웃이 표시됩니다.

데이터 원본 위에 마우스 놓기

2단계: Order_Details 테이블 확장

이 단계에서는 Orders 테이블과 관련 있는 Order_Details 테이블을 확장하여 Order_DetailsProductID, UnitPrice, Quantity 열을 Orders 테이블에 결합합니다. 확장 작업은 관련 테이블의 열을 대상 테이블에 결합합니다. 쿼리를 실행하면 관련 테이블(Order_Details)의 행이 대상 테이블(Orders)의 행에 결합됩니다.

파워 쿼리에서 관련 테이블에 대한 링크를 포함하는 열에는 항목 링크 또는 테이블 링크가 있습니다. 항목 링크는 단일 관련 레코드를 탐색하여대상 테이블과의 일 대 일 관계를 나타냅니다.테이블 링크는 관련 테이블을 탐색하여 대상 테이블과의 일 대 다 관계를 나타냅니다. 링크는 관계형 모델 내에서 데이터 원본의 탐색 속성을 나타냅니다. OData 피드의 경우 탐색 속성은 외래 키 연결을 사용한 엔터티를 나타냅니다. SQL Server 같은 데이터베이스의 경우 탐색 속성은 데이터베이스의 외래 키 관계를 나타냅니다.

Order_Details 테이블 링크 확장

Order_Details 테이블을 확장한 후에는 세 개의 새 열과 추가 행이 Orders 테이블에 추가됩니다(중첩된 테이블이나 관련 테이블의 행별로 하나씩).

  1. 쿼리 미리 보기 창에서 Order_Details 열로 스크롤합니다.

  2. Order_Details 열에서 확장 아이콘 ( 확장 )을 클릭 합니다.

  3. 확장 드롭다운에서 다음을 실행합니다.

    1. (모든 열 선택)을 클릭하여 모든 열을 지웁니다.

    2. ProductID, UnitPrice, Quantity를 클릭합니다.

    3. 확인을 클릭합니다.

      Order_Details 테이블 링크 확장

      참고: 파워 쿼리에서는 연결된 테이블을 열로 확장할 수 있으며, 대상 테이블에서 데이터를 확장하기 전에 연결된 테이블의 열에 대해 집계 연산을 수행하는 기능도 있습니다. 집계 연산을 수행하는 방법에 대한 자세한 내용은 열에서 데이터 집계를 참고하세요.

3단계: 다른 열을 제거하여 관심 있는 열만 표시

이 단계에서는 OrderDate, ProductID, UnitPrice, Quantity 열을 제외한 모든 열을 제거합니다. 이전 작업에서 다른 열 제거를 사용했습니다. 이 작업에서는 선택한 열을 제거해 보겠습니다.

선택한 열 제거

  1. 쿼리 미리 보기 창에서 모든 열을 선택합니다.

    1. 첫 번째 열(OrderID)을 클릭합니다.

    2. Shift 키를 누른 채로 마지막 열(Shipper)을 클릭합니다.

    3. Ctrl 키를 누른 채로 OrderDate, Order_Details.ProductID, Order_Details.UnitPrice, Order_Details.Quantity 열을 클릭합니다.

  2. 선택한 열 머리글을 마우스 오른쪽 단추로 클릭하고 열 제거를 클릭합니다.

4단계: 각 Order_Details 행의 총계 계산

이 단계에서는 사용자 지정 열을 만들어 각 Order_Details 행의 총계를 계산합니다.

각 Order_Details 행의 총계 계산

  1. 쿼리 미리 보기 창에서 미리 보기의 왼쪽 위 모서리에 있는 테이블 아이콘 ( 테이블 아이콘 )를 클릭 합니다.

  2. 열 삽입 > 사용자 지정을 클릭합니다.

  3. 사용자 지정 열 삽입 대화 상자의 사용자 지정 열 수식 텍스트 상자에 [Order_Details.UnitPrice] * [Order_Details.Quantity]를 입력합니다.

  4. 새 열 이름 텍스트 상자에 Line Total을 입력합니다.

  5. 확인을 클릭합니다.

각 Order_Details 행의 총계 계산

5단계: OrderDate 연도 열 변환

이 단계에서는 OrderDate 열을 변환하여 수주일 연도를 렌더링합니다.

  1. 미리 보기 표에서 OrderDate 열을 마우스 오른쪽 단추로 클릭하고 변환 > 연도를 클릭합니다.

  2. OrderDate 열의 이름을 Year로 바꿉니다.

    1. OrderDate 열을 두 번 클릭하고 Year를 입력하거나

    2. OrderDate 열을 마우스 오른쪽 단추로 클릭하고 이름 바꾸기를 클릭한 후 Year를 입력합니다.

6단계: ProductID 및 Year를 기준으로 행 그룹화

  1. 쿼리 미리 보기 표에서 YearOrder_Details.ProductID를 선택합니다.

  2. 머리글 중 하나를 마우스 오른쪽 단추로 클릭하고 그룹화 방법을 클릭합니다.

  3. 그룹화 방법 대화 상자에서 다음을 실행합니다.

    1. 새 열 이름 텍스트 상자에 Total Sales를 입력합니다.

    2. 연산 드롭다운에서 합계를 선택합니다.

    3. 드롭다운에서 Line Total을 선택합니다.

  4. 확인을 클릭합니다.

    집계 연산을 위한 그룹화 방법 대화 상자

7단계: 쿼리 이름 바꾸기

판매 데이터를 Excel로 가져오기 전에 쿼리 이름을 Total Sales로 지정합니다.

  1. 쿼리 설정 창의 이름 텍스트 상자에 Total Sales를 입력합니다.

최종 쿼리 결과

각 단계를 수행한 후에는 Northwind OData 피드를 통한 Total Sales 쿼리가 표시됩니다.

총 매출

생성된 파워 쿼리 단계

파워 쿼리에서 쿼리 작업을 수행할 때 쿼리 단계가 만들어지고 쿼리 설정 창의 적용된 단계 목록에 나열됩니다. 각 쿼리 단계에는 해당하는 파워 쿼리 수식이 있으며 이를 "M" 언어라고도 합니다. 파워 쿼리 수식 언어에 대한 자세한 내용은 파워 쿼리 수식에 대한 정보를 참고하세요.

작업

쿼리 단계

수식

OData 피드에 연결

원본

원본{[Name="Orders"]}[Data]

Order_Details 테이블 확장

Expand Order_Details

Table.ExpandTableColumn

(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})

다른 열을 제거하여 관심 있는 열만 표시

RemovedColumns

Table.RemoveColumns

(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})

각 Order_Details 행의 총계 계산

InsertedColumns

Table.AddColumn

(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

OrderDate 열을 변환하여 연도 렌더링

RenamedColumns

Table.RenameColumns

(InsertedCustom,{{"Custom", "Line Total"}})

TransformedColumn

Table.TransformColumns

(RenamedColumns,{{"OrderDate", Date.Year}})

RenamedColumns1

Table.RenameColumns

(TransformedColumn,{{"OrderDate", "Year"}})

ProductID 및 Year를 기준으로 행 그룹화

GroupedRows

Table.Group
(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", 각 List.Sum ([Line Total]), 번호를 입력}})

8단계: 쿼리를 Excel 통합 문서로 다운로드하지 않도록 설정

Total Sales 쿼리가 최종 Total Sales per Product and Year 보고서를 나타내지 않기 때문에 쿼리를 Excel 통합 문서로 다운로드하지 않도록 설정합니다. 쿼리 설정 창에서 워크시트로 로드 옵션이 해제인 경우 이 쿼리의 데이터 결과가 다운로드되지 않습니다. 하지만 원하는 결과를 작성하기 위해 이 쿼리를 다른 쿼리와 결합할 수 있습니다. 다음 작업에서는 이 쿼리를 Products 쿼리와 결합하는 방법에 대해 알아봅니다.

쿼리를 다운로드하지 않도록 설정

  1. 쿼리 설정 창에서 워크시트로 로드를 선택 취소합니다.

  2. 쿼리 편집기 리본 메뉴에서 적용 및 닫기를 클릭합니다. 통합 문서 쿼리 창의 Total Sales 쿼리에 로드하지 않습니다가 표시됩니다.

    쿼리를 다운로드하지 않도록 설정

맨 위로 이동

작업 3: Products 및 Total Sales 쿼리 결합

파워 쿼리를 사용하면 여러 쿼리를 병합 또는 추가하여 결합할 수 있습니다. 병합 작업은 데이터의 출처가 되는 데이터 원본과 관계없이 표 형식으로 된 모든 파워 쿼리의 쿼리에서 수행됩니다. 데이터 원본 결합에 대한 자세한 내용은 여러 쿼리 결합을 참조하세요.

이 작업에서는 병합확장 쿼리 단계를 사용하여 ProductsTotal Sales 쿼리를 결합합니다.

1단계: Total Sales 쿼리에 ProductID 병합

  1. Excel 통합 문서에서 Sheet2Products 쿼리로 이동합니다.

  2. 쿼리 리본 메뉴 탭에서 병합을 클릭합니다.

  3. 병합 대화 상자에서 기본 테이블로 Products를 선택하고 병합할 보조 테이블 또는 관련 쿼리로 Total Sales를 선택합니다. Total Sales가 확장 가능한 새 열이 됩니다.

  4. ProductID를 기준으로 Total SalesProducts와 일치시키려면 Products 테이블에서 ProductID 열을 선택하고 Total Sales 테이블에서 Order_Details.ProductID 열을 선택합니다.

  5. 개인 정보 수준 대화 상자에서 다음을 실행합니다.

    1. 두 데이터 원본의 개인 정보 격리 수준으로 조직을 선택합니다.

    2. 저장을 클릭합니다.

  6. 확인을 클릭합니다.

    보안 정보: 개인 정보 수준은 사용자가 여러 데이터 원본의 데이터를 실수로 결합하지 않도록 하며, 개인 또는 조직으로 설정할 수 있습니다. 쿼리에 따라, 사용자는 개인 데이터 원본의 데이터를 악의적인 다른 데이터 원본에 잘못 전송할 수 있습니다. 파워 쿼리는 각 데이터 원본을 분석하여 공용, 조직, 개인 등 정의된 개인 정보 수준으로 분류합니다. 개인 정보 수준에 대한 자세한 내용은 개인 정보 수준을 참고하세요.

    병합 대화 상자

확인을 클릭하면 병합 작업에서 쿼리가 만들어집니다. 쿼리 결과에 기본 테이블(Products)의 모든 열과 관련 테이블(Total Sales)에 대한 탐색 링크가 포함된 단일 열이 포함됩니다. 확장 작업은 기본 테이블 또는 대상 테이블에 관련 테이블의 새 열을 추가합니다.

병합 최종

2단계: 병합 열 확장

이 단계에서는 이름이 NewColumnProducts 쿼리에서 두 개의 새 열을 만드는 데 인 병합 열 확장: YearTotal Sales 합니다.

NewColumn 테이블 링크 확장

  1. 쿼리 미리 보기 표에서 NewColumn 클릭 ( 확장 ) 아이콘을 확장 합니다.

  2. 확장 드롭다운에서 다음을 실행합니다.

    1. (모든 열 선택)을 클릭하여 모든 열을 지웁니다.

    2. YearTotal Sales를 클릭합니다.

    3. 확인을 클릭합니다.

  3. 이러한 두 열의 이름을 YearTotal Sales로 바꿉니다.

  4. Total Sales 기준을 내림차순 정렬로 지정하여 판매액이 가장 높은 제품과 연도를 알아봅니다.

  5. 이름 바꾸기를 통해 쿼리의 이름을 Total Sales per Product로 변경합니다.

테이블 링크 확장

생성된 파워 쿼리 단계

파워 쿼리에서 병합 쿼리 작업을 수행할 때 쿼리 단계가 만들어지고 쿼리 설정 창의 적용된 단계 목록에 나열됩니다. 각 쿼리 단계에는 해당하는 파워 쿼리 수식이 있으며 이를 "M" 언어라고도 합니다. 파워 쿼리 수식 언어에 대한 자세한 내용은 파워 쿼리 수식에 대한 정보를 참고하세요.

작업

쿼리 단계

수식

Total Sales 쿼리에 ProductID 병합

원본(병합 작업용 데이터 원본)

Table.NestedJoin

(Products,{"ProductID"},#"Total Sales",{"Order_Details.ProductID"},"NewColumn")

병합 열 확장

ExpandNewColumn

Table.ExpandTableColumn

(Source, "NewColumn", {"Year", "Total Sales"}, {"NewColumn.Year", "NewColumn.Total Sales"})

RenamedColumns

Table.RenameColumns

(#"Expand NewColumn",{{"NewColumn.Year", "Year"}, {"NewColumn.Total Sales", "Total Sales"}})

SortedRows

Table.Sort

(RenamedColumns,{{"Total Sales", Order.Descending}})

3단계: Excel 데이터 모델에 Total Sales per Product 쿼리 로드

이 단계에서는 워크시트로 로드 옵션을 사용하지 않도록 설정하고 쿼리 결과에 연결된 보고서를 작성하기 위해 Excel 데이터 모델에 쿼리를 로드합니다. 파워 쿼리는 Excel 워크시트에 쿼리 결과를 로드할 뿐만 아니라 쿼리 결과를 Excel 데이터 모델에 로드할 수도 있습니다. 데이터를 Excel 데이터 모델에 로드한 후에는 파워 피벗 및 Power View를 사용하여 추가로 데이터를 분석할 수 있습니다.

Excel 데이터 모델에 Total Sales per Product 쿼리 로드

  1. 쿼리 설정 창에서 워크시트로 로드를 선택 해제하고 데이터 모델로 로드를 선택합니다.

  2. 쿼리를 Excel 데이터 모델로 로드하려면 적용 및 닫기를 클릭합니다.

Excel 데이터 모델 로드

최종 Total Sales per Product 쿼리

각 단계를 수행한 후에는 Products and Orders.xlsx 파일 및 Northwind OData 피드의 데이터를 결합하는 Total Sales per Product 쿼리가 생성됩니다. 이 쿼리는 파워 피벗 모델에 적용할 수 있습니다. 뿐만 아니라 파워 쿼리의 쿼리에 대한 변경 사항이 있으면 파워 피벗 모델의 결과 테이블을 수정하고 새로 고칩니다.

맨 위로 이동

참고: 쿼리 편집기 로드, 편집 또는 파워 쿼리를 사용 하 여 새 쿼리를 만들 때에 나타납니다. 다음 비디오에서 Excel 통합 문서 쿼리를 편집한 후 나타나는 쿼리 편집기 창을 보여 줍니다. 쿼리 편집기 를 로드 하거나 파워 쿼리 리본 메뉴 탭에서 외부 데이터 가져오기 섹션에서 기존 통합 문서 쿼리를 편집 하지 않고 보려면 선택 기타 원본에서 > 빈 쿼리 합니다. 다음 비디오 쿼리 편집기 를 표시 하는 방법을 보여 줍니다.

Excel에서 쿼리 편집기를 보는 방법

Office 기술 확장
교육 살펴보기
새로운 기능 우선 가져오기
Office Insider 참여

이 정보가 유용한가요?

의견 주셔서 감사합니다!

피드백을 주셔서 감사합니다. Office 지원 에이전트와 연락하는 것이 도움이 될 것 같습니다.

×