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+zは効かないので注意)

(↓クリックすると拡大します)

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

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

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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