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

By /

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

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 その他…表示数を減らす

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

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

__

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

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

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

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

  2. [データ] > [ブックからファイル > からデータ > を取得 する] を選択します 。

  3. [ データのインポート] ダイアログ ボックスで、ダウンロードした Products.xlsx ファイルを参照して探し、[ 開く] を選択します。

  4. [ ナビゲーター ] ウィンドウで、[ 製品 ] テーブルをダブルクリックします。 Power****クエリ エディター が表示されます。

手順 2: クエリステップを調べる

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

  1. [ソース] ステップを右クリックし、[設定の編集] を選択します。 この手順は、ブックをインポートしたときに作成されました。

  2. ナビゲーション ステップを右クリックし、[設定の編集] を選択します。 この手順は、[ ナビゲーション] ダイアログ ボックスからテーブルを選択したときに作成されました。

  3. [変更された種類] ステップを右クリックし、[設定の編集] を選択します。 この手順は、各列のデータ型を推論するPower Queryによって作成されました。 数式バーの右側にある下矢印を選択すると、完全な数式が表示されます。

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

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

  1. [データ プレビュー] で、ProductID 、ProductName 、CategoryID 、QuantityPerUnit の各列を選択します (Ctrl + クリックまたは Shift + クリックを使用)。

  2. [ 列の削除] > [その他の列の削除] を選択します 。

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

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

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

概要: タスク 1 で作成したPower Query手順

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

タスククエリのステップ数式
Excel ブックをインポートする発生元= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
[製品] テーブルを選択します移動= Source{[Item="Products",Kind="Table"]}[Data]
列データ型を自動的に検出するPower QueryChanged 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) の行がプライマリ テーブル (Orders) の行に結合されます。

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

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

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

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

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

1. [ **すべての列の選択] を選択** して、すべての列をクリアします。

2. **[ProductID**]、[**UnitPrice]** 、[数量] の順に**選択します** 。

3. [**OK**] を選択します。

注: Power Queryでは、列からリンクされたテーブルを展開し、件名テーブル内のデータを展開する前にリンク テーブルの列を集計できます。 集計の実行方法の詳細については、「 target=”_blank” title=”列のデータを集計する” rel=”noopener”>列のデータを集計する」を参照してください。

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

この手順では、OrderDate 列、ProductID 列、UnitPrice 列、Quantity 列を除くすべての列を削除します。

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

2. Shift キーを押しながら最後の列**である Shipper を** クリックします。

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] を選択します。

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

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

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

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

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

2. **[OrderDate**] 列に Right-Click、[**名前の変更**] を選択し、「Year」と入力**します** 。

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

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

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

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

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

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

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

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

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

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

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

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

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

概要: タスク 2 で作成Power Query手順

Power Queryでクエリ アクティビティを実行すると、クエリ ステップが作成され、[クエリ設定] ウィンドウの [適用されたステップ] リストに一覧表示されます。 各クエリ ステップには、対応する Power Query の数式があり、"M" 言語とも呼ばれます。 Power Query数式の詳細については、「 target=”_blank” title=”Power Query数式の詳細” rel=”noopener”>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名前が変更された列 1Table.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 のクエリ上で実行されます。 データ ソースの結合の詳細については、「 target=”_blank” title=”複数のクエリを結合する” rel=”noopener”>複数のクエリを結合する」を参照してください。

このタスクでは、マージ クエリと展開 操作を使用して Products****クエリと****Total Sales クエリを結合し、製品あたりの売上合計クエリを Excel データ モデルに読み込みます。

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

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

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

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

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

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

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

2. [**保存**] を選択します。

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

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

結果

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

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

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

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

  2. [ 展開 ] ドロップダウン リストで、次の操作を行います。

1. [ **すべての列の選択] を選択** して、すべての列をクリアします。

2. [ **年]** と [ **合計売上]** を選択します。

3. [**OK**] を選択します。

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

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

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

結果

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

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

  1. [ ホーム ]> [& 読み込みを閉じる] を選択します。

  2. [データのインポート ] ダイアログ ボックスで、[ データ モデルにこのデータを追加 する] を選択していることを確認します。 このダイアログ ボックスの使用方法の詳細については、疑問符 (?) を選択してください。

結果

Products.xlsx ファイルと Northwind OData フィードのデータを結合する 製品あたりの売上の合計 クエリがあります。 このクエリは、Power Pivot モデルに適用されます。 さらに、クエリに対する変更によって、データ モデル内の結果のテーブルが変更され、更新されます。

概要: タスク 3 で作成したPower Query手順

Power Queryでクエリ アクティビティのマージを実行すると、クエリ ステップが作成され、[クエリ設定] ウィンドウの [適用されたステップ] の一覧に一覧表示されます。 各クエリ ステップには、対応する Power Query の数式があり、"M" 言語とも呼ばれます。 Power Query数式の詳細については、「 target=”_blank” title=”Power Query数式の詳細” rel=”noopener”>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}})

関連項目

Excel ヘルプのPower Query

上部へスクロール