Microsoft アカウントでサインイン
サインインまたはアカウントを作成してください。
こんにちは、
別のアカウントを選択してください。
複数のアカウントがあります
サインインに使用するアカウントを選択してください。

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

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

このタスクでは、製品と 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. データプレビューで、ProductID、ProductName、CategoryID、QuantityPerUnit の各列を選択します (Ctrl + クリックまたは Shift + クリックを使用します)。

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

    他の列を非表示にする

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

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

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

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

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

タスク

クエリのステップ

数式

ブックをExcelする

Source/データベース元

= 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", {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}}

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

他の列を削除しました

= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

このタスクでは、http://services.odata.org/Northwind/Northwind.svc のサンプル Northwind OData フィードから Excel ブックにデータをインポートし、Order_Detailsテーブルを展開し、列を削除し、行の合計を計算し、OrderDate を変換し、ProductID と Year で行をグループ化し、クエリの名前を変更し、Excel ブックへのクエリのダウンロードを無効にします。

手順 1: Connect OData フィードにアクセスする

  1. [Data >Get Data> From Other Sources > From OData Feed ] を選択します。

  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 では、関連テーブルを含む列のセルの値が [レコード] または[テーブル ] になります。 これらは構造化列と呼ばれる。 レコードは、1 つの関連レコードを示し、現在のデータまたはプライマリ テーブルとの一対 一リレーションシップを表します。 テーブルは、関連テーブルを示し、現在のテーブルまたはプライマリ テーブルとの一対多リレーションシップを表します。 構造化列は、リレーショナル モデルを持つデータ ソース内のリレーションシップを表します。 たとえば、構造化列は、OData フィード内の外部キーの関連付けを持つエンティティ、またはデータベース内の外部キー関係SQL Serverします。

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

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

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

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

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

    2. [ProductID]、[ 単価]、および[数量] を選択します。

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

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

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

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

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

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

    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. [新しい 列名] ボックスに、「 行の合計」 と入力します。

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

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

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

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

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

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

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

    2. 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 の数式の詳細については、「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}})

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

このタスクでは、マージ クエリと展開操作を使用して製品クエリと売上合計クエリを組み合わせ、製品ごとの売上合計クエリを Excel データ モデルに読み込む。

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

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

  2. クエリ内のセルを選択し、[クエリと結合 ] >選択します。

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

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

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

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

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

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

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

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

結果

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

最終的なマージ

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

この手順では、マージされた列をNewColumnという名前で展開し、製品クエリに Year とTotal Sales の 2 つの新しい列を作成します。

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

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

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

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

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

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

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

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

結果

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

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

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

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

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

結果

[製品ごとの 売上合計] クエリでは 、データ ファイルと Northwind OData フィードProducts.xlsxを組み合わせたものになります。 このクエリは、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(ソース, "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 ヘルプ

Facebook LinkedIn メール

ヘルプを表示

スキルを磨く

トレーニングの探索 >

新機能を最初に入手

Microsoft 365 Insider に参加する >

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

言語の品質にどの程度満足していますか?
どのような要因がお客様の操作性に影響しましたか?

フィードバックをいただき、ありがとうございます。

×