このチュートリアルでは、Power Queryのクエリ エディターを使用して、製品情報を含むローカル Excel ファイルと、製品注文情報を含む OData フィードからデータをインポートできます。 変換と集計の手順を実行し、両方のソースのデータを結合して"製品と年あたりの売上の合計" レポートを生成します。
このチュートリアルを実行するには、 Products ブックが必要です。 [名前を付けて保存] ダイアログ ボックスで、ファイルに「製品と注文.xlsx」という名前を付けます。
作業 1: Excel ブックに製品の情報をインポートする
このタスクでは、製品と Orders.xlsx (上記のダウンロードおよび名前変更) ファイルから Excel ブックに製品をインポートし、行を列ヘッダーに昇格させ、列を削除して、クエリをワークシートに読み込みます。
- Excel ブックを作成します。
- [ Data>Get Data>From File>From Workbook] を選択します。
- [ データのインポート ] ダイアログ ボックスで、ダウンロードした Products.xlsx ファイルを参照して探し、[ 開く] を選択します。
- [ ナビゲーター ] ウィンドウで、[ 製品 ] テーブルをダブルクリックします。 Power Query エディターが表示されます。
既定では、Power Queryは便利な複数の手順を自動的に追加します。 詳細については、[クエリ設定] ウィンドウの [適用された手順] の下の各手順を確認します。
- [ソース] ステップを右クリックし、[設定の編集] を選択します。 この手順は、ブックをインポートしたときに作成されました。
- ナビゲーション ステップを右クリックし、[設定の編集] を選択します。 この手順は、[ ナビゲーション ] ダイアログ ボックスからテーブルを選択したときに作成されました。
- [変更された種類] ステップを右クリックし、[設定の編集] を選択します。 この手順は、各列のデータ型を推論するPower Queryによって作成されました。 数式バーの右側にある下矢印を選択すると、完全な数式が表示されます。
この手順では、ProductID、ProductName、CategoryID、QuantityPerUnit を除くすべての列を削除します。
- [データ プレビュー] で、ProductID、ProductName、CategoryID、QuantityPerUnit の各列を選択します (Ctrl + クリックまたは Shift + クリックを使用)。
- [列の削除]>[その他の列の削除] を選択します。
手順 4: 製品クエリを読み込む
この手順では、 製品 クエリを Excel ワークシートに読み込みます。
- [ ホーム>閉じる & 読み込み] を選択します。 クエリが新しい Excel ワークシートに表示されます。
Power Queryでクエリ アクティビティを実行すると、クエリ ステップが作成され、[クエリ設定] ウィンドウの [適用されたステップ] リストに一覧表示されます。 各クエリ ステップには、対応する Power Query の数式があり、"M" 言語とも呼ばれます。 Power Query数式の詳細については、「Excel でPower Query数式を作成する」を参照してください。
| タスク | クエリのステップ | 数式 |
|---|---|---|
| Excel ブックをインポートする | 発生元 | = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true) |
| [製品] テーブルを選択します | 移動 | = Source{[Item="Products",Kind="Table"]}[Data] |
| 列データ型を自動的に検出するPower Query | Changed Type | = 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 ブックへのクエリのダウンロードを無効にします。
- [ Data>Get Data>From other sources>From OData Feed] を選択します。
- [OData フィード] ダイアログ ボックスで、Northwind OData フィードの URL を入力します。
- [OK] を選択します。
- [ ナビゲーター ] ウィンドウで、[ 受注 ] テーブルをダブルクリックします。
この手順では、Orders テーブルに関連する Order_Details テーブルを展開して、Order_Details テーブルから Orders テーブルに、ProductID 列、UnitPrice 列、Quantity 列を結合します。 配置の操作で、関連テーブルから取得した列を主題のテーブルに結合します。 クエリを実行すると、関連テーブル (Order_Details) の行がプライマリ テーブル (Orders) の行に結合されます。
Power Queryでは、関連するテーブルを含む列のセルに値 Record または Table があります。 これらは構造化列と呼ばれます。 Record は、1 つの関連レコードを示し、現在のデータまたはプライマリ テーブルとの 1 対 1 のリレーションシップを表します。 Table は関連テーブルを示し、現在のテーブルまたはプライマリ テーブルとの 1 対多リレーションシップを表します。 構造化列は、リレーショナル モデルを持つデータ ソース内のリレーションシップを表します。 たとえば、構造化列は、OData フィード内の外部キーの関連付けや、SQL Server データベース内の外部キーリレーションシップを持つエンティティを示します。
Order_Details テーブルを展開すると、入れ子になったテーブルまたは関連するテーブルの各行に 1 つずつ、3 つの新しい列と追加の行が Orders テーブルに追加されます。
[データ プレビュー] で、Order_Details列まで水平方向にスクロールします。
[Order_Details] 列で、展開アイコン (
] ) を選択します。[配置] ボックスで、次の手順に従います。
[ すべての列の選択] を選択 して、すべての列をクリアします。
[ProductID]、[UnitPrice]、[数量] の順に選択します。
[OK] を選択します。
注
Power Queryでは、列からリンクされたテーブルを展開し、件名テーブル内のデータを展開する前にリンク テーブルの列を集計できます。 集計の実行方法の詳細については、「列のデータを集計する」を参照してください。
この手順では、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]」と入力します。
- [ 新しい列名 ] ボックスに「 Line Total」と入力します。
- [OK] を選択します。
この手順では、注文日の年を表示するように、OrderDate 列を変換します。
データ プレビューでOrderDate 列を右クリックし、[変換>Year] を選択します。
OrderDate 列の名前を「Year」に変更する:
- OrderDate 列をダブルクリックして「Year」と入力します。
- [OrderDate] 列に Right-Click、[名前の変更] を選択し、「Year」と入力します。
手順 6: ProductID と Year で行をグループ化する
[データ プレビュー] で、[年] と [Order_Details.ProductID] を選択します。
いずれかのヘッダーを Right-Click し、[ グループ化] を選択します。
[グループ化] ダイアログ ボックスで、次の手順に従います。
- [新しい列名] ボックスで、「Total Sales」と入力します。
- [演算] ボックスで、[Sum] を選びます。
- [列] ボックスで、"Line Total" を選びます。
[OK] を選択します。
![集計操作のための [グループ化] ダイアログ ボックス](media/groupbydialogbox.png)
Excel に販売データをインポートする前に、クエリの名前を変更します。
- [ クエリ設定] ウィンドウの [名前 ] ボックスに「 Total Sales」と入力します。
結果: タスク 2 の最終クエリ
各ステップを実行すると、Northwind OData フィードを対象とした売上合計のクエリが完成します。
概要: タスク 2 で作成Power Query手順
Power Queryでクエリ アクティビティを実行すると、クエリ ステップが作成され、[クエリ設定] ウィンドウの [適用されたステップ] リストに一覧表示されます。 各クエリ ステップには、対応する Power Query の数式があり、"M" 言語とも呼ばれます。 Power Query数式の詳細については、「Power Query数式の詳細」を参照してください。
| タスク | クエリのステップ | 数式 |
|---|---|---|
| OData フィードに接続する | Source | = 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 Total) に変更する | 名前が変更された列 | = 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: 製品と売上合計のクエリを結合する
Power Query では、クエリをマージまたは追加して、複数のクエリを結合することができます。 [マージ] 操作は、データを取得するデータ ソースとは関係なく、テーブルの形式で Power Query のクエリ上で実行されます。 データ ソースの結合の詳細については、「複数のクエリを結合する」を参照してください。
このタスクでは、マージ クエリと展開操作を使用して Products クエリと Total Sales クエリを結合し、製品あたりの売上合計クエリを Excel データ モデルに読み込みます。
手順 1: ProductID を合計売上クエリにマージする
Excel ブックで、[ 製品 ] ワークシート タブの [ 製品 ] クエリに移動します。
クエリでセルを選択し、[ クエリ>Merge] を選択します。
[ マージ ] ダイアログ ボックスで、プライマリ テーブルとして [ 製品 ] を選択し、マージするセカンダリクエリまたは関連クエリとして [Total Sales]\(売上の合計 \) を選択します。 合計売上 は、展開アイコンを含む新しい構造化列になります。
"売上合計" と "製品" を ProductID で照合するには、"製品" テーブルから ProductID 列を選び、"売上合計" テーブルから Order_Details.ProductID 列を選びます。
[プライバシー レベル] ダイアログ ボックス:
- 両方のデータ ソースのプライバシー分離レベルに対して [組織] を選びます。
- [保存] を選択します。
[OK] を選択します。
注
[プライバシー レベル] を設定すると、個人または組織が所有する複数のデータ ソースのデータが、不注意で結合されることがなくなります。 クエリによっては、ユーザーが個人的なデータ ソースから悪意のある別のデータ ソースに不注意でデータを送信する可能性があります。 Power Query では、各データ ソースが分析され、定義済みのプライバシー レベル (公開、組織、非公開) に分類されます。 プライバシー レベルの詳細については、「 プライバシー レベルの設定」を参照してください。
![[マージ] ダイアログ ボックス](media/mergedialogbox.png)
結果
マージ操作によってクエリが作成されます。 クエリ結果には、プライマリ テーブル (Products) のすべての列と、関連テーブル (Total Sales) に対する単一のテーブル構造化列が含まれます。 [ 展開 ] アイコンを選択して、セカンダリ テーブルまたは関連テーブルからプライマリ テーブルに新しい列を追加します。
この手順では、結合された列を NewColumn という名前で展開して、[ 製品 ] クエリで [ 年 ] と [ 売上の合計] の 2 つの新しい列を作成します。
[データ プレビュー] で、[NewColumn] の横にある [展開] アイコン (
) を選択します。[ 展開 ] ドロップダウン リストで、次の操作を行います。
- [ すべての列の選択] を選択 して、すべての列をクリアします。
- [ 年] と [ 合計売上] を選択します。
- [OK] を選択します。
これらの 2 つの列の名前を「Year」と「Total Sales」に変更します。
どの製品とどの年に製品の売上が最も多くなったかを確認するには、[合計売上で降順に並べ替え] を選択します。
クエリの名前を「製品あたりの売上合計」に変更します。
結果
手順 3: 製品ごとの合計売上クエリを Excel データ モデルに読み込む
この手順では、クエリ結果に接続されたレポートを作成するために、 Excel データ モデルにクエリを読み込みます。 Excel データ モデルにデータを読み込んだ後、Power Pivot を使用してデータ分析をさらに進めることができます。
- [ ホーム>閉じる & 読み込み] を選択します。
- [ データのインポート ] ダイアログ ボックスで、[ データ モデルにこのデータを追加する] を選択していることを確認します。 このダイアログ ボックスの使用方法の詳細については、疑問符 (?) を選択してください。
結果
Products.xlsx ファイルと Northwind OData フィードのデータを結合する 製品あたりの売上の合計 クエリがあります。 このクエリは、Power Pivot モデルに適用されます。 さらに、クエリに対する変更によって、データ モデル内の結果のテーブルが変更され、更新されます。
概要: タスク 3 で作成したPower Query手順
Power Queryでクエリ アクティビティのマージを実行すると、クエリ ステップが作成され、[クエリ設定] ウィンドウの [適用されたステップ] の一覧に一覧表示されます。 各クエリ ステップには、対応する Power Query の数式があり、"M" 言語とも呼ばれます。 Power Query数式の詳細については、「Power Query数式の詳細」を参照してください。
| タスク | クエリのステップ | 数式 |
|---|---|---|
| ProductID を売上合計のクエリにマージする | Source ([マージ] 操作のデータ ソース) | = 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"}) |
| 2 つの列の名前を変更する | 名前が変更された列 | = Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}) |
| 合計売上を昇順で並べ替える | 並べ替えられた行 | = Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}}) |