Power Queryを使ったWeb上Excelデータの取得方法
この動画では、Power Queryを用いてWeb上のExcelファイルからデータを取得する方法を、初心者向けに解説することを目指しました。特に、厚生労働省の食中毒統計データの取得を題材に、実践的な手順とエラー対処法を説明しました。
https://www.youtube.com/watch?v=sU2hZA6KmaU
www.youtube.com
目次
00:00 Web上のExcelファイル取得 Power QueryでWeb上のExcelファイルを取得する方法を解説
00:18 視聴者からの質問 視聴者から寄せられた、厚生労働省のWebサイトからExcelファイルを取得する際のエラーに関する質問を紹介
01:33 現物確認 厚生労働省のWebサイトからExcelファイルのリンクを確認
02:41 ファイル番号とURL 厚生労働省のExcelファイルのURLの構造を解説
03:34 頭0埋めテスト エクセルでURLに使う番号を0埋めする方法と、Power Queryでの動作確認テスト
04:37 新規ブック作成と0埋め設定 新規ブックを作成し、セルの書式設定で数値を0埋めする方法を解説
07:00 名前付き範囲の設定 セルに「URL」という名前付き範囲を設定する方法を解説
08:18 URL組み立てとテスト Power QueryでURLを組み立てるM言語コードを作成し、テストを実施
10:47 Text.PadStart関数解説 M言語のText.PadStart関数を使って、数値を0埋めした文字列を作成する方法を解説
14:52 コード修正とプライバシー設定 Web.Page関数を使った際のプライバシーレベルエラーへの対処法を解説
18:05 ダウンロードエラーとExcel.Workbook ダウンロードエラーの原因と、Excel.Workbook関数を使った解決策を解説
21:30 Excel.Workbook単体テスト Excel.Workbook関数でExcelファイルの先頭シートを取得する単体テスト
23:38 ヘッダー昇格 Table.PromoteHeaders関数でヘッダーを昇格させる方法を解説
26:31 URLとシート番号の組み合わせ URLの番号指定とExcelシートの組み合わせによるデータ取得方法を解説
28:58 文書番号変更による動作確認 取得したいExcelファイルの文書番号を変更して、データが正しく取得できるかを確認
33:05 2番目のシート取得方法 Excelファイルの2番目のシートを取得する方法を解説。クエリと接続からの複製方法と注意点を紹介
37:58 5年データ取得 同じ方法で令和5年のデータを取得する例を紹介
40:46 3番目シートがない場合のテスト 3番目のシートがないブックを読み込んだ場合の動作を確認
46:24 シートとデータの順番 シートではなくデータの順番で取得していることを確認。Excel.Workbook関数の動作を解説
51:01 シート取得時のエラー処理 シートを取得する場合のエラー処理方法を解説
52:36 まとめと感想 デバッグのポイントと感想を共有
キッカケ:
下記、動画解説を見た視聴者様から質問をいただきました
Power Query B2の銘柄コードで株式信用残高の表をWebから取得する Excel WebクエリでURLの一部をパラメータで変更する方法 パワークエリでセルの値を参照利用
https://www.youtube.com/watch?v=hW3MJD2Srj4
に対して、
厚生労働省公式HPから
https://www.mhlw.go.jp/stf/seisakunitsuite/bunya/kenkou_iryou/shokuhin/syokuchu/04.html
xlsxファイルを取得する質問をもらいました。
質問内容:
動画を参考に下記のコードを作成しましたがエラーとなりうまく作動しません。
let
//Excelで付けた名前範囲"URL"から値を取得
strNO = Excel.CurrentWorkbook(){[Name="URL"]}[Content]{0}[Column1],
//URLを組み立てる
strURL = "https://www.mhlw.go.jp/content/" & Number.ToText(strNO) & ".xlsx",
ソース = Web.Page(Web.Contents(strURL)),
令和6年食中毒発生事例_Sheet = ソース{[Item="令和6年食中毒発生事例",Kind="Sheet"]}[Data],
昇格されたヘッダー数 = Table.PromoteHeaders(令和6年食中毒発生事例_Sheet, [PromoteAllScalars=true]),
変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"令和6年食中毒発生事例（速報：令和6年11月1日までに厚生労働省に報告のあった事例）", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}})
in
変更された型おっさんが、デバッグしてみた:
1.まず、現物を確認する
まず、
厚生労働省公式HPから
https://www.mhlw.go.jp/stf/seisakunitsuite/bunya/kenkou_iryou/shokuhin/syokuchu/04.html
xlsxファイルのリンクを調べる
ブラウザからリンクをコピーする
令和6年（2024年）食中毒発生事例（速報）［76KB］別ウィンドウで開く
https://www.mhlw.go.jp/content/001324643.xlsx
や
(2)過去の食中毒発生状況
令和5年（2023年）食中毒発生状況［56KB］別ウィンドウで開く
https://www.mhlw.go.jp/content/001215431.xlsx
https://www.mhlw.go.jp/content/001324643.xlsx
https://www.mhlw.go.jp/content/001215431.xlsx
2.頭0が怪しいかなぁ・・・
動画の説明位置→ https://www.youtube.com/live/sU2hZA6KmaU?t=214s
あっ、
もしかして、
//Excelで付けた名前範囲"URL"から値を取得
strNO = Excel.CurrentWorkbook(){[Name="URL"]}[Content]{0}[Column1],
//URLを組み立てる
strURL = "https://www.mhlw.go.jp/content/" & Number.ToText(strNO) & ".xlsx",
で、
URLを組み立てているので、
の頭0が怪しいかなぁ・・・
2.1 復習を兼ねて、名前付き範囲を作りテストしてみる
・新規のブックを作成します
・B1,B2にデータを入力します
番号 1324643
・B2の書式を000000000 (0を9桁にします※頭0埋めを表示形式で行います)
・B2を選択して、名前をURLと付けます
//Excelで付けた名前範囲"URL"から値を取得
strNO = Excel.CurrentWorkbook(){[Name="URL"]}[Content]{0}[Column1],
//URLを組み立てる
strURL = "https://www.mhlw.go.jp/content/" & Number.ToText(strNO) & ".xlsx",なので、
Excelシートに"URL"と名前を付けて、テストしてみる
Number.ToText(strNO) 数値変換なので、
あっ、もしかして、表示形式で0埋めの数値で管理しているのかなぁ？
001324643.xlsx
なので、表示形式
000000000を指定して、中身は1324643なのかな？
パワークエリのM言語で、頭9桁の頭0数値を作るには、
Text.PadStart
を使ってみるとか、(Excelの=TEXT関数使いたいけど、無いので)
let
//Excelで付けた名前範囲"URL"から値を取得
文書NO = Excel.CurrentWorkbook(){[Name="URL"]}[Content]{0}[Column1],
// 9桁固定、頭0埋めの数値に変換
strNO = Text.PadStart(Number.ToText(文書NO), 9, "0"),
// URLを組み立てる
strURL = "https://www.mhlw.go.jp/content/" & strNO & ".xlsx"
in
strURL2.2 コードを修正して、テストしてみる
上記、確認コードをプラスして、テストしてみる
let
//Excelで付けた名前範囲"URL"から値を取得
文書NO = Excel.CurrentWorkbook(){[Name="URL"]}[Content]{0}[Column1],
// 9桁固定、頭0埋めの数値に変換
strNO = Text.PadStart(Number.ToText(文書NO), 9, "0"),
// URLを組み立てる
strURL = "https://www.mhlw.go.jp/content/" & strNO & ".xlsx",
//
ソース = Web.Page(Web.Contents(strURL)),
令和6年食中毒発生事例_Sheet = ソース{[Item="令和6年食中毒発生事例",Kind="Sheet"]}[Data],
昇格されたヘッダー数 = Table.PromoteHeaders(令和6年食中毒発生事例_Sheet, [PromoteAllScalars=true]),
変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"令和6年食中毒発生事例（速報：令和6年11月1日までに厚生労働省に報告のあった事例）", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}})
in
変更された型2.2.1 プライバシーレベルのエラーが発生する
初めて、アクセスしたからか？
エラーが発生した。
匿名でアクセスする
プライバシー情報が必要ですと聞かれるので、
今回は、自分がファイルの持ち主ではないので
(※このデータは一般公開されているので、踏み台になってアクセスされることは考えない？おいおい？)
□このファイルのプライバシーレベルチェックを無視
に☑を入れて、進めます。
※※この接続ファイルが踏み台にされる、危険な例もやらないとなぁ・・・
2.3 ダウンロードでエラーが発生する
あれ、エラーが発生した・・・
エラー発生で止まると、裏で処理されるファイルが見えるのか？
う～ん、、
あっ、Webページじゃなくって、Web上のExcelファイルか？
ソース = Web.Page(Web.Contents(strURL)),
だと、取得できないかな。
2.4 Excel.Workbookでブック指定後、先頭シートを固定で落とす例
Web.Page(Web.Contents(strURL)),でエラーが発生しているみたいなので、
シートを固定で取得してみる
※そもそも、ファイルによって、シート名が違うので、固定で0番目を取得してみる
気を取り直して、Excel.Workbookの単体テストを行う
let
WB = Excel.Workbook(Web.Contents("https://www.mhlw.go.jp/content/001324643.xlsx"), null, true),
先頭のシート = WB{0}[Data]
in
先頭のシート2.5 ヘッダーを昇格させる？
00:23:38 ヘッダー昇格 Table.PromoteHeaders関数でヘッダー昇格
動画解説位置→ https://www.youtube.com/live/sU2hZA6KmaU?t=1418s
昇格されたヘッダー数 = Table.PromoteHeaders(令和6年食中毒発生事例_Sheet, [PromoteAllScalars=true]),
を
組み込む
let
WB = Excel.Workbook(Web.Contents("https://www.mhlw.go.jp/content/001324643.xlsx"), null, true),
先頭のシート = WB{0}[Data],
昇格されたヘッダー数 = Table.PromoteHeaders(先頭のシート, [PromoteAllScalars=true])
in
昇格されたヘッダー数3.URL番号の指定とExcelSheetの読み込みを合わせる
・新規のブックを作成します
・B1,B2にデータを入力します
番号 1324643
・B2の書式を000000000 (0を9桁にします※頭0埋めを表示形式で行います)
・B2を選択して、名前をURLと付けます
3.1 パワークエリのソースコードをコピーします
let
//B2 Excelで付けた名前範囲"URL"から値を取得
文書NO = Excel.CurrentWorkbook(){[Name="URL"]}[Content]{0}[Column1],
// 9桁固定、頭0埋めの数値に変換
strNO = Text.PadStart(Number.ToText(文書NO), 9, "0"),
// URLを組み立てる
strURL = "https://www.mhlw.go.jp/content/" & strNO & ".xlsx",
// Web上のExcelを取得する
WB = Excel.Workbook(Web.Contents(strURL), null, true),
先頭のシート = WB{0}[Data],
昇格されたヘッダー数 = Table.PromoteHeaders(先頭のシート, [PromoteAllScalars=true])
in
昇格されたヘッダー数実行して、確認します
3.2 数値、文章の番号を変更します
厚生労働省公式HPから
https://www.mhlw.go.jp/stf/seisakunitsuite/bunya/kenkou_iryou/shokuhin/syokuchu/04.html
xlsxファイルのリンクを調べる
ブラウザからリンクをコピーする
令和6年（2024年）食中毒発生事例（速報）［76KB］別ウィンドウで開く
https://www.mhlw.go.jp/content/001324643.xlsx
や
(2)過去の食中毒発生状況
令和5年（2023年）食中毒発生状況［56KB］別ウィンドウで開く
https://www.mhlw.go.jp/content/001215431.xlsx
令和3年
https://www.mhlw.go.jp/content/000912971.xlsx
3.3 シート2番目
00:33:05 2番目シート取得 2番目のシート取得方法と複製、注意点
https://www.youtube.com/live/sU2hZA6KmaU?t=1985s
名前指定をやめたので、
コピー、複製して、
シート2番目,3番目を作る？
// Web上のExcelを取得する
WB = Excel.Workbook(Web.Contents(strURL), null, true),
先頭のシート = WB{0}[Data],
ここの数値 WB{0}をWB{1}やWB{2}へ変更する
右側のクエリと接続から複製でコピー作成すると、
親切に、型変換のコードをプラスしてくれる・・・
※シート名が年度ごとに違うので、エラーになる。ここは、カットする。
余談蛇足:
変数名を適切に変更しないで放置すると、
後から見た人が、
先頭のシート = WB{2}[Data],
を見て、
先頭なのに？2？と混乱する。
変数名も直しましょうね・・・と、若手に注意されるオッサンの体験談でした。おいおい。
3.3 3番目のシートが無いブックを読み込んだ時・・・
3番目のシートが無いブックを読み込み、テストします
・クエリと接続から複製でコピー作成
・先頭のシート = WB{2}[Data], 2に変更する(0,1,2で3番目)
4.シートじゃなくて、データの順番ってことが判明する
あれ？3番目のシートと思い込んでたけど、
エラーにならなくて、
テーブルを読み込んで、ヘッダーに1行目のデータが入った・・・
なんだこれ・・・
5.終わりの挨拶
問題点・積み残しが多いライブ配信ですが、
デバッグ処理の参考となれば幸いです。
質問の答えは、頭0埋めでURLを作成して、0番目のデータを取得してみては？
でした。
let //B2 Excelで付けた名前範囲"URL"から値を取得 文書NO = Excel.CurrentWorkbook(){[Name="URL"]}[Content]{0}[Column1], // 9桁固定、頭0埋めの数値に変換 strNO = Text.PadStart(Number.ToText(文書NO), 9, "0"), // URLを組み立てる strURL = "https://www.mhlw.go.jp/content/" & strNO & ".xlsx", // Web上のExcelを取得する WB = Excel.Workbook(Web.Contents(strURL), null, true), 先頭のシート = WB{0}[Data], 昇格されたヘッダー数 = Table.PromoteHeaders(先頭のシート, [PromoteAllScalars=true]) in 昇格されたヘッダー数
なんか、蛇足の方が多すぎだけど、
素人のオッサンがパワークエリをデバッグしてみました。
シートじゃなくて、テーブルを直接取れそうかなぁ・・と思いつつ、
逃げるようにライブを終了します。
質問・感想など、コメント欄に気軽に書いてください。
処理の参考となれば、幸いです。
枠外:イラスト作成の失敗例)Web上のデータを指定したつもりが、机の上に乗ってしまった画像、面白かったので載せてみた。
