이 자습서에서는 파워 쿼리의 쿼리 편집기를 사용하여 제품 정보가 포함된 로컬 Excel 파일과 제품 주문 정보가 포함된 OData 피드에서 데이터를 가져옵니다. 변환 및 집계 단계를 수행하고 두 원본의 데이터를 결합하여 제품별 총 매출 및 연도 보고서를 만듭니다.
이 자습서를 완료하려면 제품 통합 문서가 필요합니다. 다른 이름으로 저장 대화 상자에서 파일 이름을 Products and Orders.xlsx로 지정합니다.
작업 1: Excel 통합 문서로 제품 가져오기
이 작업에서는 제품 및 Orders.xlsx 파일(이전 섹션에서 다운로드 및 이름 변경)에서 Excel 통합 문서로 제품을 가져옵니다. 그런 다음 행을 열 머리글로 승격하고 일부 열을 제거한 다음 워크시트에 쿼리를 로드합니다.
- Excel 통합 문서를 만듭니다.
- 데이터> 통합문서의파일>에서데이터> 가져오기를 선택합니다.
- 데이터 가져오기 대화 상자에서 다운로드한 Products.xlsx 파일을 찾아 찾은 다음 열기를 선택합니다.
- 탐색기 창에서 제품 테이블을 두 번 클릭합니다. Power Query 편집기가 나타납니다.
기본적으로 파워 쿼리는 사용자의 편의를 위해 여러 단계를 자동으로 추가합니다. 쿼리 설정 창의 적용된 단계에서 각 단계를 검사하여 자세히 알아보세요.
- 소스 단계를 마우스 오른쪽 단추로 클릭하고 설정 편집을 선택합니다. 이 단계는 통합 문서를 가져올 때 만들어졌습니다.
- 탐색 단계를 마우스 오른쪽 단추로 클릭하고 설정 편집을 선택합니다. 이 단계는 탐색 대화 상자에서 테이블을 선택할 때 만들어졌습니다.
- 변경된 형식 단계를 마우스 오른쪽 단추로 클릭하고 설정 편집을 선택합니다. 이 단계는 각 열의 데이터 형식을 유추하는 파워 쿼리에 의해 만들어졌습니다. 수식 입력줄의 오른쪽에 있는 아래쪽 화살표를 선택하여 전체 수식을 확인합니다.
이 단계에서는 ProductID, ProductName, CategoryID 및 QuantityPerUnit을 제외한 모든 열을 제거합니다.
- 데이터 미리 보기에서 ProductID, ProductName, CategoryID 및 QuantityPerUnit 열을 선택합니다(Ctrl+클릭 또는 Shift+클릭 사용).
-
열 제거를> 선택하고다른 열을 제거합니다.
4단계: 제품 쿼리 로드
이 단계에서는 제품 쿼리를 Excel 워크시트에 로드합니다.
- 홈>닫기 & 로드를 선택합니다. 쿼리가 새 Excel 워크시트에 나타납니다.
파워 쿼리에서 쿼리 작업을 수행하면 쿼리 단계가 만들어지고 적용된 단계 목록의 쿼리 설정 창에 나열됩니다. 각 쿼리 단계에는 해당하는 파워 쿼리 수식이 있으며 이를 "M" 언어라고도 합니다. 파워 쿼리 수식에 대한 자세한 내용은 파워 쿼리 설명서를 참조하세요.
| 작업 | 쿼리 단계 | 수식 |
|---|---|---|
| Excel 통합 문서 가져오기 | 원본 | = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true) |
| 제품 테이블 선택 | 이동 | = Source{[Item="Products",Kind="Table"]}[Data] |
| 파워 쿼리에서 열 데이터 형식을 자동으로 감지합니다. | 변경된 형식 | = 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"}) |
작업 2: OData 피드에서 주문 데이터 가져오기
이 작업에서는 다음에 있는 샘플 http://services.odata.org/Northwind/Northwind.svcNorthwind OData 피드에서 Excel 통합 문서로 데이터를 가져오고, Order_Details 테이블을 확장하고, 열을 제거하고, 라인 합계를 계산하고, OrderDate를 변환하고, ProductID 및 연도를 기준으로 행을 그룹화하고, 쿼리 이름을 바꾸고, Excel 통합 문서에 쿼리 다운로드를 사용하지 않도록 설정합니다.
- 데이터> 선택OData 피드에서다른 원본>의데이터를> 가져옵니다.
- OData 피드 대화 상자에 Northwind OData 피드의 URL을 입력합니다.
- 확인을 선택합니다.
- 탐색기 창에서 주문 테이블을 두 번 클릭합니다.
이 단계에서는 Orders 테이블과 관련 있는 Order_Details 테이블을 확장하여 Order_Details의 ProductID, UnitPrice, Quantity 열을 Orders 테이블에 결합합니다. 확장 작업은 관련 테이블의 열을 대상 테이블에 결합합니다. 쿼리가 실행되면 관련 테이블(Order_Details)의 행이 기본 테이블(주문)이 있는 행으로 결합됩니다.
파워 쿼리에서 관련 테이블이 포함된 열의 셀에 레코드 또는 테이블 값이 있습니다. 이를 구조적 열이라고 합니다. 레코드는 단일 관련 레코드를 나타내며 현재 데이터 또는 기본 테이블과의 일대일 관계를 나타냅니다. Table 은 관련 테이블을 나타내며 현재 테이블 또는 기본 테이블과의 일대다 관계를 나타냅니다. 구조화된 열은 관계형 모델이 있는 데이터 원본의 관계를 나타냅니다. 예를 들어 구조화된 열은 OData 피드 또는 SQL Server 데이터베이스의 외래 키 관계에 외래 키 연결이 있는 엔터티를 나타냅니다.
Order_Details 테이블을 확장하면 중첩 테이블 또는 관련 테이블의 각 행에 대해 하나씩 세 개의 새 열과 추가 행이 주문 테이블에 추가됩니다.
데이터 미리 보기에서 Order_Details 열까지 가로로 스크롤합니다.
Order_Details 열에서 확장 아이콘(
)을 선택합니다.확장 드롭다운에서 다음을 실행합니다.
모든 열을 지우려면 (모든 열 선택) 을 선택합니다.
ProductID, UnitPrice 및 Quantity를 선택합니다.
확인을 선택합니다.
참고
파워 쿼리에서 열에서 연결된 테이블을 확장하고 연결된 테이블의 열을 집계한 후 제목 테이블의 데이터를 확장할 수 있습니다. 집계 작업을 수행하는 방법에 대한 자세한 내용은 열에서 데이터 집계(파워 쿼리)를 참조하세요.
이 단계에서는 OrderDate, ProductID, UnitPrice 및 Quantity 열을 제외한 모든 열을 제거합니다.
데이터 미리 보기에서 다음 열을 선택합니다.
- 첫 번째 열인 OrderID를 선택합니다.
- Shift+마지막 열인 배송업체를 클릭합니다.
- Ctrl 키를 누른 채로 OrderDate, Order_Details.ProductID, Order_Details.UnitPrice, Order_Details.Quantity 열을 클릭합니다.
선택한 열 머리글을 마우스 오른쪽 단추로 클릭하고 다른 열 제거를 선택합니다.
4단계: 각 Order_Details 행에 대한 줄 합계 계산
이 단계에서는 사용자 지정 열을 만들어 각 Order_Details 행의 총계를 계산합니다.
-
데이터 미리 보기에서 미리 보기의 왼쪽 위 모서리에 있는 테이블 아이콘(
)을 선택합니다. - 사용자 지정 열 추가를 선택합니다.
- 사용자 지정 열 대화 상자의 사용자 지정 열 수식 상자에 [Order_Details.단가] * [Order_Details.수량]을 입력합니다.
- 새 열 이름 상자에 라인 합계를 입력합니다.
- 확인을 선택합니다.
이 단계에서는 OrderDate 열을 변환하여 수주일 연도를 렌더링합니다.
데이터 미리 보기에서 주문 날짜 열을 마우스 오른쪽 단추로 클릭하고변환 연도를선택합니다>.
OrderDate 열의 이름을 Year로 바꿉니다.
- 주문 날짜 열을 두 번 클릭하고 연도 또는
- 주문 날짜 열을 마우스 오른쪽 단추로 클릭하고 이름 바꾸기를 선택한 다음 연도를 입력합니다.
6단계: ProductID 및 연도를 기준으로 행 그룹화
데이터 미리 보기에서 연도와Order_Details.ProductID를 선택합니다.
헤더 중 하나를 마우스 오른쪽 단추로 클릭하고 그룹화 기준을 선택합니다.
그룹화 방법 대화 상자에서 다음을 실행합니다.
- 새 열 이름 텍스트 상자에 Total Sales를 입력합니다.
- 연산 드롭다운에서 합계를 선택합니다.
- 열 드롭다운에서 Line Total을 선택합니다.
확인을 선택합니다.
판매 데이터를 Excel로 가져오기 전에 쿼리 이름을 바꿉니다.
- 쿼리 설정 창의 이름 상자에 Total Sales를 입력합니다.
결과: 작업 2에 대한 최종 쿼리
각 단계를 수행한 후에는 Northwind OData 피드에 대한 Total Sales 쿼리가 있습니다.
요약: 작업 2에서 만든 파워 쿼리 단계
파워 쿼리에서 쿼리 작업을 수행하면 쿼리 단계가 만들어지고 적용된 단계 목록의 쿼리 설정 창에 나열됩니다. 각 쿼리 단계에는 해당하는 파워 쿼리 수식이 있으며 이를 "M" 언어라고도 합니다. 파워 쿼리 수식에 대한 자세한 내용은 파워 쿼리 설명서를 참조하세요.
| 작업 | 쿼리 단계 | 수식 |
|---|---|---|
| OData 피드에 연결 | 원본 | = OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"]) |
| 표 선택 | 탐색 | = 원본{[Name="주문"]}[데이터] |
| 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]) |
| 더 의미 있는 이름으로 변경합니다. 합계 | 이름이 바뀐 열 | = Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}}) |
| OrderDate 열을 변환하여 연도 렌더링 | 추출 연도 | = Table.TransformColumns(#"Grouped Rows",{{"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}}) |
작업 3: Products 및 Total Sales 쿼리 결합
파워 쿼리를 사용하면 여러 쿼리를 병합하거나 추가하여 결합할 수 있습니다. 데이터 원본에 관계없이 테이블 형식의 모든 파워 쿼리 쿼리에 대해 병합 작업을 수행할 수 있습니다. 데이터 원본 결합에 대한 자세한 내용은 여러 쿼리 결합(파워 쿼리)을 참조하세요.
이 작업에서는 병합 쿼리 및 확장 작업을 사용하여 제품 및 총 판매액 쿼리를 결합한 다음 제품별 총 판매액 쿼리를 Excel 데이터 모델에 로드합니다.
1단계: ProductID를 Total Sales 쿼리에 병합
Excel 통합 문서에서 제품 워크시트 탭의 제품 쿼리로 이동합니다.
쿼리에서 셀을 선택한 다음 쿼리>병합을 선택합니다.
병합 대화 상자에서 기본 테이블로 제품을 선택하고 병합할 보조 또는 관련 쿼리로 총 매출을 선택합니다. 총 판매액은 확장 아이콘이 있는 새 구조화된 열이 됩니다.
ProductID를 기준으로 Total Sales를 Products와 일치시키려면 Products 테이블에서 ProductID 열을 선택하고 Total Sales 테이블에서 Order_Details.ProductID 열을 선택합니다.
개인 정보 수준 대화 상자에서 다음을 실행합니다.
- 두 데이터 원본의 개인 정보 격리 수준으로 조직을 선택합니다.
- 저장을 선택합니다.
확인을 선택합니다.
참고
개인 정보 수준은 사용자가 여러 데이터 원본의 데이터를 실수로 결합하지 않도록 하며, 개인 또는 조직으로 설정할 수 있습니다. 쿼리에 따라, 사용자는 개인 데이터 원본의 데이터를 악의적인 다른 데이터 원본에 잘못 전송할 수 있습니다. 파워 쿼리는 각 데이터 원본을 분석하여 공용, 조직, 개인 등 정의된 개인 정보 수준으로 분류합니다. 개인 정보 수준에 대한 자세한 내용은 개인 정보 수준 설정(파워 쿼리)을 참조하세요.
결과
병합 작업은 쿼리를 만듭니다. 쿼리 결과에는 기본 테이블(제품)의 모든 열과 단일 테이블 구조 열에서 관련 테이블(총 판매액)까지 포함됩니다. 확장 아이콘을 선택하여 보조 테이블 또는 관련 테이블에서 기본 테이블에 새 열을 추가합니다.
이 단계에서는 NewColumn 이라는 이름으로 병합된 열을 확장하여 제품 쿼리에 연도 및 총 판매액이라는 두 개의 새 열을 만듭니다.
데이터 미리 보기에서 NewColumn 옆에 있는 확장 아이콘(
)을 선택합니다.확장 드롭다운 목록에서 다음을 수행합니다.
- 모든 열을 지우려면 (모든 열 선택) 을 선택합니다.
- 연도 및 총 매출을 선택합니다.
- 확인을 선택합니다.
이러한 두 열의 이름을 Year 및 Total Sales로 바꿉니다.
어떤 제품과 몇 연도에 가장 많은 판매량을 기록했는지 확인하려면 총 판매량을 기준으로 내림차순 정렬을 선택합니다.
이름 바꾸기를 통해 쿼리의 이름을 Total Sales per Product로 변경합니다.
결과
3단계: 제품당 총 판매액 쿼리를 Excel 데이터 모델에 로드
이 단계에서는 쿼리를 Excel 데이터 모델에 로드하여 쿼리 결과에 연결된 보고서를 작성할 수 있습니다. Excel 데이터 모델에 데이터를 로드한 후에는 파워 피벗을 사용하여 데이터 분석을 더 자세히 수행할 수 있습니다.
- 홈>닫기 & 로드를 선택합니다.
- 데이터 가져오기 대화 상자에서 데이터 모델에 이 데이터 추가를 선택해야 합니다. 이 대화 상자 사용에 대한 자세한 내용을 보려면 물음표(?)를 선택하세요.
결과
Products.xlsx 파일과 Northwind OData 피드의 데이터를 결합한 제품당 총 매출 쿼리가 있습니다. 이 쿼리는 파워 피벗 모델에 적용됩니다. 또한 쿼리를 변경하면 데이터 모델의 결과 테이블이 수정되고 새로 고쳐집니다.
요약: 작업 3에서 만든 파워 쿼리 단계
파워 쿼리에서 병합 쿼리 작업을 수행하면 쿼리 단계가 만들어지고 적용된 단계 목록의 쿼리 설정 창에 나열됩니다. 각 쿼리 단계에는 해당하는 파워 쿼리 수식이 있으며 이를 "M" 언어라고도 합니다. 파워 쿼리 수식에 대한 자세한 내용은 파워 쿼리 설명서를 참조하세요.
| 작업 | 쿼리 단계 | 수식 |
|---|---|---|
| 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}}) |