Excel で Power Query 式を作成する

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

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

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

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

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

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

バックグラウンドでは、クエリの各ステップは、数式バーに表示される数式に基づいています。

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

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

手順

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

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

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

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

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

6. "Year" という単語が含まれる行を削除するには、Column1 のフィルター矢印を選択し、[年] の横にある [チェック] ボックスをオフにして、[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 エディターから以前に読み込まれたクエリを見つけ、データ内のセルを選択し、[クエリ ]> [編集] を選択します。 詳細については、「 Excel でクエリを作成、読み込み、または編集する」を参照してください。

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

3. 数式バーで、パラメーター値を見つけて変更し、[Enter] アイコンを選択するか、Enter キーを押します。 たとえば、この数式を変更して、Column2:Before:
   
   = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   After:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. [Enter ] アイコンを選択するか、Enter キーを押して、データ プレビューに新しい結果が表示されます。

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

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

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

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

2. 数式バーに「=Text.Proper("text value")」と入力し、Enter アイコンを選択するか、Enter キーを押します。
   
   結果はデータ プレビュー に表示されます。

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

結果:

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

重要     ソース、ナビゲーション 、および変更された型の各手順は、Power Queryによって作成され、データ ソースを定義および設定するために作成されるため、慎重に編集してください。

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

このメソッドは、手順に応じて異なるダイアログ ボックスを使用します。 数式の構文を知る必要はありません。

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

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

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

ステップを挿入する

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

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

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

3. 形式を使用して新しい数式を入力します:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   たとえば、列 Gender を持つテーブルがあり、値 "Ms" の列を追加するとします。 人の性別に応じて"Mr."。 数式は次のようになります:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

ステップを並べ替える

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

ステップの削除

• 手順の左側にある [削除 ] アイコンを選択するか、ステップを右クリックして、[ 削除 ] または [Delete Until End]\( 終了まで削除\) を選択します。 [ の削除 ] アイコンは、数式バーの左側にも表示されます。

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

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

前

後

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

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

各ステップは、名前でステップを参照することで、前のステップに基づいていることがわかります。 リマインダーとして、Power Query数式言語では大文字と小文字が区別されます。

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

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

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

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

1. Excel.CurrentWorkbook 関数を使用して let 式を作成
   
   します。let#x1
   in
   
       
   
   Source#x4

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

結果:

フェーズ 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)#x3
       #"First Row as Header"

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

結果:

フェーズ 4: 列の各値を適切なケースに変更する

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

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

3. let 式で、Table.TransformColumns 関数を使用して各 ProductName 列の値を適切なテキストに変換します。前の "First Row as Header" クエリ式ステップを参照し、#"大文字の各Word" をデータ ソースに追加し、結果に #"大文字の各Word" を割り当てます。
   
   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. クエリをワークシートに読み込むには、[完了] を選択し、[ホーム ]> [閉じる] & [読み込み] > [読み込みを閉じる] & [読み込み] を選択します。

結果:

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

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

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

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

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

M Intellisense のオンとオフを切り替える

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

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

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

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