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