複数のデータ ソースを組み合わせる方法について説明します (Power Query)

適用先
Excel for Microsoft 365 Excel 2024 Excel 2021

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

このチュートリアルを完了するには、 製品 ブックが必要です。 [名前を付けて保存] ダイアログ ボックスで、ファイルに「製品と注文.xlsx」という名前を付けます。

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

このタスクでは、 製品と Orders.xlsx ファイル (前のセクションでダウンロードして名前を変更した) から Excel ブックに製品をインポートします。 次に、行を列ヘッダーに昇格させ、いくつかの列を削除して、クエリをワークシートに読み込みます。

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

  1. Excel ブックを作成します。
  2. [データ]>データの取得>ファイルから>ブックから] を選択します。
  3. [ データのインポート ] ダイアログ ボックスで、ダウンロードした Products.xlsx ファイルを参照して見つけ、[ 開く] を選択します。
  4. [ナビゲーター] ウィンドウで、[製品] テーブルをダブルクリックします。 Power Query エディターが表示されます。

手順 2: クエリの手順を調べる

既定では、Power Query では利便性のためにいくつかの手順が自動的に追加されます。 詳細については、[クエリ設定] ウィンドウの [適用された手順] の下にある各手順を確認してください。

  1. 「 ソース 」ステップを右クリックし、「 設定の編集」を選択します。 このステップは、ブックをインポートするときに作成されています。
  2. ナビゲーション ステップを右クリックし、[設定の編集] を選択します。 この手順は、[ ナビゲーション] ダイアログ ボックスでテーブルを選択したときに作成されました。
  3. [ 変更されたタイプ ] ステップを右クリックし、[ 設定の編集] を選択します。 この手順は、各列のデータ型を推論する Power Query によって作成されました。 数式バーの右側にある下矢印を選択して、完全な数式を表示します。

手順 3: 他の列を削除して、関心のある列のみを表示する

この手順では、 ProductID、 ProductName、 CategoryID、および QuantityPerUnit を除くすべての列を削除します。

  1. [データのプレビュー] で、[商品コード]、[商品名]、[カテゴリ ID]、[単位あたりの数量] の各列を選択します (Ctrl キー + クリック キーまたは Shift キー + クリック キーを使用)。
  2. [列の削除]> [他の列の削除] を選択します。
    [その他の列を非表示にする] を示すスクリーンショット。

手順 4: 製品クエリを読み込む

この手順では、 Products クエリを Excel ワークシートに読み込みます。

  • [ホーム] >[読み込み & 閉じる] を選択します。 クエリが新しい Excel ワークシートに表示されます。

概要: タスク 1 で作成された Power Query の手順

Power Query でクエリ アクティビティを実行すると、クエリ ステップが作成され、[クエリ設定] ウィンドウの [適用されたステップ] リストに一覧表示されます。 各クエリ ステップには、対応する Power Query の数式があり、"M" 言語とも呼ばれます。 Power Query の数式の詳細については、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 ブックへのクエリのダウンロードを無効にします。

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

  1. [データ]>データの取得>他のソースから>OData フィードから] を選択します。
  2. [OData フィード] ダイアログ ボックスで、Northwind OData フィードの URL を入力します。
  3. [OK] を選択します。
  4. [ナビゲーター] ウィンドウで、[受注] テーブルをダブルクリックします。

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

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

Power Query では、関連テーブルを含む列のセルの値は Record または Table です。 これらは構造化列と呼ばれます。 レコードは 、単一の関連レコードを示し、現在のデータまたはプライマリ テーブルとの 1 対 1 リレーションシップを表します。 Table は関連テーブルを示し、現在のテーブルまたはプライマリ テーブルとの一対多のリレーションシップを表します。 構造化列は、リレーショナル モデルを持つデータ ソース内のリレーションシップを表します。 たとえば、構造化列は、OData フィード内の外部キー関連付けまたは SQL Server データベース内の外部キー関係を持つエンティティを示します。

Order_Details テーブルを展開すると、入れ子になったテーブルまたは関連テーブルの各行に対して 1 つずつ、3 つの新しい列と追加の行が [受注] テーブルに追加されます。

  1. [データのプレビュー] で、[Order_Details] 列まで横方向にスクロールします。

  2. Order_Details列で、展開アイコン () を選択します。

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

    1. (すべての列の選択) を選択して、すべての列をクリアします。

    2. [ 商品コード]、[ 単価]、[ 数量] を選択します。

    3. [OK] を選択します。
      [Order_Detailsテーブルの展開] リンクを示すスクリーンショット。

      注

      Power Query では、列からリンクされたテーブルを展開し、主題テーブルのデータを展開する前にリンク テーブルの列を集計できます。 集計操作を実行する方法の詳細については、「列からデータを集計する (Power Query)」を参照してください。

手順 3: 他の列を削除して、関心のある列のみを表示する

この手順では、[ 受注日]、[ 商品コード]、[ 単価]、[ 数量] 列を除くすべての列を削除します。 

  1. [データのプレビュー] で、次の列を選択します。

    1. 最初の列 ( OrderID) を選択します。
    2. Shift+最後の列 [ 発送元] をクリックします。
    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. [ 新しい列名] ボックスに「 Line Total」と入力します。
  5. [OK] を選択します。

各Order_Details行の明細合計を計算する を示すスクリーンショット。

手順 5: OrderDate year 列を変換する

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

  1. [データのプレビュー] で、[受注日] 列を右クリックし、[変換]>[年] を選択します。

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

    1. [ 受注日 ] 列をダブルクリックし、「 年 」または「
    2. [ 受注日 ] 列を右クリックし、[ 名前の変更] を選択して、「 年」と入力します。

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

  1. [データのプレビュー] で、[年] と [Order_Details.ProductID] を選択します。

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

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

    1. [新しい列名] ボックスで、「Total Sales」と入力します。
    2. [演算] ボックスで、[Sum] を選びます。
    3. [列] ボックスで、"Line Total" を選びます。

  4. [OK] を選択します。
    集計操作の [グループ化] ダイアログ ボックスを示すスクリーンショット。

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

売上データを Excel にインポートする前に、クエリの名前を変更します。

  • [ クエリ設定] ウィンドウの [ 名前 ] ボックスに「 Total Sales」と入力します。

結果: タスク 2 の最終クエリ

各手順を実行すると、Northwind OData フィードに対して Total Sales クエリが生成されます。

Total Sales を示すスクリーンショット。

概要: タスク 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(#"展開Order_Details", "明細合計", each [Order_Details.UnitPrice] * [Order_Details.Quantity])
より意味のある名前 (Lne 合計) に変更します 名前が変更された列 = Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
OrderDate 列を変換して年を表示する 抽出された年 = Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}})
次に変更:
より意味のある名前、 OrderDate and 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 クエリに対してマージ操作を実行できます。 データ ソースの結合の詳細については、「複数のクエリを組み合わせる (Power Query)」を参照してください。

このタスクでは、Merge クエリと Expand 操作を使用して Products クエリと Total Sales クエリを結合し、Total Sales per Product クエリを Excel データ モデルに読み込みます。

手順 1: ProductID を Total Sales クエリにマージする

  1. Excel ブックで、[製品] ワークシート タブの [製品] クエリに移動します。

  2. クエリでセルを選択し、[クエリ>マージ] を選択します。

  3. [ マージ] ダイアログ ボックスで、プライマリ テーブルとして [製品 ] を選択し、マージするセカンダリ クエリまたは関連クエリとして [ 売上合計 ] を選択します。 [Total Sales] は、展開アイコン付きの新しい構造化列になります。

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

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

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

  6. [OK] を選択します。

    注

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

    [差し込み] ダイアログ ボックスを示すスクリーンショット。

結果

マージ操作はクエリを作成します。 クエリの結果には、プライマリ テーブル (製品) のすべての列と、関連するテーブル (売上合計) までの単一のテーブル構造列が含まれます。 [ 展開] アイコンを選択して、セカンダリ テーブルまたは関連テーブルからプライマリ テーブルに新しい列を追加します。

最終版のマージを示すスクリーンショット。

手順 2: 結合された列を展開する

この手順では、結合された列を NewColumn という名前で展開して、[ 製品 ] クエリに 2 つの新しい列 " 年" と "売上合計" を作成します。

  1. [データのプレビュー] で、[新しい列] の横にある [展開] アイコン () を選択します。

  2. [ 展開] ドロップダウン リストで、次の手順を実行します。

    1. (すべての列の選択) を選択して、すべての列をクリアします。
    2. [ 年] と [売上合計] を選択します。
    3. [OK] を選択します。

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

  4. どの製品がどの年に最も売れ上げたかを確認するには、[降順で売上合計で並べ替え] を選択します。

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

結果

[表の展開] リンクを示すスクリーンショット。

手順 3: Excel データ モデルに Total Sales per Product クエリを読み込む

この手順では、 クエリを Excel データ モデルに読み込んで、クエリ結果に接続されたレポートを作成できます。 Excel データ モデルにデータを読み込んだ後、Power Pivot を使用してデータ分析を進めることができます。

  1. [ホーム] >[読み込み & 閉じる] を選択します。
  2. [ データのインポート ] ダイアログ ボックスで、[ このデータをデータ モデルに追加する] を必ず選択します。 このダイアログ ボックスの使用方法の詳細については、疑問符 (?) を選択してください。

結果

Products.xlsx ファイルと Northwind OData フィードのデータが結合された Total Sales per Product クエリがあります。 このクエリは、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)
マージ列を展開する Expanded Total Sales = 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}})

関連項目

Power Query for Excel のヘルプ