Excelの機能である「PowerQuery」を使ってデータ加工を行うためには、まずは加工したいデータを取り込む必要があります。
ですが、一言にデータといっても同じようにExcelやCSVファイルでのデータもあれば、
・PDFファイル
・webサイト上のデータ
・各システムに登録されているデータ
・画像データ
など、挙げれば切りがありません。
今回はPowerQueryでデータ加工や整形を行うための最初のステップである、
「データの取得」
の機能や使い方について解説していきたいと思います。
Power Queryとはどのような機能なのか？、どんなことができるかについては、
下記の記事をご覧ください。
【今回の記事はこのような方にオススメ】
・Power Queryを初めて使う方
・データの取得方法について、大枠を知りたい方
・PDFやWebからデータを取得する方法を知りたい方
PowerQueryでのデータ取得方法
PowerQueryでデータ加工を行い、その後集計や分析を行うにも元となるデータがなければいけません。
そのために、必要とするデータの取得を行う必要があります。
データの取得方法は下図の①～の流れで行います。
（ここでは、例としてExcelブックでのデータを取得する流れで説明します。）
①：Excelファイルを開き、「データ」タブを選択する。
②：データの取得と変換にて、「データの取得」を選択する。
③：データの取得を選択後、「ファイルから」を選択する。
④：「Excelブックから」を選択する。
⑤：取得したいExcelファイルを選択する。
⑥：インポートをクリックする。
①～⑥までの流れを行うことで、PowerQueryでのデータ加工の第一歩となる、データの取得を行うことができます。
③の工程では「ファイルから」、④の工程では「Excelブックから」を選択する流れでしたが、実際には様々なファイルやデータ形式からデータを取得することができます。
ここで、データの取得で選択する代表的な選択肢を下図で紹介します。
「ファイルから」
ファイルから（赤枠）では、Excel、CSVファイルの他、PDFやフォルダ内のファイルを一括で取り込む方法を選択することができます。
「データベースから」
データベースから（青枠）では、SQLserverといったDBやAccessのデータテーブル、クエリからデータを取り込むことができます。
「その他のデータソースから」
その他のデータソースから（緑色）からは同じExcelファイル内のテーブルからデータを取得する「テーブルまたは範囲から」やWeb、画像からデータを取り込む方法を選択することができます。
それ以外にも「Azureから」や「Power Platformから」といった選択肢もありますが、経験上はExcelブックやCSV、Accessのテーブルといったデータ形式を選択して、データを取得することが多いように感じます。
また、上図での選択項目に関しては、Office365でのExcelブックで選択できるものであり、Excelのバージョンが古い場合は上図とは異なる選択肢となっています。
（少なくとも、「画像から」はExcel2019のバージョンでは存在しないため、2020年以降に搭載された機能のようです。）
ですが、今回の記事で紹介する
「Excelブック」
「CSV」
「PDF」
「Web」
に関しては古いバージョンでもデータの取得は可能です。
これ以降にて、それぞれのデータ取得について解説していきます。
取得後はどうする？ PowerQueryエディターでの加工と保存
データを取得することができても、そのあとのデータの扱いや活用はどうするのでしょうか？
データの形式によって取得の方法は異なるものの、大きくは下図の①～④の流れでデータを反映、活用することができます。
取得
上記「データの取得」にて取り込みたいデータの形式に合わせてデータの取り込みを行います。
加工
取り込んだデータをPowerQueryエディターにて加工処理をおこないます。
取り込んだデータのデータ形式を整えたり、無駄なデータ削除や特定データの抽出、データ結合（マージや追加）を行い、目的のデータに加工/整形を行っていきます。
読み込み
取得・加工したデータを反映させるために「データの読み込み」を行う必要があります。
単純にPowerQueryエディター上で「閉じて読み込む」を行った場合、ワークシートへ取込・加工したデータをテーブルとして反映します。
特段必要がない限りは「閉じて次に読み込む」を選択し、データの反映方法を選択できる状態で読み込みを行うのがベターです。
反映
取込⇒加工したデータをワークシート上で反映させます。
読み込んだデータをワークシート上で「テーブル」形式で一覧表として反映させることもできるが、取得したデータ量が大きいこともあるため、「データのみの接続」と「データモデルに追加する」を選択して読み込み、PowerPivotでピボットテーブルでの集計やピボットグラフでの可視化を行うことが多いかと思います。
データの取得方法
PowerQueryでのデータの取得といっても、様々なデータ形式から取得することが可能ですが、今回は、
Excel
CSV
Web
の4つのデータに関する取得方法についてそれぞれ解説していきます。
Excelブックのデータ取得方法
Excelブックでのデータ取得方法の一部については、１．PowerQueryでのデータ取得方法にて解説していますが、ここでは再掲として、下記に「データの取得」から取り込むExcelブックをインポートするまでの流れを記載します。
①：Excelファイルを開き、「データ」タブを選択する。
②：データの取得と変換にて、「データの取得」を選択する。
③：データの取得を選択後、「ファイルから」を選択する。
④：「Excelブックから」を選択する。
⑤：取得したいExcelファイルを選択する。
⑥：インポートをクリックする。
⑥のインポートのクリックまでを実施すると、下図のようなインポート時に選択したExcelブック内のシートから実際に取り込むデータを選択する画面（ナビゲーター）が表示されます。
ナビゲーター画面が表示された後は①、②の2点を行います。
① 前ページでインポートしたファイル内でデータを取り込むシートを選択する。
（複数シートを選択する場合は「複数のアイテムの選択)にチェックをする。）
② 取り込むシート（データ）を選択し、「データの変換」をクリックして、PowerQueryエディターを表示させる。
「読み込む」をクリックした場合でも、データは取得され、ワークシート上にテーブルとして表示されますが、先にデータの変換にてPowerQueryエディターを開き、データ加工しておくことがベターです。
取り込むデータを選択し、「データの変換」をクリックすることでPowerQueryエディターが表示されます。
PowerQueryエディターが表示された際は、下図の①、②の確認・変更を行っておくようにしましょう。
① 取り込んだデータのクエリ名
最初は取得するExcelファイルのシート名ですが、複数のExcelファイルから取得することも考慮して、データの内容がわかりやすいクエリ名に変更しておくことがベストです。
② 各列のデータ形式
データを取り込んだ際に自動的に判定してくれますが、想定外のデータ形式に変換されている場合もあるため、使用しやすいデータ形式に変換しておきましょう。
特に、クエリのマージでキーとなる列に関しては「テキスト」の形式にしておくと、間違ったデータのマージや正しくデータがマージされないといった間違いを防ぐこともできます。
クエリ名とデータ形式の確認・変更やデータ加工が済んだら、下図赤枠の「閉じて読み込む」をクリックします。
この際に、「閉じて読み込む」と「閉じて次に読み込む」の2つがありますが、「閉じて次に読み込む」を選択するようにしましょう。
「閉じて読み込む」の場合、PowerQueryエディター上で取込・加工したデータがワークシート上にテーブルとして反映されますが、データ量が多い場合は反映するのに時間がかかってしまいます。
そのため、「閉じて次に読み込む」を選択し、ワークシート上でのデータの表示方法を選択できるようにしておくのがベストです。
ここで、PowerQueryエディター上で「閉じて次に読み込む」を選択すると、下図のような「データのインポート」が表示されます。
ワークシート上に取込と加工を行ったデータを表示させる場合、下図左側の①～④までの選択を行います。
【ワークシート上にデータを表示させる場合】
① テーブルを選択する。
② 新規ワークシートを選択する。
（既存のワークシートを選択することも可能であるが、誤った箇所にデータを反映させてしまうトラブルも起きやすいため、特段指定が無い場合は新規ワークシートで表示させるのがベター）
③ このデータをデータモデルに追加するにチェックする。
④ OKをクリック。
また、取り込んだデータ量が多い場合やデータを取り込んだExcelファイル自身の容量を軽くしたい場合、ワークシート上にデータを反映させずにデータを保持することも可能です。
その場合、上図右側の「ワークシート上にデータを表示させない場合」の①~③までを行います。
【ワークシート上にデータを表示させない場合】
① 接続の作成のみを選択する。
② このデータをデータモデルに追加するにチェックする。
③ OKをクリックする。
この「接続の作成のみ」を選択した場合、一見するとExcelファイル上には目に見えるデータが何もないため、不安になることがありますが、「クエリと接続」をクリックすることでデータの取得やPowerQueryエディターで加工したクエリ（データ）は存在していることが確認できます。
また、PowerQueryで加工したデータをテーブルで一覧としてみることもありますが、多くがPowerPivotでのピボットテーブルでの集計やピボットグラフでの可視化を行うことが多いため、こちらも特に指定や制約がない場合は「接続の作成のみ」を選択しておくのがベターです。
CSVファイルのデータ取得方法
CSVファイルのデータを取り込む場合、Excelと同様下図のように①～③までは同じように選択していきますが、CSVファイルの取り込みの場合は④の「テキストまたはCSVから」を選択をします。
CSVファイルの選択はExcelの場合と同様ですが、ファイルを選択肢、ナビゲーター画面がExcelファイルからの取り込みの場合と異なります。
元のファイル、区切り記号、データ型検出の3つの項目（青枠）がありますが、区切り記号がカンマでないなど、形式が特殊でない場合は特段変更することなく、⑤の「データの変換」をクリックしてPowerQueryエディターを起動して問題ないと思います。
エディター起動後はExcelファイルでの取込と同様、クエリ名や列名、データ形式の確認・変更やデータ加工を行い、データ読み込みを行います。
（PowerQueryエディター起動後以降はExcelブックの取り込みと同じ流れとなります。）
PDFファイルのデータ取得方法
PDFファイルのデータを取り込む場合、Excelと同様下図のように①～③までは同じように選択していきますが、PDFファイルの取り込みの場合は④の「PDFから」を選択します。
今回取り込むデータは参考として、日本取引所グループの統計月報の株式総括表を使用してみます。
https://www.jpx.co.jp/markets/statistics-equities/monthly/index.html
ナビゲーター画面は下図のようにExcelファイルの場合と同じですが、表示オプションに青枠の形式と緑枠の形式2つが表示されています。
青枠のデータは「テーブル」として認識された表です。一方の緑色のデータはPDFファイルでの一覧表をそのまま取り込んだ場合の表となります。
データの内容自体は列名やデータ形式がPowerQueryエディターを起動した際に異なりますが、データ自体に違いはない、また列名やデータ形式はPowerQueryエディターで変更可能なため、どちらを選んでも大きな違いはありません。
今回の場合は、PDFファイルでの一覧表のまま取込を行うために、緑枠でのデータを選択して「データの変換」をクリックします。
PowerQueryエディター起動後にクエリ名と列名、データ形式の変更を行います。
下図赤枠①でのクエリ名は取り込んだ際に、ナビゲーターで表示されていた「Page2」となってしまうため、データの内容に合わせてクエリ名を変更しておきましょう。
赤枠②の列名やデータ形式も取り込んだ当初は「Column●●」、「テキスト型」となっていますが、取り込んだデータを扱いやすい形に直していく過程で列名やデータ形式を整えていきます。
青枠のように、PDFファイルでのデータ取込の場合、取り込んですぐに活用できないことが多いため、扱いやすい形にデータを整えていく必要があります。
扱いやすい形に整えた後はExcelファイルの取り込みと同様の流れで読み込みを行います。
Webからのデータ取得方法
Webからデータを取り込みする場合はこれまでのように「ファイルから」ではなく、下図のように、①「その他のデータソースから」を選択し、②の「Webから」をクリックします。
そうするとこれまではナビゲーションがすぐに開きましたが、③のようにWebからの場合は取り込むデータがあるWebサイトのURLを張り付けるダイアログボックスが表示されます。
今回は例として、世界の株価と日経平均先物の日経２２５先物 リアルタイム チャート CME SGX（ https://nikkei225jp.com/cme/）より、リアルタイム日経平均 日経先物の範囲にあるデータをPowerQueryで読み込んでいきます。
日経２２５先物 リアルタイム チャート CME SGX
https://nikkei225jp.com/cme/
URL入力後、ナビゲーター画面が表示されますが、これまでのExcelやCSVとは異なり、下図のように青枠箇所に「テーブルビュー」と「Webビュー」の選択タブが表示されます。
この2つのタブですが、違いはWebサイト上でどの範囲のデータを取り込むかを表示してくれるのが「Webビュー」であり、「テーブルビュー」はこれまでのExcelやCSV、PDFの取り込みと同様、表形式でデータを取り込んだ場合のプレビューを表示してくれます。
個人的には、Webからデータを取り込む場合は、取り込みたい範囲のデータを間違いなく取り込んでいるかを「Webビュー」で確認してから、「テーブルビュー」で取込直後のデータテーブルの状態をプレビューで確認するのがベターだと思います。
この時、Webビュー上でどの範囲のデータを取り込むかについては、スライド赤枠部分にもあるように、取り込む範囲のデータを緑色で表示してくれるため、自身が取り込みたいデータを取り込めるかどうかが一目で判断しやすいです。
取り込んだデータの更新方法
これまで、ExcelファイルやCSV、PDF、またWebと主要なデータの取得～反映までの流れを行ってきましたが、実際のデータ（クエリ）の更新はどうするのでしょうか？
実際、更新する作業はかなり簡単です。
下図のように、データタブ赤枠①の「すべて更新」をクリックし、赤枠②の「すべて更新」をクリックします。そうすると赤枠③にて、表示されているクエリやワークシート上に反映したテーブルやピボットテーブル・グラフが一括で更新されます。
ただし、ピボットテーブルやピボットグラフをこの「すべて更新」で一度に更新させようと思った場合、データのインポート画面でチェックを入れていた、「このデータをデータモデルに追加する」にチェックを入れた状態で更新作業を行わないとピボットテーブルやピボットグラフまでは更新できないです。
もしデータモデルに追加せず、更新させる場合は2回、すべて更新をクリックしてクエリの更新を行う必要があります。
上記のように「すべて更新」の場合は、ファイルにて作成されたクエリ全数を基本的には更新します。
では、特定のクエリ単独を更新する場合はどうすればよいでしょうか？
上図のように、クエリと接続を開いた状態で、クエリにマウスポインタをあわせると、赤枠にあるマークが表示されます。このマークをクリックすることで、クエリ単独の更新を行うことができます。
ですが、クエリを1つ1つ更新しようとすると、数十個もクエリが1ファイルにある場合は、更新作業に時間がかかるほか、更新漏れが発生する可能性もあります。
そうした予期せぬトラブルを未然に防ぐためにも、「すべて更新」にてクエリ全体を更新しておくと良いです。
PowerQueryを効率的に学習するためには？
上記のように、PowerQueryでのデータ取込から加工、集計やデータの更新を行えることで仕事上での業務改善や生産性をあげていくことにも役立つこと間違いなしです。
ですが、
PowerQueryについて学ぶにはどうしたらいいのか？
時間がない中で効率よく学習するためにはどうすればいいか？
といった悩みを持つ人もいるかと思います。
私自身も時間がない中でどうやって学習すればよいか悩みました。
本で学習しようにも、読む時間や学んだことを定着化させることに難しさを感じていました。
そんな中、Udemyでの動画を活用することで、
効率的にPowerQueryやその先の集計（Power PivotやDAX）を学習できた。
学んだスキルを定着させ、日々の仕事に活かすことができている。
大きく2つのメリットが自身にとってありました。
Udemyについては、過去にまとめた記事がありますのでそちらもご参照ください。
特にUdemyを利用して、
・ハンズオン形式で手を動かしながら学ぶことができる。
・動画のため、再生速度を調節しながら時短できる（1.5倍速～2倍速で視聴していました。）
・ポイントを絞って、繰り返し視聴で学習できる。
といった良かった点がありました。
Udemyではよくセールをしているため、1動画1,200円～1,800円程度で購入できます。
（本1冊分で効率よく学習することができます。）
特に、私がUdemyでPowerQueryについて学習していてオススメの動画が下記2つでした
①Microsoft Excel – Power Pivot, Power Query, DAX 入門講座
≪こんな方にオススメ！≫
Power Queryでの加工からPower PivotやDAXを使った集計・分析までをざっくりと学びたい方向け。
②【続】Microsoft – Excel Power Pivot 入門講座 ～Power Query Editor編～
≪こんな方にオススメ！≫
Power Queryエディターでのデータ加工/整形方法を集中的に学びたい方向け。
PowerQueryを学習してみたい、仕事に活かしてみたいと思った方は是非、利用・活用してみてください！
まとめ
PowerQueryでの「データの取得」では、多岐に渡るデータ形式からデータを取得することができますが、今回の記事では、
・Excelブック
・CSVファイル
・Web
といった使用頻度の高いデータ形式での取込方法と取込後のデータ更新方法について解説しました。
「データの取得」と「データの更新」を使いこなせるようにあるだけでも、マクロの作成のような難易度の高いスキルを使わずともExcelの自動化への一歩を踏み出すことができます。
また、PowerQueryやその先のPowerPivot、DAXをつかった集計や分析を行うことへの第一歩にもつながります。
今回の記事から、
・PowerQueryを学んでみよう！
・日々の仕事に活用してみよう！
と思えてもらえますと幸いです。
コメント