PowerQuery【02】PowerQuery（パワークエリー）の動かし方

前回の「PowerQueryで出来ること」に続いて、実際の作成方法をご案内します。

1．PowerQuery作成の全体の手順

手順は以下の通りです。（↓クリックすると拡大します）

 

※2021/3/22PowerPivot記事に合わせて追記・差し替えしました。

2．PowerQuery手順

なお、マニュアルは　Office365です(2020/9現在）。Excel2016とは若干異なります。

①「データ」タブ
②元データを指定（様々なパターンが選択可能）
○別のExcelファイルのシート
○同じExcelファイルの別シート（テーブルまたは範囲から）
※範囲を指定するときは、フィルタを解除してから実施。
でないと、「シート名!_FilterDatabase という名前のExcelテーブルが見つかりませんでした」というエラーが出る
○フォルダを指定して複数のファイルを一度に取り込む（Excel・CSVファイル）
（Excel2016では、データ／「新しいクエリ／ファイルから」または「テーブルから」）

③ファイルを選ぶ場合は、ファイル／シートを選択して、「読み込み」　または　「データの変換」
※慣れない内は「データの変換」を選択する。
(Excel2016では「読み込み／編集」）

④「データの変換」　で　PowerQueryエディタ画面へ

※「読み込み」をクリックすると、PowerQueryエディタ画面を開かずに、クエリとテーブルが出来る。
（クエリ編集は後からでも可能）

 

3．PowerQueryエディター画面と手順

①ヘッダーが1行目になっていないときは、1行目をヘッダにする（変換タブ／1行目をヘッダーとして使用）
②書式を修正（列毎に数値をテキストに変換する）
③クエリの名前を変更する
④閉じて読み込む
⑤読み込まれたExcelシート名の変更（「Sheet4」等のままではわかりにくいため）

（↓クリックすると拡大します）

4．PowerQueryエディターの仕組み

書式変更やフィルタなどの操作をすると「適用したステップ」に、1行ずつ登録されていく。
取り消す時は、ステップの横の「×」をクリックする。（ctrl＋ｚは効かないので注意）

（↓クリックすると拡大します）

こんなイメージで「適用したステップ」が積み上がっていく（↓クリックすると拡大します）

5．PowerQueryエディタを閉じた後

①緑地のシートが新しく出来て、右側にクエリ一覧が表示される。
分かりやすくするため、シート名を変える。（クエリ名とセットで分かりやすい名前にする）

元データが変更されれば「更新」で変更が反映

②更新するときは、右クリック/更新
また、「リンク解除」で独立したデータになる

6.接続のみにしてExcelファイルを軽くする

シートを消しても「クエリ」は残る。
重いデータ読み込みの場合は、シートを消した方が軽くなる。（データの実体が無くなるため）
最終クエリ分のみ残して他のシートを消すのも一策。

該当クエリを右クリックして／読み込み先　をクリック／「接続の作成のみ」にする
（該当のExcelシートを削除しても良い）

※PowerPivotを使うときは「このデータをデータモデルに追加する」に「ｖ」

7．データソースを確認できる(修正もできる）

 

8．元データのテーブル設定の解除方法

PowerQueryエディタをキャンセルしたり、テーブルを削除しても、元データは青のままでデータベース設定が残る（ヘッダーが削除できないなど）

データベース設定の解除方法　（②を実施すると①が実施できなくなるので、①→②の順に処理すること）

「テーブルデザイン」タブ（青地のテーブルのどこかをクリックすると表示される）
①テーブルスタイルをクリア
・「▼」をクリック
・クリア(網掛け解除）

②標準の範囲に変換
・「範囲に変換」
・確認メッセージ／はい　(テーブルを標準の範囲に変換しますか？）

次回以降は、個別のインポート方法（CSV・Excel・フォルダ等）について記載していきます。