20231111パワークエリ備忘録（市町人口を取ってみる）
こちらは、パソコン素人です。
久々にパワークエリ(エクセル)をしたのですが、やはり素人です。汗。
（これらは自分用のメモです。）
不都合あれば削除いたします。
先日、
ネット(「エクセルの学校」さんの質問・回答『毎月アドレスが変わるwebページからデータを取得したい』について検証しました。
そこでは、気象庁の毎日のデータをパワークエリで取得（欲しい日～最新まで）するものでした。（勉強になりました。感謝。）
それを他のwebの記事を取れるように直そうと勉強しています。
ということで、隣県●●県内の市町人口のデータを取得するのを作りました。
（注意：この県のデータ、2023年3月からしか同じアドレスにはなかったので、2022年などは遡れません。扱えたのは、2023年3月から。他の県にすればよかった。）
気になったことを先に書くと、通常PQは、テーブルからデータを取得して、加工の場合、同じテーブルに書き込むことはできません。
（「クエリの結果をテーブルまたはXMLの対応付けと重ねることはできません。他の場所を選択してください」エラー）
今回、シートは１枚です。テーブルのA2セルの日付（ここは手入力）を使っているのですが、
元データは、webから取るためか、M言語やテーブル名を手作業で修正したためか、
テーブルを増やさずに実行可能でした。
他にもいろいろやってみないと分からないこと多い。
ヘッダー（表題）のリネームも、パワークエリのステップで作業していくと、別の月データで試した際、
"2023/04/01"がそのデータでは"2023/05/01"だったりして、変数に置き換えるなど。
パワークエリは、各データを直すようにはできていないので、一部を直すのでなく、列ごとに直したり、
結構変な作業をステップ上でしています。（隔靴掻痒）
あと、詳細エディターで完了を押したり、きちんと読み込んで保存したりしないと、せっかくのコードが保存されてなかったりします。
パラメータは、カッコをなくし、コメントアウトしたりして、なんとなく仕組みを知れました。
(年 as number,月 as number) as table =>
あとあと、#が変数名だったり、//がコメントアウトだったり（chatGPT3.5は間違ってました）
半角スペースが不用意に入っていたり、初心者レベルです。
分からない場合は、１個１個見本の「気象庁」のクエリを振り返るしかなかったです。
（でも最後の方は、かなり変わりました。）
＜覚えている違いなど＞
元の気象庁データはhtmlだが、これはPDF取得。（取得の仕方も、テーブルの整理も違う）
元の気象庁データは、日単位だが、人口は月単位。年月日の作り方違います。
（テーブル：名前も変えます）
（下記は自分用コードです。）
*******************************
クエリ名：fnテーブル取得
(年 as number,月 as number) as table =>
let
和暦年 = 年 - 2018, //いったん和暦に直す必要)
ソース = Pdf.Tables(Web.Contents("https://www.pref.●●.jp/〇〇/documents/r" & Text.From(和暦年) & "_" & Text.From(月) & "jukijinko.pdf"), [Implementation="1.2"]),
フィルターされた行3 = Table.SelectRows(ソース, each ([Name] = "Table001 (Page 1)")),
削除された他の列 = Table.SelectColumns(フィルターされた行3,{"Data"})
in
削除された他の列
*******************************
クエリ名：住民基本台帳更新
let
ソース = Excel.CurrentWorkbook(){[Name="住民基本台帳更新"]}[Content],
開始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"}),
削除されたエラー = Table.RemoveRowsWithErrors(#"展開された Column1", {"tbl"}), //列名右クリック削除
#"展開された tbl" = Table.ExpandTableColumn(削除されたエラー, "tbl", {"Data"}, {"Data"}), //tbl.Data名直し
#"展開された Data" = Table.ExpandTableColumn(#"展開された tbl", "Data", Table.ColumnNames(#"展開された tbl" [Data]{0})),
フィルターされた行 = Table.SelectRows(#"展開された Data", each ([Column1] <> null and [Column1] <> "合計" and [Column1] <> "市合計" and [Column1] <> "町合計")),
昇格されたヘッダー数 = Table.PromoteHeaders(フィルターされた行, [PromoteAllScalars=true]),
firstColumnName = Table.ColumnNames(昇格されたヘッダー数){0}, //修正追加
#"名前が変更された列 2" = Table.RenameColumns(昇格されたヘッダー数,{{firstColumnName, "年月日"}}),
変更された型 = Table.TransformColumnTypes(#"名前が変更された列 2",{{"年月日", type date}, {"市町", type text}, {"男", type text}, {"女", type text}, {"計", type text}, {"世帯数", type text}}),
フィルターされた行1 = Table.SelectRows(変更された型, each ([市町] <> "市町")),
追加されたカスタム = Table.AddColumn(フィルターされた行1, "カスタム", each Date.EndOfMonth([年月日])),
並べ替えられた列 = Table.ReorderColumns(追加されたカスタム,{"カスタム", "年月日", "市町", "男", "女", "計", "世帯数"}),
削除された列 = Table.RemoveColumns(並べ替えられた列,{"年月日"}),
変更された型1 = Table.TransformColumnTypes(削除された列,{{"カスタム", type date}}),
#"名前が変更された列 1" = Table.RenameColumns(変更された型1,{{"カスタム", "年月日"}})
in
#"名前が変更された列 1"
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
