여러 데이터 원본을 결합하는 방법 알아보기(Power Query)

이 작업에서는 제품 및 Orders.xlsx 파일에서 Excel 통합 문서로 제품을 가져오고 행을 열 머리글로 승격하고, 일부 열을 제거하고, 워크시트에 쿼리를 로드합니다.

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

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

2. 데이터 > 통합 문서에서 파일 > 데이터 > 가져오기를 선택합니다.

3. 데이터 가져오기 대화 상자에서 다운로드한 Products.xlsx 파일을 찾아 찾은 다음 열기를 선택합니다.

4. 탐색기 창에서 Products 테이블을 두 번 클릭합니다. Power 쿼리 편집기 나타납니다.

2단계: 쿼리 단계 검사

기본적으로 Power Query 편의를 위해 여러 단계를 자동으로 추가합니다. 자세한 내용은 쿼리 설정 창의 적용된 단계 아래에서 각 단계를 검토합니다.

1. 원본 단계를 마우스 오른쪽 단추로 클릭하고 설정 편집을 선택합니다. 이 단계는 통합 문서를 가져올 때 만들어졌습니다.

2. 탐색 단계를 마우스 오른쪽 단추로 클릭하고 설정 편집을 선택합니다. 이 단계는 탐색 대화 상자에서 테이블을 선택할 때 만들어졌습니다.

3. 변경된 형식 단계를 마우스 오른쪽 단추로 클릭하고 설정 편집을 선택합니다. 이 단계는 각 열의 데이터 형식을 유추한 Power Query 의해 만들어졌습니다. 수식 입력줄 오른쪽의 아래쪽 화살표를 선택하여 전체 수식을 확인합니다.

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

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

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

2. 열 제거 > 다른 열 제거를 선택합니다.

   

4단계: 제품 쿼리 로드

이 단계에서는 Products 쿼리를 Excel 워크시트에 로드합니다.

• 홈 > & 로드 닫기를 선택합니다. 쿼리가 새 Excel 워크시트에 나타납니다.

요약: 작업 1에서 만든 Power Query 단계

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

작업	쿼리 단계	수식
Excel 통합 문서 가져오기	원본	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
제품 테이블 선택	이동	= 원본{[Item="Products",Kind="Table"]}[Data]
Power Query 열 데이터 형식을 자동으로 검색합니다.	변경된 형식	= Table.TransformColumnTypes(Products_Table,{{"ProductID", Int64.Type}, {"ProductName", type text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}})
다른 열을 제거하여 관심 있는 열만 표시	제거된 다른 열 수	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

이 작업에서는 http://services.odata.org/Northwind/Northwind.svc 샘플 Northwind OData 피드에서 Excel 통합 문서로 데이터를 가져오고 , Order_Details테이블을 확장하고, 열을 제거하고, 줄 합계를 계산하고, OrderDate를 변환하고, ProductID 및 Year별로 행을 그룹화하고, 쿼리 이름을 바꾸고, Excel 통합 문서에 대한 쿼리 다운로드를 사용하지 않도록 설정합니다.

1단계: OData 피드에 연결

1. 데이터 > OData 피드에서 다른 원본 > 데이터> 가져오기 를 선택합니다.

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

3. 확인을 선택합니다.

4. 탐색기 창에서 Orders 테이블을 두 번 클릭합니다.

2단계: Order_Details 테이블 확장

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

Power Query 관련 테이블이 포함된 열에는 셀의 Record 또는 Table 값이 있습니다. 이를 구조적 열이라고 합니다. 레코드는 단일 관련 레코드를나타내며 현재 데이터 또는 기본 테이블과의 일대일 관계를 나타냅니다. 테이블은 관련 테이블을 나타내며 현재 또는 기본 테이블과의 일대다 관계를 나타냅니다. 구조화된 열은 관계형 모델이 있는 데이터 원본의 관계를 나타냅니다. 예를 들어 구조화된 열은 OData 피드에서 외래 키 연결이 있는 엔터티 또는 SQL Server 데이터베이스의 외래 키 관계를 나타냅니다.

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

1. 데이터 미리 보기에서 Order_Details 열까지 가로로 스크롤합니다.

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

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

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

   2. ProductID, UnitPrice 및 Quantity를 선택합니다.

   3. 확인을 선택합니다.

      

      참고: Power Query 열에서 연결된 테이블을 확장하고 연결된 테이블의 열을 집계한 후 주체 테이블의 데이터를 확장할 수 있습니다. 집계 연산을 수행하는 방법에 대한 자세한 내용은 열에서 데이터 집계를 참고하세요.

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 행의 총계를 계산합니다.

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

2. 사용자 지정 열 추가를 클릭합니다.

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

4. 새 열 이름 상자에 줄 합계를 입력합니다.

5. 확인을 선택합니다.

5단계: OrderDate 연도 열 변환

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

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

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

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

   2. OrderDate 열에서 Right-Click 이름 바꾸기를 선택하고 Year를 입력합니다.

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

1. 데이터 미리 보기에서 Year 및 Order_Details.ProductID를 선택합니다.

2. 머리글 중 하나를 Right-Click 그룹화 기준 을 선택합니다.

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

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

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

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

4. 확인을 선택합니다.

   

7단계: 쿼리 이름 바꾸기

판매 데이터를 Excel로 가져오기 전에 쿼리 이름을 바꿉니다.

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

결과: 작업 2에 대한 최종 쿼리

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

요약: 작업 2에서 만든 Power Query 단계 

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

작업	쿼리 단계	수식
OData 피드에 연결	원본	= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])
표 선택	탐색	= Source{[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 행의 총계 계산	추가된 사용자 지정	= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])
더 의미 있는 이름으로 변경, Lne 합계	이름이 바뀐 열	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
OrderDate 열을 변환하여 연도 렌더링	추출된 연도	= Table.TransformColumns(#"그룹화된 행",{{"Year", Date.Year, Int64.Type}})
다음으로 변경  더 의미 있는 이름, OrderDate 및 Year	이름이 바뀐 열 1	Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}})
ProductID 및 Year를 기준으로 행 그룹화	GroupedRows	= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

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

이 작업에서는 병합 쿼리 및 확장 작업을 사용하여 Products 및 Total Sales 쿼리를 결합한 다음 제품당 총 판매액 쿼리를 Excel 데이터 모델에 로드합니다.

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

1. Excel 통합 문서에서 제품 워크시트 탭의 제품 쿼리로 이동합니다.

2. 쿼리에서 셀 을 선택한 다음 쿼리> 병합을 선택합니다.

3. 병합 대화 상자에서 제품을 기본 테이블로 선택하고 병합할 보조 또는 관련 쿼리로 Total Sales를 선택합니다. Total Sales 는 확장 아이콘이 있는 새로운 구조화된 열이 됩니다.

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

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

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

   2. 저장을 선택합니다.

6. 확인을 선택합니다.

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

   

결과

병합 작업은 쿼리를 만듭니다. 쿼리 결과에는 주 테이블(Products)의 모든 열과 관련 테이블(총 판매액)에 대한 단일 테이블 구조화된 열이 포함됩니다. 확장 아이콘을 선택하여 보조 또는 관련 테이블의 기본 테이블에 새 열을 추가합니다.

2단계: 병합된 열 확장

이 단계에서는 NewColumn 이라는 이름으로 병합된 열을 확장하여 Products 쿼리에 Year 및 Total Sales라는 두 개의 새 열을 만듭니다.

1. 데이터 미리 보기에서 NewColumn 옆에 있는 확장 아이콘()을 선택합니다.

2. 확장 드롭다운 목록에서 다음을 수행합니다.

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

   2. 연도 및 총 판매량을 선택합니다.

   3. 확인을 선택합니다.

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

4. 어떤 제품과 몇 년 동안 제품이 가장 많은 판매량을 얻었는지 알아보려면 총 판매액 기준으로 내림차순 정렬을 선택합니다.

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

결과

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

이 단계에서는 쿼리 결과에 연결된 보고서를 작성하기 위해 쿼리를 Excel 데이터 모델에 로드합니다. Excel 데이터 모델에 데이터를 로드한 후 파워 피벗을 사용하여 데이터 분석을 강화할 수 있습니다.

1. 홈 > & 로드 닫기를 선택합니다.

2. 데이터 가져오기 대화 상자에서 데이터 모델에 이 데이터 추가를 선택해야 합니다. 이 대화 상자를 사용하는 방법에 대한 자세한 내용은 물음표(?)를 선택합니다.

결과

Products.xlsx 파일 및 Northwind OData 피드의 데이터를 결합하는 제품당 총 판매액 쿼리가 있습니다. 이 쿼리는 파워 피벗 모델에 적용됩니다. 또한 쿼리 변경 내용이 데이터 모델에서 결과 테이블을 수정하고 새로 고칩니다.

요약: 작업 3에서 만든 Power Query 단계

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

작업	쿼리 단계	수식
Total Sales 쿼리에 ProductID 병합	원본(병합 작업용 데이터 원본)	= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
병합 열 확장	확장된 총 판매액	= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})
두 열 이름 바꾸기	이름이 바뀐 열	= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}} )
총 판매액을 오름차순으로 정렬	정렬된 행	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})