Power BI Desktop で一般的なクエリ タスクを実行するPerform common query tasks in Power BI Desktop

Power BI Desktop の Power Query エディター ウィンドウには、一般的に使用される多数のタスクがあります。In the Power Query Editor window of Power BI Desktop, there are a handful of commonly used tasks. この記事では、それらの一般的なタスクについて説明し、追加情報へのリンクを示します。This article demonstrates those common tasks and provides links for additional information.

ここでは、次の一般的なクエリ タスクについて説明します。The common query tasks demonstrated here are:

  • データに接続するConnect to data
  • データの整形と結合Shape and combine data
  • 行のグループ化Group rows
  • 列のピボットPivot columns
  • カスタム列の作成Create custom columns
  • 数式のクエリQuery formulas

これらのタスクを完了するために、いくつかのデータ接続を使用します。We’ll use a few data connections to complete these tasks. これらのタスクの手順をユーザー自身で実行することもできるように、データはダウンロードや接続が可能になっています。The data is available for you to download or connect to, in case you want to step through these tasks yourself.

最初のデータ接続は Excel ブックです。ダウンロードしてローカルに保存できます。The first data connection is an Excel workbook, which you can download and save locally. もう 1 つは、他の Power BI Desktop 記事でも使用されている Web リソースです。The other is a Web resource that's also used in other Power BI Desktop articles:

https://www.bankrate.com/retirement/best-and-worst-states-for-retirement/

一般的なクエリ タスクは、それら両方のデータ ソースへの接続に必要な手順から始まります。Common query tasks begin at the steps necessary to connect to both of those data sources.

データに接続するConnect to data

Power BI Desktop のデータに接続するには、 [ホーム][データの取得] の順に選択します。To connect to data in Power BI Desktop, select Home and then Get Data. Power BI Desktop は、最も一般的なデータ ソースのメニューを表示します。Power BI Desktop presents a menu with the most common data sources. Power BI Desktop が接続できるデータ ソースの完全な一覧を確認するには、メニューの末尾にある [その他] を選択します。For a complete list of data sources to which Power BI Desktop can connect, select More at the end of the menu. 詳細については、「Power BI Desktop のデータ ソース」を参照してください。For more information, see Data sources in Power BI Desktop.

[よく使われる] データ ソース メニュー、[データの取得] ボタン、Power BI Desktop

まず [Excel] を選択し、前述の Excel ブックを指定して、 [開く] を選択します。To start, select Excel, specify the Excel workbook mentioned earlier, and then select Open. テーブルを選択すると、クエリによってブックが検査され、 [ナビゲーター] ダイアログ ボックスで見つかったデータが表示されます。Query inspects the workbook, then presents the data it found in the Navigator dialog box after you select a table.

Excel データ ソース、[ナビゲーター] ダイアログ ボックス、[データの取得]、Power BI Desktop

Power BI Desktop に読み込む前に、 [データの変換] を選択してデータの編集、調整、または "整形" を行うことができます。You can select Transform Data to edit, adjust, or shape, the data before you load it into Power BI Desktop. 編集は、読み込む前に減らしておきたい大規模なデータセットを使用する場合に特に便利です。Editing is especially useful when you work with large datasets that you want to pare down before loading.

さまざまな種類のデータに接続することは簡単です。Connecting to different types of data is as easy. また、Web リソースに接続することもできます。You also want to connect to a Web resource. [データの取得] > [詳細] を選択し、 [その他] > [Web] > [接続] を選択します。Choose Get Data > More, and then select Other > Web > Connect.

Web データ ソース、[データの取得] ダイアログ ボックス、Power BI Desktop

[Web から] ダイアログ ボックスが表示され、ここに Web ページの URL を入力できます。The From Web dialog box appears, where you can type in the URL of the Web page.

[Web から] ダイアログ ボックス、Web データ ソース、[データの取得]、Power BI Desktop

[OK] を選択します。Select OK. 以前と同様に、Power BI Desktop によって Web ページ データが検査され、 [ナビゲーター] ダイアログ ボックスにプレビュー オプションが表示されます。Like before, Power BI Desktop inspects the Web page data and shows preview options in the Navigator dialog box. テーブルを選択すると、データのプレビューが表示されます。When you select a table, it displays a preview of the data.

その他のデータ接続も類似しています。Other data connections are similar. データ接続に認証が必要な場合、Power BI Desktop は適切なユーザーが資格情報を入力するように求めるダイアログを表示します。If authentication is required to make a data connection, Power BI Desktop prompts you for the appropriate credentials.

Power BI Desktop 内のデータに接続する方法を示すステップごとの実例については、「Power BI Desktop におけるデータへの接続」を参照してください。For a step-by-step demonstration of connecting to data in Power BI Desktop, see Connect to data in Power BI Desktop.

データの整形と結合Shape and combine data

Power Query エディターを使用すると、データの整形と結合を簡単に行うことができます。You can easily shape and combine data with Power Query Editor. このセクションでは、データを整形する方法を示すいくつかの例を示します。This section includes a few examples of how you can shape data. データを整形および結合する方法を示す、より詳しい実例は、「Power BI Desktop でのデータの整形と結合」を参照してください。For a more complete demonstration of shaping and combining data, see Shape and combine Data with Power BI Desktop.

前のセクションでは、Excel ブックと Web リソースという 2 組のデータに接続しました。In the previous section, you connected to two sets of data: an Excel workbook and a Web resource. データが Power Query エディターに読み込まれたら、次に示すように、 [クエリ] ペインで使用できるクエリから Web ページ クエリを選択します。After the data is loaded in Power Query Editor, select the Web page query from the available queries in the Queries pane, as shown here:

[クエリ] ペイン、Power Query エディター、Power BI Desktop

データを整形するときには、データ ソースの形式と書式をユーザーの必要に合わせて変換します。When you shape data, you transform a data source into the form and format that meets your needs.

Power Query エディターのリボンとコンテキスト メニューには、多くのコマンドがあります。In Power Query Editor, many commands can be found in the ribbon, and in context menus. たとえば、列を右クリックすると、コンテキスト メニューを使用して列を削除できます。For example, when you right-click a column, the context menu lets you remove the column. また、列を選択してから、リボンの [ホーム] タブから [列の削除] ボタンを選択することもできます。You may also select a column and then select the Remove Columns button from the Home tab in the ribbon.

[列の削除] コマンド、Power Query エディター、Power BI Desktop

このクエリでは、他のさまざまな方法でデータを整形できます。You can shape the data in many other ways in this query. 上または下から任意の数の行を削除できます。You may remove any number of rows from the top or bottom. また、列の追加、列の分割、値の置換などの整形タスクを行うことができます。Or you may add columns, split columns, replace values, and do other shaping tasks. これらの機能を使用すると、好みの方法でデータを取得するように Power Query エディターに指示できます。With these features, you can direct Power Query Editor to get the data how you want it.

行のグループ化Group rows

Power Query エディターでは、複数行の値を 1 つの値にグループ化することができます。In Power Query Editor, you can group the values from many rows into a single value. この機能は、提供される製品の数、総売り上げ高、学生の数などを集計する際に便利です。This feature can be useful when summarizing the number of products offered, the total sales, or the count of students.

この例では、教育登録データ セット内の複数の行をグループ化します。In this example, you group rows in an education enrollment dataset. データは、Excel ブックのものです。The data is from the Excel workbook. Power Query エディターでは、必要な列のみを取得し、テーブルの名前を変更し、他のいくつかの変換を行うように整形されます。It's been shaped in Power Query Editor to get just the columns you need, rename the table, and make a few other transforms.

各州の機関の数を調べてみましょう Let’s find out how many Agencies each state has. (機関には、学区や他の教育機関 (地域のサービス地区など) が含まれる可能性があります)。 [Agency ID - NCES Assigned [District] Latest available year](機関 ID - NCES によって割り当てられた <地区> の最新の使用可能な年) 列を選択し、リボンの [変換] タブまたは [ホーム] タブの [グループ化] ボタンを選択します (Agencies can include school districts, other education agencies such as regional service districts, and more.) Select the Agency ID - NCES Assigned [District] Latest available year column, then select the Group By button in the Transform tab or the Home tab of the ribbon. ( [グループ化] は両方のタブで使用できます)。(Group By is available in both tabs.)

テーブル内の行をグループ化する方法を示すスクリーンショット。

[グループ化] ダイアログ ボックスが表示されます。The Group By dialog box appears. Power Query エディターで行がグループ化されると、 [グループ化] の結果が格納される新しい列が作成されます。When Power Query Editor groups rows, it creates a new column into which it places the Group By results. Group By 操作は、次の方法で調整できます。You can adjust the Group By operation in the following ways:

  1. ラベルが付いていないドロップダウン リストで、グループ化する列を指定します。The unlabeled dropdown list specifies the column to be grouped. Power Query エディターでは、選択した列にこの値が既定で設定されますが、テーブル内の任意の列に変更できます。Power Query Editor defaults this value to the selected column, but you can change it to be any column in the table.
  2. 新しい列名:Power Query エディターからは、グループ化の対象の列に適用される操作に基づいて、新しい列の名前が提案されます。New column name: Power Query Editor suggests a name for the new column, based on the operation it applies to the column being grouped. ただし、新しい列には任意の名前を付けることができます。You can name the new column anything you want, though.
  3. Operation: [合計][中央値][個別の行数のカウント] など、Power Query エディターで適用される操作を選択できます。Operation: You may choose the operation that Power Query Editor applies, such as Sum, Median, or Count Distinct Rows. 既定値は [行数のカウント] です。The default value is Count Rows.
  4. [グループ化の追加][集計の追加] :これらのボタンは、 [詳細] オプションを選択した場合にのみ使用できます。Add grouping and Add aggregation: These buttons are available only if you select the Advanced option. 1 回の操作で、多くの列に対してグループ化操作 ( [グループ化] アクション) を行い、これらのボタンを使用して複数の集計を作成できます。In a single operation, you can make grouping operations (Group By actions) on many columns and create several aggregations using these buttons. このダイアログ ボックスでの選択に基づき、Power Query エディターによって複数の列に対して動作する新しい列が作成されます。Based on your selections in this dialog box, Power Query Editor creates a new column that operates on multiple columns.

[グループの追加] または [集計の追加] を選択すると、 [グループ化] 操作にグループ化または集計を追加できます。Select Add grouping or Add aggregation to add more groupings or aggregations to a Group By operation. グループ化または集計を削除するには、行の右側にある省略記号アイコン ( [...] ) を選択し、 [削除] を選択します。To remove a grouping or aggregation, select the ellipsis icon (...) to the right of the row, and then Delete. 次に進み、既定値を使用して [グループ化] 操作を試して、何が起こるかを確認します。Go ahead and try the Group By operation using the default values to see what occurs.

[グループ化の追加] と [集計の追加] に引き出し線が付けられた [グループ化] ダイアログ ボックスを示すスクリーンショット。

[OK] を選択すると、クエリによって [グループ化] 操作が実行され、結果が返されます。When you select OK, Query does the Group By operation and returns the results. 驚きの結果です。オハイオ州、イリノイ州、テキサス州、カリフォルニア州には、それぞれ 1,000 を超える数の機関があります。Whew, look at that – Ohio, Illinois, Texas, and California now each have over a thousand agencies!

[カウント] 列、[グループ化] 操作、Power Query エディター、Power BI Desktop

Power Query エディターを使うと、最後の整形操作をいつでも削除できます。And with Power Query Editor, you can always remove the last shaping operation. [クエリの設定] ペインの [適用したステップ] で、最近完了したステップの横にある [X] を選択するだけです。In the Query Settings pane, under Applied Steps, just select the X next to the step recently completed. では、試してみましょう。So go ahead and experiment. 結果が気に入らない場合は、Power Query エディターでデータが希望どおりに整形されるまでステップをやり直します。If you don’t like the results, redo the step until Power Query Editor shapes your data the way you want.

列のピボットPivot columns

列をピボットして、列内の一意の値ごとの集計値を含むテーブルを作成できます。You can pivot columns and create a table that contains aggregated values for each unique value in a column. たとえば、各製品カテゴリに含まれる異なる製品数を調べるために、それを行うテーブルをすばやく作成できます。For example, to find out how many different products are in each product category, you can quickly create a table to do that.

例を見てみましょう。Let’s look at an example. 次の Products_by_Categories テーブルは、一意の各製品 (その名前) と、その製品が分類されるカテゴリだけを示すように整形されています。The following Products_by_Categories table has been shaped to only show each unique product (by name), and which category each product falls under. (CategoryName 列に基づいて) 各カテゴリの製品数を示す新しいテーブルを作成するには、列を選択し、 [変換] > [列のピボット] を選択します。To create a new table that shows a count of products for each category (based on the CategoryName column), select the column, then select Transform > Pivot Column.

[列のピボット] コマンド、Power Query エディター、Power BI Desktop

[列のピボット] ダイアログ ボックスが表示され、新しい列 (1) の作成に使用される列の値がわかります The Pivot Column dialog box appears, letting you know which column’s values will be used to create new columns (1). (CategoryName の目的の列名が表示されない場合は、ドロップダウン リストから選択します)。 [詳細] オプション (2) を展開すると、集計値 (3) に適用される関数を選択できます。(If the wanted column name of CategoryName isn't shown, select it from the dropdown list.) When you expand Advanced options (2), you can select the function that will be applied to the aggregated values (3).

[列のピボット] ダイアログ ボックス、Power Query エディター、Power BI Desktop

[OK] を選択すると、 [列のピボット] ダイアログ ボックスで指定された転送指示に従って、クエリによってテーブルが表示されます。When you select OK, Query displays the table according to the transform instructions provided in the Pivot Column dialog box.

[列のピボット] の結果、Power Query エディター、Power BI Desktop

カスタム列の作成Create custom columns

Power Query エディターでは、テーブル内の複数の列に対して動作するカスタム数式を作成できます。In Power Query Editor, you can create custom formulas that operate on multiple columns in your table. これで、そのような数式の結果を新しい (カスタム) 列に配置できるようになります。Then you may place the results of such formulas into a new (custom) column. Power Query エディターを使用すると、簡単にカスタム列を作成できます。Power Query Editor makes it easy to create custom columns.

Power Query エディターで Excel ブックのデータを使用し、リボンの [列の追加] タブに移動し、 [カスタム列] を選択します。With the Excel workbook data in Power Query Editor, go to the Add Column tab on the ribbon, and then select Custom Column.

[カスタム列の追加] コマンド、Power Query エディター、Power BI Desktop

次のダイアログ ボックスが表示されます。The following dialog box appears. この例では、英語学習者 (ELL) である合計学生数のパーセンテージを計算する、「Percent ELL」という名前のカスタム列を作成します。In this example, create a custom column called Percent ELL that calculates the percentage of total students that are English Language Learners (ELL).

[カスタム列] ダイアログ ボックス、Power Query エディター、Power BI Desktop

Power Query エディターの他の適用されるステップと同様に、探しているデータが新しいカスタム列に表示されない場合は、そのステップを削除できます。As with any other applied step in Power Query Editor, if the new custom column doesn’t provide the data you’re looking for, you can delete the step. [クエリの設定] ペインの [適用したステップ] で、 [追加されたカスタム] ステップの横にある [X] を選択します。In the Query Settings pane, under Applied Steps, just select the X next to the Added Custom step.

[適用したステップ]、[クエリの設定] ペイン、Power Query エディター、Power BI Desktop

数式のクエリQuery formulas

Power Query エディターによって生成される手順を編集できます。You can edit the steps that Power Query Editor generates. また、カスタム式を作成することもできます。これを使うと、データに接続してより正確に整形できます。You can also create custom formulas, which let you connect to and shape your data more precisely. Power Query エディターがデータに対してアクションを実行するたびに、アクションに関連付けられた数式が数式バーに表示されます。Whenever Power Query Editor does an action on data, the formula associated with the action is displayed in the formula bar. 数式バーを表示するには、リボンの [表示] タブに移動し、 [数式バー] を選択します。To view the formula bar, go to the View tab of the ribbon, and then select Formula Bar.

[数式バー] オプション、Power Query エディター、Power BI Desktop

Power Query エディターには、各クエリに適用されているすべてのステップが、表示や変更が可能なテキストとして保持されます。Power Query Editor keeps all applied steps for each query as text that you can view or modify. 詳細エディター を使用して、任意のクエリのテキストを表示または変更できます。You can view or modify the text for any query using the Advanced Editor. [表示][詳細エディター] の順に選択します。Just select View and then Advanced Editor.

[詳細エディター] コマンド、Power Query エディター、Power BI Desktop

USA_StudentEnrollment クエリに関連付けられたクエリ手順が表示されている、詳細設定エディター をここに示します。Here's a look at the Advanced Editor, with the query steps associated with the USA_StudentEnrollment query displayed. これらの手順は、しばしば「M」と呼ばれる Power Query 数式言語で作成されています。詳細については、「Power Query 数式の詳細について」を参照してください。These steps are created in the Power Query Formula Language, often referred to as M. For more information, see Learn about Power Query formulas. 言語仕様そのものを確認するには、「Power Query M 言語仕様」を参照してください。To view the language specification itself, see Power Query M language specification.

[詳細エディター] ダイアログ ボックス、Power Query エディター、Power BI Desktop

Power BI Desktop には、数式カテゴリの幅広いセットが備わっています。Power BI Desktop provides an extensive set of formula categories. すべての Power Query エディターの数式の詳細と完全なリファレンスについては、「Power Query M 関数参照」を参照してください。For more information, and a complete reference of all Power Query Editor formulas, see Power Query M function reference.

次の手順Next steps

Power BI Desktop では、あらゆる種類の操作を実行できます。You can do all sorts of things with Power BI Desktop. そのような機能の詳細については、次のリソースを参照してください。For more information on its capabilities, see the following resources: