パワークエリの最初のステップとなるデータを収集する方法を解説します。ExcelファイルやCSV等からデータを収集する方法を具体的に解説していますので、サンプルデータを元に実際に操作してみましょう。
Excelブックからの読み込み方法
Excelのブックからのデータ取り込み方法を学習します。サンプルデータとして以下の3シートあるExcelファイルをパワークエリで取り込みます。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source1-1024x400.png)
単一シートの取り込み方法
一つのExcelのシートを取り込む方法です。複数のExcelシートの取り込みは次項で説明しています。
取り込みたいシートが含まれるExcelを指定します。
- [データ]タブを選択します。
- [データの取得]の上にカーソルを合わせます。
- [ファイルから]>[ブックから]を選択します。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source2-1024x504.png)
- 取り込むExcelファイルを選択
- [インポート]をクリック
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source11-1024x683.png)
上のSTEPで選択したExcel内の全てのシートが読み込まれます。取り込みたいシートを選択します。
- 取り込むシートを選択します。(下図の場合は「Sheet1」を選択)
- [データの変換]をクリックします。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source3-1024x805.png)
PowerQueryエディターが立ち上がるので、[閉じて読み込む]をクリック
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source7-1024x386.png)
コードを確認しておきましょう。構造を理解しておくと後々の学習が理解し易くなります。
最初のステップ「ソース」を選択すると以下のコード書かれています。これは、一旦は全てのExcelデータを取り込む記述です。Name列にSheet1~Sheet3が表示されていて、Data列にサブテーブルが格納されています。
= Excel.Workbook(File.Contents("C:\パワークエリ\データソース\bookの取り込み.xlsx"), null, true)
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source5-1024x386.jpg)
次のステップ「ナビゲーション」を選択すると以下のコードが書かれています。Item=”Sheet1″とい引数でsheet1に絞っています。
= ソース{[Item="Sheet1",Kind="Sheet"]}[Data]
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source6-1024x386.jpg)
この様にステップが積み重なってデータを整形していきます。
データが取り込まれました。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source8-1024x531.png)
複数シートの取り込み方法
複数シートの取り込み方法を解説します。普通に取り込むだけでは面白くないので、複数のシートを一つに統合してみたいと思います。
取り込みたいシートが含まれるExcelを指定します。
- [データ]タブを選択します。
- [データの取得]の上にカーソルを合わせます。
- [ファイルから]>[ブックから]を選択します。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source2-1024x504.png)
- 取り込むExcelファイルを選択
- [インポート]をクリック
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source11-1024x683.png)
上のSTEPで選択したExcel内の全てのシートが読み込まれます。
- [複数のアイテムの選択]にチェックを入れます。
- 取り込むシートを選択します。(下図の場合は「Sheet1、Sheet2、Sheet3」を選択)
- [データの変換]をクリック
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source21-1024x779.png)
取り込み後はSheet毎にクエリが作られています。適用したステップのソース以外を削除してください。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source22-1024x452.jpg)
取り込みを行うコードだけが残り、以下のようなテーブルに変わります。1行=1シートという構造になっています。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source23-1024x448.png)
Data列の1行目を選択すると、サブテーブルが表示されます。下図の場合だと、Sheet1のテーブルの情報が表示されています。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source24-1024x448.jpg)
3つのシートの統合を行います。
Data列の右にあるアイコンをクリックします。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source25-1024x448.jpg)
選択しはデフォルトのまま、OKをクリックします。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source26-1024x449.png)
3つのシートの情報が一つに統合されました。ただ、ヘッダーが混在したりとまだ使えない状態です。次のステップで整形していきます。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source27-1024x448.png)
不要な列を削除します。緑色になっている列は現在選択されている列です。選択されている列以外を削除する方法です。
- [ホーム]タブを選択します。
- [列の削除]を選択します。
- [他の列の削除]をクリックします。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source28-1024x385.png)
必要な列に絞ることができました。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source29-1024x365.png)
ヘッダーが、Data.Columnxといった形になっているので整形します。1行目がヘッダー情報となっているのでこれを活用します。[ホーム]タブの[1行目をヘッダーとして使用]をクリックします。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source30-1024x365.jpg)
1行目がヘッダーになりました。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source31.png)
続いて、不要な行の削除をします。他のSheet2,3のヘッダー情報が不要なので削除します。
「氏名」列の右側のアイコンをクリックします。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source32-1024x503.jpg)
- 不要な[氏名]のチェックを外します。
- OKをクリックします。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source33-1024x525.png)
不要が行が削除され、キレイにSheet1~Sheet3が統合されました。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source34.png)
不要なクエリを削除します。
- 不要なシートを右クリック(以下の例では、Sheet2を右クリックしている)
- [削除]を選択します。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source35-1024x569.png)
以下は削除された状態でクエリがSheet1だけになっています。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source36.png)
PowerQueryエディターが立ち上がるので、[閉じて読み込む]をクリックします。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source37-1024x591.png)
取り込みが完了しました。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/data-source38.png)
CSVファイルからのデータ取り込み
カンマ区切りCSVファイル(文字コード:シフトJIS)
文字コードがシフトJISのCSVファイルの取り込みを行います。
説明で利用するCSVファイルをダウンロードして実際に操作をしてみましょう。
メモ帳でCSVファイルを開くと右下に文字コードが表記されています。(シフトJISは、ANSIと表記されます。)
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce51.png)
- [データ]タブを押下します。
- [テキストまたはCSVファイルから]を押下します。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce52-1024x273.png)
取り込むCSVファイルを選択すると、以下の画面に遷移します。
- 元のファイルで[932日本語(シフトJIS)]を選択します。
- コンマ区切りのファイルであるため区切り記号で[コンマ]を選択します。
- [読み込み]を押下します。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce53-1024x746.png)
取り込まれました。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce54-1024x414.png)
コードを確認しておきましょう。構造を理解しておくと後々の学習が理解し易くなります。
最初のステップ「ソース」を選択すると以下のコード書かれています。
= Csv.Document(File.Contents("C:\パワークエリ\データソース\csv\csv-コンマ区切り-シフトJIS.csv"),[Delimiter=",", Columns=4, Encoding=932, QuoteStyle=QuoteStyle.None])
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce63-1024x340.jpg)
コードを分解して解説します。
<黄色の内側ブロック>
取り込みデータが置いてあるファイルのパスです。
File.Contents("C:\パワークエリ\データソース\csv\csv-コンマ区切り-シフトJIS.csv")
<青色の外側ブロック>
Csv.Document関数が使われています。引数として以下を指定しています。
- Delimiterでカンマ区切り”,”
- Columnsで4列を指定
- Encodingで文字コード932を指定
= Csv.Document({ファイルの場所},[Delimiter=",", Columns=4, Encoding=932, QuoteStyle=QuoteStyle.None])
カンマ区切りCSVファイル(文字コード:UTF8)
文字コードがUTF8のCSVファイルの取り込みを行います。
説明で利用するCSVファイルをダウンロードして実際に操作をしてみましょう。
メモ帳でCSVファイルを開くと右下に文字コードが表記されています。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce61.png)
- [データ]タブを押下します。
- [テキストまたはCSVファイルから]を押下します。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce52-1024x273.png)
取り込むCSVファイルを選択すると、以下の画面に遷移します。
- 元のファイルで[65001:Unicode(UTF-8)]を選択します。
- コンマ区切りのファイルであるため区切り記号で[コンマ]を選択します。
- [読み込み]を押下します。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce62-1024x746.png)
取り込まれました。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce54-1024x414.png)
コードを確認しておきましょう。構造を理解しておくと後々の学習が理解し易くなります。
最初のステップ「ソース」を選択すると以下のコード書かれています。
= Csv.Document(File.Contents("C:\パワークエリ\データソース\csv\csv-コンマ区切り-UTF8.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None])
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce64-1024x340.jpg)
コードを分解して解説します。
<黄色の内側ブロック>
取り込みデータが置いてあるファイルのパスです。
File.Contents("C:\パワークエリ\データソース\csv\csv-コンマ区切り-UTF8.csv")
<青色の外側ブロック>
Csv.Document関数が使われています。引数として以下を指定しています。
- Delimiterでカンマ区切りなので”,”を指定
- Columnsで4列を指定
- Encodingで文字コード65001を指定
= Csv.Document({ファイルの場所},[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None])
タブ区切りCSVファイル
タブで区切られたCSVファイルの取り込みを行います。
メモ帳でCSVファイルを開くとタブになっていることが分かります。今回は文字コードは意識していませんが、シフトJISです。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce71.png)
- [データ]タブを押下します。
- [テキストまたはCSVファイルから]を押下します。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce52-1024x273.png)
取り込むCSVファイルを選択すると、以下の画面に遷移します。
- 元のファイルで[932日本語(シフトJIS)]を選択します。
- タブ区切りのファイルであるため区切り記号で[タブ]を選択します。
- [読み込み]を押下します。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce72-1024x746.png)
取り込まれました。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce54-1024x414.png)
コードを確認しておきましょう。構造を理解しておくと後々の学習が理解し易くなります。
最初のステップ「ソース」を選択すると以下のコード書かれています。
= Csv.Document(File.Contents("C:\パワークエリ\データソース\csv\csv-タブ区切り.csv"),[Delimiter=" ", Columns=4, Encoding=932, QuoteStyle=QuoteStyle.None])
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce73-1024x340.jpg)
コードを分解して解説します。
<黄色の内側ブロック>
取り込みデータが置いてあるファイルのパスです。
File.Contents("C:\パワークエリ\データソース\csv\csv-タブ区切り.csv")
<青色の外側ブロック>
Csv.Document関数が使われています。引数として以下を指定しています。
- Delimiterでタブ区切りなので=” “が指定
- Columnsで4列を指定
- Encodingで文字コード932を指定
= Csv.Document({ファイルの場所},[Delimiter=" ", Columns=4, Encoding=932, QuoteStyle=QuoteStyle.None])
列が変動するCSVファイル
列が変動するCSVファイルの取り込みを行います。
シナリオとしては、列数6のデータ取り込みのクエリを作成し、取り込みデータの列が12列に増加した場合どうなるのか、そしてどのように対処していけば良いのか解説します。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce81-1024x398.png)
列数6のCSVファイルを取り込みます。以下のコードにて取り込みがされます。
Csv.Document関数が使われいて、引数として以下を指定しています。
- ファイル「列数6_列が可変.csv」を取り込み
- Delimiterでカンマ区切り”,”
- Columnsで6列を指定
- Encodingで文字コード932を指定
= Csv.Document(File.Contents("C:\パワークエリ\データソース\列数6_列が可変.csv"),[Delimiter=",", Columns=6, Encoding=932, QuoteStyle=QuoteStyle.None])
列数が6のファイル取り込みなので、列数を指定するColumns=6となっている点がポイントとなります。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce91-1024x409.jpg)
続いて、列数を12のファイルに変更してクエリの更新を行ったらどうなるのか確認してみます。
取り込むソースファイルを以下のように変更します。
変更前 | 変更後 |
File.Contents(“C:\パワークエリ\データソース\列数6_列が可変.csv“) | File.Contents(“C:\パワークエリ\データソース\列数12_列が可変.csv“) |
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce92-1024x409.jpg)
列数12のファイルに差し替えてクエリの更新をします。「プレビューの更新ボタン」を押下します。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce94-1024x409.jpg)
列数が6までしか表示されません。
引数がColumns=6となっているので6列までの取り込みになるわけです。
列数が増えても取り込めるようにしたいので、以下のように引数をColums=nullに変更します。
変更前 | 変更後 |
[Delimiter=”,”, Columns=6, Encoding=932, QuoteStyle=QuoteStyle.None] | [Delimiter=”,”, Columns=null, Encoding=932, QuoteStyle=QuoteStyle.None] |
変更後に「プレビューの更新ボタン」を押下してクエリを更新すると以下のように12列まで取り込まれました。もちろん13列以上になったとしても取り込まれます。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce95-1024x420.jpg)
このように、コードで理解していると応用的な使い方も簡単にできてしまいます。
複数CSVファイルの取り込み
取り込むファイルが複数ある場合でもパワークエリでは簡単に取り込むことができます。
- [データ]タブを選択
- データの取得の[ファイルから]を選択
- フォルダーからを選択
![](https://pq.ittrip.xyz/wp-content/uploads/2022/08/datasorce301-1024x473.png)
複数ファイルが保存されているフォルダのパスを指定します。
3つのフィアルが取り込まれました。この3つのファイルを結合するには、右下の結合から[データの結合と変換]をクリックします。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce302-1024x463.png)
3つのファイルのデータが結合されました。また、各ファイルに存在しているヘッダーの削除等が自動で行われます。理想的なデータでない場合は、編集をしてください。
[保存して読み込む]ボタンを押下します。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce304-1024x390.jpg)
複数フォルダの取り込みはFolder.Files関数が使われています。
= Folder.Files(“{複数ファイルが保存されているフォルダのパス}”)
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce401-1024x410.jpg)
エクセルにデータが読み込まれました。
3つのファイルが同じデータ構造であることをパワークエリが自動的に判断してくれているのでワンタッチで複数のファイルの結合が可能になっています。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce305.png)
PDFファイルの取り込み
パワークエリではCSVやExcelデータの他にもPDFファイルを取り込むことが可能です。PDFファイルの取り込み方法を解説していきます。
取り込むPDFファイルの絶対パスをSTEP2以降で利用しますので取得してください。
Windows11の場合は、取り込み対象のPDFファイルを右クリックして「パスのコピー」を押下してください。Windows10以前のOSの場合はShiftキーを押下しながら右クリックをすると「パスのコピー」メニューが表示されます。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce102-1.png)
続いて空のクエリを開きます。
- [データ]タブを押下します。
- [その他のデータソースから]を選択します。
- [空のクエリ]を押下します。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce101-1-1024x448.png)
File.Contents関数でPDFファイルを指定します。
STEP1で取得したPDFファイルの絶対パスを指定してください。
=File.Contents({PDFファイルの絶対パス})
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce103-1024x354.png)
[fx]を押下して新しいステップを追加してください。適用したステップに「カスタム1」が追加されます。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce104-1.png)
カスタム1の中が以下のようになっています。上位ステップのソースをただ読み込んでいるだけです。ソースの中身を変換します。
=ソース
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce105-1-1024x292.png)
Pdf.Tables関数の中身をソースにします。以下の記述にすれば問題ありません。
= Pdf.Tables(ソース)
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce106-1-1024x292.jpg)
PDFの構成要素すなわち中身が表示されます。ためしに2行目のTableを選択してみます。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce107-1-1024x292.jpg)
PDFの中身が表示されます。複雑なPDFでも構成要素毎にデータベース化してくれるので整理がしやすいです。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce108-1-1024x292.jpg)
Web上からの取り込み
パワークエリは、Web上に散らばった様々な形式のデータを取り込むことが可能です。取り込み後に他のデータと結合したりなどができるため使い方次第では大幅な時短が期待できます。
Web上のHTMLテーブルの取り込み
HTML形式で表示されたWeb上のテーブルをデータベースとして取り込みます。
以下のWikipediaに掲載されている各国の実質GDPリストをExcelに取り込みます。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce111-1024x443.jpg)
サイト上のテーブルがTabletという表記で表示されます。取り込みたいTableを選択して[読み込み]を押下します。もちろん、データの変換でデータの整形をクエリ上で行うこともできます。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce114-1024x573.png)
読み込むとExcel上にテーブルとして表示されます。Web上からコピペで転記するより正確で簡単です。
Web上のデータが変更れた場合は、更新するだけで最新データが取り込まれます。これがパワークエリの利点です!
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce115.png)
Web上のCSVデータの取り込み
CSVファイルとして公開されているWeb上のデータをデータベースとして取り込みます。
TOKYO OPEN DATAというサイトに統計データだCSVファイル形式でアップされています。試しに品川区の避難所の位置情報のデータをパワークエリで取り込んでみます。以下のサイトへアクセスして、CSVファイルのリンクをコピーしてください。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce201-1024x593.jpg)
データを確認して[読み込み]を押下します。もちろん、データの変換でデータの整形をクエリ上で行うこともできます。
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce203-1-1024x768.jpg)
読み込むとExcel上にテーブルとして表示されます。
Web上のデータが変更れた場合は、更新するだけで最新データが取り込まれます。これがパワークエリの利点です!
![](https://pq.ittrip.xyz/wp-content/uploads/2022/09/datasorce204-1024x478.jpg)
パワークエリ学習コンテンツ
-
【初心者向け】パワークエリ入門:ETLツールを使ってエクセルデータを簡単に整形・統合しよう!(1/11)
-
【実践ガイド】パワークエリでデータ収集:Excel、CSV、PDF、Webデータを簡単に取り込む方法をマスターしよう!(2/11)
-
【総力特集】パワークエリで列操作をマスター:選択、変更、移動、削除、結合、分割の詳細解説&実践テクニック!(3/11)
-
【徹底解説】パワークエリで行操作をマスター!フィルター・保持・削除テクニックと練習用エクセルで実践学習(4/11)
-
パワークエリでデータクレンジング: 文字列結合、0埋め、テキスト関数をマスター(5/11)
-
パワークエリで四捨五入、切り捨て、切り上げをマスターする方法(6/11)
-
パワークエリで効率的なデータグループ化を実現する方法(7/11)
-
パワークエリで時間と日付の計算をマスター!便利な関数を使って効率アップ(8/11)
-
パワークエリで条件別集計をマスターする方法(9/11)
-
Excelパワークエリでクロス集計表とデータベース形式を瞬時に変換する方法(10/11)
-
Excelパワークエリ入門: 効率的なデータ整理をマスターしよう!(11/11)
コメント