Excel で Power Query 式を作成する

Power Query エディターを使用して、Power Query の数式を作成しました。 Power Query のしくみを見て見てみしましょう。 Power Query エディターの動作を確認して、数式を更新または追加する方法を学習できます。  高度なエディターを使用して、独自の数式をロールすることもできます。           

Power Query エディターは、さまざまなデータ ソースからのデータのExcelに使用できるデータ クエリとデータの整形エクスペリエンスを提供します。 [Power Query エディター] ウィンドウを表示するには、Excel ワークシートの外部データ ソースからデータをインポートし、データ内のセルを選び、[クエリ] >編集] を選択します。 主なコンポーネントの概要を次に示します。

クエリ エディターの要素

  1. データの整形に使用する Power Query エディター リボン

  2. データ ソースとテーブルの検索に使用する [クエリ] ウィンドウ

  3. リボンのコマンドのショートカットに便利なコンテキスト メニュー

  4. データに適用された手順の結果を表示するデータ プレビュー

  5. クエリのプロパティ設定各ステップを一覧表示する [クエリ] ウィンドウ

背後では、クエリの各ステップは、数式バーに表示される数式に基づいて行います。

クエリ エディターの数式例

数式を変更または作成する場合があります。 数式では、単純な式と複雑な式の両方を構築するために使用できる Power Query 数式言語を使用します。 構文、引数、解説、関数、および例の詳細については、「Power Query M の数式言語」を参照してください

例として、サッカー の競技のリストを使用して、Power Query を使用して、Web サイトで見つかった生データを取得し、それを整形式のテーブルに変換します。 [Query 設定] ウィンドウの [適用されたステップ] と [数式バー]で、各タスクのクエリステップと対応する数式がどのように作成されるのかをご確認ください

ご利用のブラウザーではビデオがサポートされていません。

手順

  1. データをインポートするには、[Webからデータ>] を選択し、[URL]ボックスに「http://en.wikipedia.org/wiki/UEFA_European_Football_Championship」と入力し、[OK] を選択します

  2. [ナビゲーター ] ダイアログ ボックスで、左側の [結果[編集] テーブルを選択し、下部にある [データの変換 ] を選択します。 Power Query エディターが表示されます。

  3. 既定のクエリ名を変更するには、[クエリ] ウィンドウの [プロパティ] 設定[Results [Edit]] を削除し、「UEFAのチャンピオン」と入力します。

  4. 不要な列を削除するには、最初、4 番目、5 番目の列を選択し、[ホーム]> [列の削除] >その他の列を削除] を選択します

  5. 不要な値を削除するには、[列 1]を選択し、[ホーム]> [値の置換] を選択し、[検索する値] ボックスに「詳細」と入力し、[OK] を選択します

  6. "Year" という単語が含む行を削除するには、[ 列 1]でフィルター矢印を選択し、[年] の横にあるチェック ボックスをオフにし 、[OK]を選択します。

  7. 列見出しの名前を変更するには、各見出しをダブルクリックし、"Column1" を "Year"、"Column4" を "Winner"、"Column5" を "Final Score" に変更します。

  8. クエリを保存するには、[ホーム] を選択 > を閉&読み込む] を選択します

結果

チュートリアルの結果 - 最初の数行

次の表は、適用された各ステップと対応する数式の概要を示しています。

クエリのステップとタスク

Source

Web データ ソースに接続する

= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))

Navigation

接続するテーブルを選択する

=Source{2}[Data]

Changed Type

データ型を変更する (Power Query によって自動的に実行される)

= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})

他の列を削除しました

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

= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})

置き換えられた値

値を置き換え、選択した列の値をクリーンアップする

= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})

フィルター処理された行

列で値をフィルター処理する

= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))

列の名前が変更されました

列見出しを意味のあるものに変更しました

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

大事な    ソース、ナビゲーション、変更された種類の各手順は、Power Query によって作成され、データ ソースを定義および設定するために作成されます  

数式バーの表示と非表示を切り替える

数式バーは既定で表示されますが、表示されない場合は再表示できます。

  • [数式バー] >レイアウト>表示] を選択します

数式バーで数式を入力する

  1. クエリを開く場合は、Power Query Editor から以前に読み込まれたクエリを探し、データ内のセルを選択し、[クエリ] を選択して 、[編集]>します。 詳細については、「クエリの作成、読み込み、または編集」を参照Excel。

  2. [クエリの設定]ウィンドウの [適用されたステップ] で、編集するステップを選択します。

  3. 数式バーで、パラメーター値を見つけて変更し、[Enter キーを押す] Power Query の数式バーの左側にある Enter アイコン 選択するか、Enter キーを押します。 たとえば、Column2:

    Before: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
    After:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. Enter キーを押 Power Query の数式バーの左側にある Enter アイコン または Enter キーを押して、データ プレビューに表示される新しい結果を確認します。

  5. ワークシートの結果を表示するには、[ホーム] Excelを選択し、[読み込>閉&します

数式バーに数式を作成する

簡単な数式の例では 、Text.Proper関数を使用して、テキスト値を適切な大文字と小文字に変換します。

  1. 空のクエリを開く場合は、Excelクエリからデータ>データ>取得] を>選択します。 詳細については、「クエリの作成、読み込み、または編集」を参照Excel。

  2. 数式バーに「」と入力=Text.Proper("text value")、Enter キーを押 Power Query の数式バーの左側にある Enter アイコン または Enter キーを押します。

    結果は データ プレビュー に表示されます。

  3. ワークシートの結果を表示するには、[ホーム] Excelを選択し、[読み込>閉&します

結果:

Text.Proper

 数式を作成すると、Power Query によって数式の構文が検証されます。 ただし、クエリで中間ステップを挿入、並べ替え、または削除すると、クエリが壊れる可能性があります。  常にデータ プレビューで結果を確認してください。

大事な    ソース、ナビゲーション、変更された種類の各手順は、Power Query によって作成され、データ ソースを定義および設定するために作成されます  

ダイアログ ボックスを使用して数式を編集する

このメソッドでは、手順によって異なるダイアログ ボックスを使用します。 数式の構文を知る必要はない。

  1. クエリを開く場合は、Power Query Editor から以前に読み込まれたクエリを探し、データ内のセルを選択し、[クエリ] を選択して 、[編集]>します。 詳細については、「クエリの作成、読み込み、または編集」を参照Excel。

  2. [クエリ 設定] ウィンドウの[適用されたステップ] で、編集するステップの [設定 設定アイコン の編集] アイコンを選択するか、ステップを右クリックして、[編集] 設定 を選択します。

  3. ダイアログ ボックスで変更を行い 、[OK] を選択します

ステップを挿入する

データを再設定するクエリ ステップを完了すると、現在のクエリ ステップの下にクエリ ステップが追加されます。 ただし、手順の途中にクエリ ステップを挿入すると、後続の手順でエラーが発生する可能性があります。 Power Queryでは、新しいステップを挿入しようとして、新しいステップによって、挿入された手順に従う任意の手順で使用されるフィールド (列名など) が変更された場合に、ステップの挿入に関する警告が表示されます。

  1. [クエリの設定]ウィンドウの [適用されたステップ] で、新しいステップとその対応する数式の直前に表示するステップを選択します。

  2. 数式バー の左側 関数のアイコン ステップの追加] アイコンを選択します。 または、ステップを右クリックし、[後にステップを挿入 ] を選択します。という形式で新しい数式が作成= <nameOfTheStepToReference>( など)

    =Production.WorkOrder

  3. 次の形式で新しい数式
    を入力=Class.Function(ReferenceStep[,otherparameters])


    たとえば、Gender 列を含むテーブルを作成し、値 "Ms" の列を追加するとします。 または "Mr." を選択します。その人の性別に応じて異なる場合があります。 数式は次の式

    =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

数式の例

ステップを並べ替える

  • [クエリ]ウィンドウ設定[適用されたステップ] で、ステップを右クリックし、[上へ移動] または [下へ移動]を選択します

ステップの削除

  • 手順の左側 ステップの削除 削除] アイコンを選択するか、ステップを右クリックして、[削除] または [終了するまで削除]を選択します。 数式 バー ステップの削除 の [削除] アイコンも使用できます。

この例では、高度なエディターで数式の組み合わせを使用して、列のテキストを適切な大文字と小文字に変換します。 

たとえば、Orders という名前Excel、ProductName 列を適切なケースに変換するテーブルがあります。 

前

ステップ 4 - 結果

高度なクエリを作成する場合は、let 式に基づいて一連のクエリ式ステップを作成します。 let 式 を使用 して名前を割り当て、ステップを定義する in 句によって参照される値を計算します。 この例では、"数式バーに数式を作成する" セクションの結果と同じ結果が返されます。

let  
    Source = Text.Proper("hello world")
in  
    Source  

各ステップは、名前でステップを参照することで、前の手順に基付けされているのが分かっています。 Power Query の数式言語では、大文字と小文字が区別されます。

フェーズ 1: 詳細エディターを開く

  1. [Excel] で、[データの取得>その他>クエリ] >選択します。 詳細については、「クエリの作成、読み込み、または編集」を参照Excel。

  2. Power Query エディターで 、[ホーム] >エディター ] を選択します。このエディターが開き、let式のテンプレートが表示されます。

詳細エディター 2

フェーズ 2: データ ソースを定義する

  1. 次のコマンド使用して let式をExcel。CurrentWorkbook関数:



    let    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]in
        

    Source ステップ 1 - 詳細エディター

  2. クエリをワークシートに読み込むには、[完了] を選択し、[ホーム]>閉じる] &み込>を&します

結果:

ステップ 1 - 結果

フェーズ 3: 最初の行をヘッダーに昇格する

  1. クエリを開く場合は、ワークシートからデータ内のセルを選択し、[クエリ] を選択し、[編集] >選択します。 詳細については、「クエリの作成、読み込み、または編集 」を参照Excel (Power Query) を参照してください。

  2. Power Query エディターで 、[ホーム] >詳細エディター] を選択します。このエディターが開き、[フェーズ 2: データ ソースの定義] で作成したステートメントが表示されます

  3. let 式、次のように #"First Row as Header" 関数とTable.PromoteHeaders関数を

    追加let
        
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   

    #"First Row as Header" = Table.PromoteHeaders(Source)in    #"First Row as Header"

  4. クエリをワークシートに読み込むには、[完了] を選択し、[ホーム]>閉じる] &み込>を&します

結果:

ステップ 3 - 結果

フェーズ 4: 列の各値を適切な大文字と小文字に変更する

  1. クエリを開く場合は、ワークシートからデータ内のセルを選択し、[クエリ] を選択し、[編集] >選択します。 詳細については、「クエリの作成、読み込み、または編集」を参照Excel。

  2. Power Query エディターで、[ホーム]>詳細エディター] を選択します。このエディターが開き、[フェーズ 3: 最初の行をヘッダーに昇格する] で作成したステートメントが表示されます

  3. let式で、Table.TransformColumns関数を使用して各 ProductName 列の値を適切なテキストに変換します。前の "ヘッダーとしての最初の行" クエリ式の手順を参照し、#"大文字の各単語" をデータ ソースに追加し、結果に #"大文字の各単語" を割り当てします。

    let
        Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
        #"First Row as Header" = Table.PromoteHeaders(Source),
        #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
    in
        #"Capitalized Each Word"

  4. クエリをワークシートに読み込むには、[完了] を選択し、[ホーム]>閉じる] &み込>を&します

結果:

ステップ 4 - 結果

すべてのブックについて、Power Query エディターで数式バーの動作を制御できます。

数式バーを表示または非表示にする

  1. [ファイルオプション]>選択し、[クエリ オプション] 設定>を選択します

  2. 左側のウィンドウの [グローバル] で 、[PowerQuery エディター] を選択します

  3. 右側のウィンドウの [レイアウト] 、[数式バーを表示 する] をオンまたはオフにします

M Intellisense のオンとオフを切り替えます。

  1. [ファイルオプション]>選択し、[クエリ オプション] 設定>を選択します

  2. 左側のウィンドウの [グローバル] で 、[PowerQuery エディター] を選択します

  3. 右側のウィンドウの [ 数式]で、数式バー、高度なエディター、カスタム列ダイアログで [M Intellisenseを有効にする] をオンまたはオフにします。

注意   この設定を変更すると、次回 Power Query エディター ウィンドウを開いた時点で有効になります。

関連項目

Power Query for Excel のヘルプ

カスタム関数を作成して呼び出す

[適用されたステップ] リストの使用 (docs.com)

カスタム関数の使用 (docs.com)

Power Query M の数式 (docs.com)

エラーの処理 (docs.com)

ヘルプを表示

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

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

×