20231030パワークエリ勉強PQ（ひとりBPR）エクセルの学校様 気象庁データ取得 パラメータ 備忘録
パソコン素人です。
パワークエリ素人です。
自分用のメモです。備忘録。
読んでも分からないと思います。（汗）
間違いもあると思います。
（なお、ホームページの引用等不都合がありましたら、早急に削除いたします。）
<前置き>
２、３年前にエクセルのパワークエリなどを本で勉強した。
結構、ピボット解除など、エクセル内部では日常的に使ったりしているが、最近、もっと使い道を広げないといけない気がしてきた。
というのも、
「オープンデータを取り込んで、ダッシュボード化したりする」とか、
「データをマップに自動的に（？）落とし込み表示する」とかが、
ごく一部（周り？）で流行っているようだ。
でも、忙しい人らに興味本位で聞くわけにもいかないので、個人的に何かできないかやってみる。
でも、今現在何もできていないので、その備忘録。
（「ひとりBPR」と書いたように、他にも少し何か出来たら書いていくつもりです。）
<以上、前置き>
ネットで、
パワークエリで、比較的楽してwebデータを取ってくる質問と回答を見つけた。
（エクセルの学校様「毎月アドレスが変わるwebページからデータを取得したい」（感謝））
すこしやってみたが、最後の方にM言語だが、クエリを２つ利用するとか、パラメータの設定とか、無名関数とか、eachの繰り返し処理が多くあり、この記事だけでは自分には理解できないことが分かった。
（基礎がない。）
少し考えてみました。（うーん、多分よくできているコードだと思いました。）
詳しくは該当ページを参照下さい。
要するに、エクセルのパワークエリで、気象庁のデータなど毎月urlが変わる場合どのようにしたらいいのか、という質問と回答でした。
前半で、上記の質問の答えは解決したのですが、続いて下記の質問がでました。
前半では、URLをPQの中で、可変にして、毎月変わるデータページを取得するものでした。（パラメータを取るクエリと本体の２つのクエリで解決。）
記事の前半は省略して「後半」部分を下記にメモします。（転記）
***************************************後半の質問
すみません。気象庁の平均気温をWebクエリで取得する件で追加の質問です。
(´・ω・`）さんとマナさんのご教授のおかげで2021年9月といった固定の年月のデータは取得できるようになったのですが
可能であれば、いつでもデータメニューの『すべて更新』ボタンで連続した最新のデータを取得するようには出来ないでしょうか。
例えば、前回取得したデータが2021年9月20日までのデータであった場合、2021年10月27日に『すべて更新』ボタンをクリックしたら
前回の9月20日までのデータに追加するかたちで10月27日までの連続したデータを取得するといったことは可能なのでしょうか？
可能であれば、その方法を教えていただきたいです。
いつも教えていただいてばかりで申し訳ないのですが、よろしくお願いします。
（豆腐とキムチ） 2021/10/27(水) 11:36
****************************************
↑うーん、確かに知りたいが、難易度がさらに上がった。
（以下自分のメモだが、下記もややこしくなった。汗）
****************************************後半の大事なコード１（完成形）
クエリ名：気象庁
let
開始tbl = Table.Buffer(ソース),
見出し = Table.ColumnNames(開始tbl),
開始年月日 = Date.From(Table.Column(開始tbl, 見出し{0}){0}),
取得日 = Date.AddMonths(Date.StartOfMonth(開始年月日), -1),
開始年 = Date.Year(取得日),
開始月 = Date.Month(取得日),
今月末 = Date.EndOfMonth(DateTime.Date(DateTime.LocalNow())),
WEBから = List.Generate(
()=> [年 = 開始年, 月 = 開始月, 取得月 = 取得日, tbl = #table({},{})],
each [取得月] <= 今月末,
each [
取得月 = Date.AddMonths([取得月], 1),
年 = Date.Year(取得月),
月 = Date.Month(取得月),
tbl = fnテーブル取得(年, 月)
],
each [[取得月],[tbl]]
),
テーブルに変換済み = Table.FromList(WEBから, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
削除された最初の行 = Table.Skip(テーブルに変換済み,1),
#"展開された Column1" = Table.ExpandRecordColumn(削除された最初の行, "Column1", {"取得月", "tbl"}, {"取得月", "tbl"}),
#"展開された tbl" = Table.ExpandTableColumn(#"展開された Column1", "tbl", {"Data"}, {"Data"}),
#"展開された Data" = Table.ExpandTableColumn(#"展開された tbl", "Data", Table.ColumnNames(#"展開された tbl" [Data]{0})),
フィルターされた行 = Table.SelectRows(#"展開された Data", each [日] <> "日"),
追加されたカスタム = Table.AddColumn(フィルターされた行, "年月日", each Date.AddDays([取得月], Number.From([日]) -1)),
削除された列 = Table.RemoveColumns(追加されたカスタム,{"取得月", "日"}),
並べ替えられた列 = Table.ReorderColumns(削除された列, 見出し),
フィルターされた行1 = Table.SelectRows(並べ替えられた列, each [年月日] >= 開始年月日)
in
フィルターされた行1
****************************************後半の大事なコード２（完成形）
クエリ名：fnテーブル取得
(年 as number, 月 as number) as table =>
let
url="http://www.data.jma.go.jp/obd/stats/etrn/view/daily_a1.php?prec_no=44&block_no=0371&year=" & Text.From(年) & "&month=" & Text.From(月),
ソース = Web.Page(Web.Contents(url)),
フィルターされた行 = Table.SelectRows(ソース, each ([Source] = "Table")),
削除された他の列 = Table.SelectColumns(フィルターされた行,{"Data"})
in
削除された他の列
****************************************
↑完成形としてはこの２つ。これを使って、気象庁データをある日付～当日まで１個のテーブルに更新する。
<事前準備>
その前に、準備。
後半の大事なコード１（完成形）だが、完成形の前に、下記のコードを作成して、完成形で使用する気象庁という名の「テーブル」を作る。
**************************************１の完成前コード・・・・これを後で「コード１（完成形）」に修正する。
１）クエリ名：気象庁
※最初に１回だけ実施
let
ソース = Web.Page(Web.Contents("https://www.data.jma.go.jp/obd/stats/etrn/view/daily_a1.php?prec_no=44&block_no=0371&year=2021&month=9&day=&view=")),
Data0 = ソース{0}[Data],
変更された型 = Table.TransformColumnTypes(Data0,{{"日", type text}, {"降水量(mm) 合計", type text}, {"降水量(mm) 最大 1時間", type text}, {"降水量(mm) 最大 10分間", type text}, {"気温(℃) 平均", type text}, {"気温(℃) 最高", type text}, {"気温(℃) 最低", type text}, {"風向・風速(m/s) 平均 風速", type text}, {"風向・風速(m/s) 最大風速 風速", type text}, {"風向・風速(m/s) 最大風速 風向", type text}, {"風向・風速(m/s) 最大瞬間風速 風速", type text}, {"風向・風速(m/s) 最大瞬間風速 風向", type text}, {"風向・風速(m/s) 最多 風向", type text}, {"日照 時間 (h)", type text}, {"雪(cm) 降雪 合計", type text}, {"雪(cm) 最深積雪 値", type text}}),
削除された最初の行 = Table.Skip(変更された型,3),
保存された先頭行 = Table.FirstN(削除された最初の行,1),
追加されたプレフィックス = Table.TransformColumns(保存された先頭行, {{"日", each "2021/9/" & _, type text}}),
変更された型1 = Table.TransformColumnTypes(追加されたプレフィックス,{{"日", type date}}),
#"名前が変更された列 " = Table.RenameColumns(変更された型1,{{"日", "年月日"}})
in
#"名前が変更された列 "
**************************************
上記を実施することで、気象庁のテーブルができる。（テーブル中のデータは消すので何でもいい。）(追記：１レコード目の年月日は、開始データとして使います。また、もしも開始日を変更したければ、このセルを手打ちして修正すればＯＫです。)
********************************
上記のコードを実施して確認した点
・年またぎは大丈夫か？
・月途中からの更新は大丈夫か？
→コードからも確認できますが、2022年10月～2023年10月とか問題なかったです。
2022年10月10日等1日始まりでなくても問題なかったです。
（よくできてます。）
後半の大事なコード１（完成形）の自分用解説==================
クエリ名：気象庁
let
開始tbl = Table.Buffer(ソース),
見出し = Table.ColumnNames(開始tbl),
開始年月日 = Date.From(Table.Column(開始tbl, 見出し{0}){0}),
①取得日 = Date.AddMonths(Date.StartOfMonth(開始年月日), -1),
==========取得日は、テーブルの１行目データの当月の１日目の１か月前
（例：10/2→10/1→9/1）（１個上で日付変換済(Date.From)）
②開始年 = Date.Year(取得日),
==========開始年は、取得日の「年」
（例：2023/9/1なら2023）
③開始月 = Date.Month(取得日),
==========開始月は、取得日の「月」
（例：2023/9/1なら9）（実際より１か月前）
今月末 = Date.EndOfMonth(DateTime.Date(DateTime.LocalNow())),
④-⓪WEBから = List.Generate(
==========ここで、「気象庁テーブル」を作る（全期間分）。（加工前）
④-①()=> [年 = 開始年, 月 = 開始月, 取得月 = 取得日, tbl = #table({},{})],
==========「()=>」は無名関数らしいです（？）。
「年, 月, 取得月, tbl」という４つのフィールドの「空レコード」を作ります。
（特に、最後のtbl(テーブルは列数も未定の空テーブル)）（多分）。
④-②each [取得月] <= 今月末,
==========予約語「each」で「取得月」を作成。後ろの「 <= 今月末」が終了条件
（取得月（開始日１か月前から今月末まで）をたくさん作成します。）
⑤each [
取得月 = Date.AddMonths([取得月], 1),
年 = Date.Year(取得月),
月 = Date.Month(取得月),
tbl = fnテーブル取得(年, 月)
],
==========ここは、「後半の大事なコード２（完成形）クエリ名：fnテーブル取得」にパラメータを渡しています。
（後述）
（「取得月 = Date.AddMonths([取得月], 1),」とすることで、翌月分のデータをひと月単位で作っています。）
⑥each [[取得月],[tbl]]
==========取得月ごとのテーブルを作ります。
（④-②、⑤、⑥は、並列（順次処理）で、④-②の終了条件（<= 今月末,）まで続きます。
),
==========④-⓪閉じる（繰り返し）
=====================================================================================
後半の大事なコード１（完成形）の自分用解説（ステップごとの状態の確認）==================
例：2023/10/10～2023/10/30の場合
ソース
開始tbl
見出し
==========リスト
年月日
降水量合計
降水量最大１時間
降水量最大10分間
気温平均
気温最高
気温最低
湿度平均（単位省略。以下項目省略）
開始年月日
==========例：2023/10/10
取得日
==========例：2023/09/01
開始年
開始月
今月末
WEBから
==========リスト
1 Record
2 Record（９月、１０月分）
テーブルに変換済み
==========Column1
1 Record
2 Record（９月、１０月分）
削除された最初の行
==========Column1
1 Record（前月を削り１０月分のみに）
展開されたColumn1
==========取得日 tbl
2023/10/01 table(テーブル)
展開されたtbl
==========取得日 Data
2023/10/01 table(テーブル)
展開されたData
==========取得日 日 降水量など横並びでデータあり
（上３つのレコードは縦に「日」「日」「日」で、気象庁の表のまま。）
（取得日はすべて2023/10/01。日のデータは、１，２，３と日ごと）
フィルターされた行
==========上３つのレコード削除（表整う）
追加されたカスタム
==========最終列に、１列目の取得日と日データを組み合わせて、レコードごとに日付を入れている。
削除された列
==========取得日と日を削除
並べ替えられた列
==========カスタム列「年月日」を左列へ移動
フィルターされた行1
==========１０月１日からだったレコードを開始年月日１０月１０日以上にフィルター
============================================================
============================================================
後半の大事なコード２（完成形）の自分用解説==================
注意：「fnテーブル取得」（接続専用）は、サブルーチンとして使うけど、単独で最初に実行してみると、エラーになります。
（多分）
「データベースドライバーの操作を完了できませんでした」
具体的には「呼び出された関数fnテーブル取得」というのが、新たにできて、それがエラーになります。
（ダウンロードは完了していません）
でも、これでOKです。本体の大事なコード１からならばエラー起こしません。
エラーを起こすのは、このクエリ単体で使うと読み込み先の設定をしていないからです。
大事なコード１から使用する場合は、テーブルとして使うのでこれでいいのです。
（単体で使う場合のエラー回避には、できてしまったクエリの読み込み先（書き出し先）を接続専用にするか、
シートに書き出せば、エラーは回避できます。
が、そんなことをするより、できてしまった「呼び出された関数fnテーブル取得」を削除すればOKです。）
（なお、このパラメータ入りクエリ「fnテーブル取得」は、一旦使用するとクエリと接続ペインで、ダブルクリックしても中は見れません。）
クエリ名を右クリックして編集で開きます。）
クエリ名：fnテーブル取得
(年 as number, 月 as number) as table =>
==========「=>」より前は、入力するパラメータです。
let
url="http://www.data.jma.go.jp/obd/stats/etrn/view/daily_a1.php?prec_no=44&block_no=0371&year=" & Text.From(年) & "&month=" & Text.From(月),
ソース = Web.Page(Web.Contents(url)),
フィルターされた行 = Table.SelectRows(ソース, each ([Source] = "Table")),
削除された他の列 = Table.SelectColumns(フィルターされた行,{"Data"})
in
削除された他の列
==========上記のパラメータで気象庁の当該年月のデータにアクセスし、テーブルにします。
この工程を本体の大事なコード１から、取得月数分（前月を含めて＋１月分）テーブルを作成します。
その他：（パワークエリの使い方で見つけたこと）（無関係）
便利機能
適用したステップの歯車（ギヤ）マーク・・・クリックすると、中の変数を変えることができる。
追記：コードの最初の方で、開始の１か月前の日付を取得しています。
で、コードの最後の方で、当該データを削除しています。素人なので分かっていませんが、途中のfn関数で、月を回す必要がある（１ト月ずつずらす）ため、この方がコード上簡単だということでこうなっている・・・ような気がします。
感想：この質問と回答は、とても自分には、有益でした。おそらく基本として覚えることで、いろんなことに応用できると思います。
感謝します。
気になるのは、前月のデータテーブルを取得して、最終的に切るところや、
テーブルからデータへの変換などかなりいろんな工程があるので注意が必要です。
過去に本を読んだけど、こんなやり方までは紹介されてなかった気がする。
どこまで、ポチポチ、やって、どこから、詳細エディタで、M言語を追加修正するのか、結構考えることあるなあ。
（また、pythonとかでもいいのかもね。）
