複数のデータ ソースからデータを結合する (Power Query)

注:  最新のヘルプ コンテンツをできるだけ早く、お客様がお使いの言語で提供したいと考えております。 このページは、自動翻訳によって翻訳されているため、文章校正のエラーや不正確な情報が含まれている可能性があります。私たちの目的は、このコンテンツがお客様の役に立つようにすることです。お客様にとって役立つ情報であったかどうかを、このページの下部でお知らせください。 簡単に参照できるように、こちらに 英語の記事 があります。

注: Power Queryは、取得し、変換に含まれる Excel 2016 と呼ばれます。ここで提供される情報は、両方に適用されます。詳細については、取得するし Excel 2016 で変換を参照してください。

このチュートリアルでは、製品の情報を格納し、OData フィードから注文の製品情報が含まれていますローカルの Excel ファイルからデータをインポートするのには、Power Query のクエリ エディターを使用します。変換と集計の手順を実行して、製品と年あたりの売上合計のレポートを生成する両方のソースからデータを結合します。

このチュートリアルを実行するために、製品と注文のブックを作成する必要があります。名前を付けて保存] ダイアログ ボックスで、製品と注文ファイルに名前します。

このチュートリアルの内容

作業 1: Excel ブックに製品の情報をインポートする

手順 1: Excel ブックに接続する

手順 2: 1 行目をテーブルの列見出しにする

手順 3: 対象とする列のみを表示するために他の列を削除する

作成された Power Query のステップ

手順 4: 製品のクエリをインポートする

作業 2: OData フィードから注文データをインポートする

手順 1: OData フィードに接続する

手順 2: Order_Details テーブルを展開する

Order_Details テーブル リンクを展開する

手順 3: 対象とする列のみを表示するために他の列を削除する

選んだ列を削除する

手順 4: Order_Details の各行で行の合計を計算する

各 Order_Details 行の明細金額を計算する

手順 5: OrderDate 列を Year 列に変換する

手順 6: ProductID と Year で行をグループ化する

手順 7: クエリの名前を変更する

最終的なクエリの結果

作成された Power Query のステップ

手順 8: Excel ブックへのクエリ ダウンロードを無効にする

クエリのダウンロードを無効にする

作業 3: 製品と売上合計のクエリを結合する

手順 1: ProductID を売上合計のクエリにマージする

手順 2: マージ列を展開します。

NewColumn テーブル リンクを展開する

作成された Power Query のステップ

手順 3: 製品あたりの売上合計のクエリを Excel データ モデルにロードする

製品あたりの売上合計のクエリを Excel データ モデルにロードする

製品あたりの売上合計の最終的なクエリ

作業 1: Excel ブックに製品の情報をインポートする

この作業で Excel ブックに製品と注文ファイルから製品をインポートします。

手順 1: Excel ブックに接続する

  1. Excel ブックを作成します。

  2. POWER QUERY のリボン タブで、[ファイルから]、[Excel から] の順にクリックします。

  3. Excel の [参照] ダイアログ ボックスで、製品と注文.xlsx ファイルを参照して選びます。または、インポートするこのファイルのパス、あるいはこのファイルへのリンクを入力します。

  4. [ナビゲーター] ウィンドウで [製品] ワークシートをダブルクリックするか、[製品] をクリックして、[クエリの編集] をクリックします。 クエリを編集するとき、または新しいデータ ソースに接続するときに、[クエリ エディター] ウィンドウが表示されます。

    注: クエリ エディターの表示方法に関するビデオについては、この記事の最後を参照してください。

手順 2: 1 行目をテーブルの列見出しにする

[Query Preview] グリッドのテーブルでは、1 行目にテーブルの列名が表示されません。1 行目をテーブルの列見出しにするには:

  1. データ プレビューの左上隅にあるテーブル アイコン ( [テーブル] アイコン ) をクリックします。

  2. [先頭の行を見出しとして使用] をクリックします。

テーブルの最初の行を列見出しにする

手順 3: 対象とする列のみを表示するために他の列を削除する

この手順では、ProductIDProductNameCategoryIDQuantityPerUnit を除くすべての列を削除します。

  1. [Query Preview] グリッドで、ProductID 列、ProductName 列、CategoryID 列、QuantityPerUnit 列を選びます (Ctrl + クリックまたは Shift + クリックで)。

  2. [クエリ エディター] リボンで [列の削除]、[他の列の削除] の順にクリックするか、列ヘッダーを右クリックして [他の列の削除] をクリックします。

    他の列を非表示にする

作成された Power Query のステップ

Power Query でクエリの操作を実行する場合、クエリのステップが作成され、[クエリの設定] ウィンドウの [適用したステップ] リストに一覧表示されます。各クエリ ステップには、対応する Power Query の数式があり、"M" 言語とも呼ばれます。Power Query の数式の言語の詳細については、「Power Query の式について」を参照してください。

タスク

クエリのステップ

数式

Excel ブックに接続する

Source

Source{[Name="Products"]}[Data]

1 行目をテーブルの列見出しにする

FirstRowAsHeader

Table.PromoteHeaders

(Products)

対象とする列のみを表示するために他の列を削除する

RemovedOtherColumns

Table.SelectColumns

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

手順 4: 製品のクエリをインポートする

この手順では、Excel ブックに [製品] クエリをインポートします。

  1. クエリ エディター リボンで、[適用して閉じる] をクリックします。結果が、Excel の新しいブックに表示されます。

ページの先頭へ

作業 2: OData フィードから注文データをインポートする

この作業では、サンプルの Northwind OData フィード (http://services.odata.org/Northwind/Northwind.svc) からデータを Excel ブックにインポートします。

手順 1: OData フィードに接続する

  1. POWER QUERY のリボン タブで、[その他のソース]、[OData フィードから] の順にクリックします。

  2. [OData フィード] ダイアログ ボックスで、Northwind OData フィードの URL を入力します。

  3. [OK] をクリックします。

  4. [ナビゲーター] ウィンドウで、Orders テーブルをダブルクリックするか、Orders をクリックしてから [編集] をクリックします。

注: テーブルにマウス ポインターを合わせると、テーブル プレビューのフライアウトが表示されます。

データ ソースをマウスでポイントする

手順 2: Order_Details テーブルを展開する

この手順では、Orders テーブルに関連する Order_Details テーブルを展開して、Order_Details テーブルから Orders テーブルに、ProductID 列、UnitPrice 列、Quantity 列を結合します。配置の操作で、関連テーブルから取得した列を主題のテーブルに結合します。クエリを実行すると、関連テーブル (Order_Details) の行が主題のテーブル (Orders) の行に結合されます。

Power Query では、関連テーブルへのリンクを含む列には、[エントリ] リンクまたは [テーブル] リンクが設定されます。[エントリ] リンクをクリックすると、関連する 1 件のレコードが表示されますが、これは主題のテーブルに対して 1 対 1 の関係になることを表しています。[テーブル] リンクをクリックすると、関連テーブルが表示されますが、これは主題のテーブルに対して 1 対多の関係になることを表しています。リレーショナル モデルでは、リンクはデータ ソース内を移動するためのプロパティになります。OData フィードの場合、エンティティと外部キーの関連付けが移動のためのプロパティになります。SQL Server などのデータベースでは、データベース内の外部キー リレーションシップが、移動のためのプロパティになります。

Order_Details テーブル リンクを展開する

Order_Details テーブルを展開した後、3 つの新しい列と追加の行が Orders テーブルに結合され、入れ子のテーブルまたは関連付けられたテーブルの各行に保存されます。

  1. [Query Preview] ウィンドウで、Order_Details 列までスクロールします。

  2. [ Order_Details ] 列で、展開アイコン ( 展開 ) をクリックします。

  3. [配置] ボックスで、次の手順に従います。

    1. [すべての列を選択] をクリックして、すべての列をオフにします。

    2. ProductIDUnitPriceQuantity をクリックします。

    3. [OK] をクリックします。

      Order_Details テーブル リンクを展開する

      注: Power Query では、列との関連でリンクを設定したテーブルを展開できますが、さらに主題のテーブルでデータを展開する前に、リンクされたテーブルの列で集計を実行できます。集計の実行方法の詳細については、「列のデータを集計する」を参照してください。

手順 3: 対象とする列のみを表示するために他の列を削除する

この手順では、OrderDate 列、ProductID 列、UnitPrice 列、Quantity 列を除くすべての列を削除します。前述の作業では、[他の列の削除] を使いました。この作業では、選んだ列を削除します。

選んだ列を削除する

  1. [Query Preview] ウィンドウで、すべての列を選びます。

    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. [OK] をクリックします。

各 Order_Details 行の明細金額を計算する

手順 5: OrderDate 列を Year 列に変換する

この手順では、注文日の年を表示するように、OrderDate 列を変換します。

  1. [プレビュー] グリッドで OrderDate 列を右クリックして、[変換]、[Year] の順にクリックします。

  2. OrderDate 列の名前を「Year」に変更する:

    1. OrderDate 列をダブルクリックして「Year」と入力します。

    2. または、OrderDate 列を右クリックして [名前の変更] をクリックし、「Year」と入力します。

手順 6: ProductID と Year で行をグループ化する

  1. [Query Preview] グリッドで、YearOrder_Details.ProductID を選びます。

  2. いずれかの見出しを右クリックし、[グループ化] をクリックします。

  3. [グループ化] ダイアログ ボックスで、次の手順に従います。

    1. [新しい列名] ボックスで、「Total Sales」と入力します。

    2. [演算] ボックスで、[Sum] を選びます。

    3. [] ボックスで、"Line Total" を選びます。

  4. [OK] をクリックします。

    集計操作のための [グループ化] ダイアログ ボックス

手順 7: クエリの名前を変更する

Excel に売上データをインポートする前に、クエリに「売上合計」という名前を付ける:

  1. [クエリの設定] ウィンドウの [名前] ボックスに「売上合計」と入力します。

最終的なクエリの結果

各ステップを実行すると、Northwind OData フィードを対象とした売上合計のクエリが完成します。

売り上げ総額

作成された Power Query のステップ

Power Query でクエリの操作を実行する場合、クエリのステップが作成され、[クエリの設定] ウィンドウの [適用したステップ] リストに一覧表示されます。各クエリ ステップには、対応する Power Query の数式があり、"M" 言語とも呼ばれます。Power Query の数式の言語の詳細については、「Power Query の式について」を参照してください。

タスク

クエリのステップ

数式

OData フィードに接続する

Source

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 の各行で行の合計を計算する

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、{「年」,"Order_Details.ProductID"}, {{「売上合計」、([Line Total])、各 List.Sum 番号を入力}})

手順 8: Excel ブックへのクエリ ダウンロードを無効にする

"売上合計" クエリでは最終的な "製品および年あたりの売上合計" レポートは作成されないため、Excel ブックへのクエリ ダウンロードを無効にします。[クエリの設定] ウィンドウで [ワークシートへの読み込み] オプションが [オフ] になっている場合、このクエリの処理結果のデータはダウンロードされません。ただし、必要な結果を作成するために、このクエリを他のクエリと結合することはできます。次の作業では、このクエリと製品クエリを結合する方法について説明します。

クエリ ダウンロードを無効にする

  1. [クエリの設定] ウィンドウで、[ワークシートへの読み込み] チェック ボックスをオフにします。

  2. クエリ エディター リボンで、[適用して閉じる] をクリックします。[ブック クエリ] ウィンドウで、"売上合計" クエリに [読み込みが無効です] と表示されます。

    クエリのダウンロードを無効にする

ページの先頭へ

作業 3: 製品と売上合計のクエリを結合する

Power Query では、クエリをマージまたは追加して、複数のクエリを結合することができます。[マージ] 操作は、データを取得するデータ ソースとは関係なく、テーブルの形式で Power Query のクエリ上で実行されます。データ ソースの結合の詳細については、「複数のクエリを結合する」を参照してください。

この作業では、[マージ] と [配置] のクエリ ステップを使って、"製品""売上合計" クエリを結合します。

手順 1: ProductID を売上合計のクエリにマージする

  1. Excel ブックで、Sheet2 の "製品" クエリに移動します。

  2. QUERY のリボン タブで、[マージ] をクリックします。

  3. [マージ] ダイアログ ボックスで、プライマリ テーブルとして "製品" を選び、セカンダリ テーブルとして、またはマージする関連クエリとして "売上合計" を選びます。"売上合計" は、展開可能な新しい列になります。

  4. "売上合計" と "製品" を ProductID で照合するには、"製品" テーブルから ProductID 列を選び、"売上合計" テーブルから Order_Details.ProductID 列を選びます。

  5. [プライバシー レベル] ダイアログ ボックス:

    1. 両方のデータ ソースのプライバシー分離レベルに対して [組織] を選びます。

    2. [保存] をクリックします。

  6. [OK] をクリックします。

    セキュリティ メモ: [プライバシー レベル] を設定すると、個人または組織が所有する複数のデータ ソースのデータが、不注意で結合されることがなくなります。クエリによっては、ユーザーが個人的なデータ ソースから悪意のある別のデータ ソースに不注意でデータを送信する可能性があります。Power Query では、各データ ソースが分析され、定義済みのプライバシー レベル (公開、組織、非公開) に分類されます。プライバシー レベルの詳細については、「プライバシー レベル」を参照してください。

    [マージ] ダイアログ ボックス

[OK] をクリックすると、[マージ] 操作が行われ、クエリが作成されます。クエリの結果には、プライマリ テーブル (製品) の全列と、関連テーブル (売上合計) へのナビゲーション リンクが設定された 1 列が含まれます。配置操作では、関連テーブルから、プライマリ テーブルまたは主題のテーブルに新しい列が追加されます。

最終的なマージ

手順 2: マージ列を展開する

この手順で、"製品"クエリで 2 つの新しい列を作成するのには、 NewColumnという名前でマージ列を展開する:売上合計します。

NewColumn テーブル リンクを展開する

  1. [Query Preview ] グリッドで [ NewColumn ] をクリックします ( 展開 )] アイコンを展開します。

  2. [配置] ボックスで、次の手順に従います。

    1. [すべての列を選択] をクリックして、すべての列をオフにします。

    2. "Year" と "Total Sales" をクリックします。

    3. [OK] をクリックします。

  3. これらの 2 つの列の名前を「Year」と「Total Sales」に変更します。

  4. "Total Sales" で [降順で並べ替え] を選ぶと、売上が最も多かった製品と年がわかります。

  5. クエリの名前を「製品あたりの売上合計」に変更します。

テーブルのリンクを展開する

作成された Power Query のステップ

Power Query で [マージ] クエリの操作を実行する場合、クエリのステップが作成され、[クエリの設定] ウィンドウの [適用したステップ] リストに一覧表示されます。各クエリ ステップには、対応する Power Query の数式があり、"M" 言語とも呼ばれます。Power Query の数式の言語については、「Power Query の式について」を参照してください。

タスク

クエリのステップ

数式

ProductID を売上合計のクエリにマージする

Source ([マージ] 操作のデータ ソース)

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 データ モデルにロードする

このステップでは、クエリの結果に連動するレポートを作成するために、[ワークシートへの読み込み] オプションを無効にして、クエリを Excel データ モデルに読み込みます。クエリ結果を Excel ワークシートに読み込むだけではなく、Power Query では、クエリの結果を Excel データ モデルに読み込むことができます。Excel データ モデルにデータをロードすると、PowerPivot と Power View を使ってさらにデータを分析できます。

製品あたりの売上合計のクエリを Excel データ モデルにロードする

  1. [クエリの設定] ウィンドウで、[ワークシートへの読み込み] チェック ボックスをオフにして、[データ モデルへの読み込み] をオンにします。

  2. クエリを Excel データ モデルに読み込むには、[適用して閉じる] をクリックします。

Excel のデータ モデルを読み込む

製品あたりの売上合計の最終的なクエリ

各ステップを実行すると、製品および注文.xlsx ファイルと Northwind OData フィードのデータを結合する "製品あたりの売上合計" クエリが完成します。このクエリは、PowerPivot モデルに適用できます。またこれで Power Query 内のクエリが変更され、PowerPivot モデル内で処理結果テーブルが変更の後で更新されます。

ページの先頭へ

注: クエリ エディターは、読み込み、編集、またはPower Queryを使って新しいクエリを作成するときにのみ表示されます。次のビデオでは、Excel ブックからクエリを編集した後に表示される [クエリ エディター ] ウィンドウが表示されます。読み込みまたはPower Queryリボン タブで、[外部データの取り込み] セクションから、既存のブック クエリを編集するにはせずに、クエリ エディターを表示するのには、選択他のデータ ソースから > 空のクエリします。次のビデオでは、 [クエリ エディターを表示する 1 つの方法を説明します。

Excel でクエリ エディターを表示する方法

Office のスキルを磨く
トレーニングの探索
新機能を最初に入手
Office Insider に参加する

この情報は役に立ちましたか?

ご意見をいただきありがとうございます。

フィードバックをお寄せいただき、ありがとうございます。Office サポートの担当者におつなぎいたします。

×