X

【Excel】Power Queryを語るスレ【Power BI】

2019/10/20(日) 11:59:28.17
Excel 2016から標準装備、Excel 2010以降も追加可能なPower Query。
使ってみると驚く部分は多々あれど、普及したとは言い難い。

手探りで分かったこと、英語の資料から判明したこと等、いろいろ語って下さい。
Power Pivot、Power View、Power BIについても歓迎。

公式　「概要と学習」 (日本語)
https://support.office.com/ja-jp/article/Power-Query-%E6%A6%82%E8%A6%81%E3%81%A8%E5%AD%A6%E7%BF%92-ed614c81-4b00-4291-bd3a-55d80767f81d

Excel 2010、2013用アドイン (日本語)
https://www.microsoft.com/ja-jp/download/details.aspx?id=39379

公式　M言語レファレンス (英語)
https://docs.microsoft.com/en-us/powerquery-m/index

「Power Query メモ」 (外部サイト：M言語レファレンスの日本語化等)
https://sites.google.com/site/powerquerymemo/
321名無しさん＠そうだ選挙にいこう
垢版 |
2023/06/06(火) 07:34:36.14
>>320
リレーションシップだから処理が軽く早いんだと聞いていましたが違ったのですね
322320
垢版 |
2023/06/06(火) 10:15:46.29
たとえば、>>297 の
日本版電力エリア需給実績（1時間値）もどきをやると、
1時間平均値なので、1供給エリアごとに年間8,760行。

個別の供給10エリアはあって、
さらに、50Hz連系時間帯串刺し、60Hz連系串刺し、10エリア計時間帯串刺しで＋3エリア
都合、13エリア分になる。
年間：8,760行×13エリア＝113,880行/年。

ここまでの、Web公開csvファイルDLからクレンジング、加工、集計とかの下処理は、
Excelで個別にクエリーやVBAを使って月次処理。

公開開始の2016年度から2022年度までの7年間だと、797,160行。
3ヶ年だけ、蓄電池シミュレーション版も入れたら、104万行を超えてもうた。

で、まだ勉強しながら作ってる途中だけど、
Power BI Desktopから年度別Excelファイルを読みに行って、
統合してデータモデルに格納（そもそも、BIにはワークシートがない）、
これの月別1時間値推移の、積み上げ棒＆折れ線の複合グラフを作ると・・・

・スライサー切替えによるグラフ再描写が1秒弱。(第8世代Core i7のKなし)
・月次集計、年次集計、エリア間比較、電源構成比も楽ですよ
・BIの.pbixファイルだけなら130MBくらい
・ただし、BIだけでメモリーは1.4GB食ってます、
　制作途中でBIの中でクエリー更新すると、CPU負荷率とメモリー消費量がもっと跳ね上がって、ちょっと時間が掛かります、
　出来てしまえば、スライサー切替えによる再計算は速いです・・・

って話。

https://i.imgur.com/C74ul4i.png
https://i.imgur.com/omO9dQm.png
https://i.imgur.com/1QPmbIO.png
2023/06/06(火) 16:36:36.54
データモデルに入れちゃったテーブルを外せる？
2023/06/06(火) 17:13:19.88
外せるよ。

「クエリと接続」の右ペイン出して、
目的のクエリー右クリックして「読込み先」、
「このデータをデータモデルに追加する」のチェックを外す
だったか。
Power Pivot開いて目的のクエリーのタブ右クリックして削除でも行けたかな？
2023/06/06(火) 17:42:40.53
>>324
ありがと！
326名無しさん＠そうだ選挙にいこう
垢版 |
2023/06/06(火) 20:01:51.66
パワーピボットで3種類の値の入った列を複数列、行フィールドに入れてフィルターをかけたらどうなりますか？
ANDでフィルターをかけられた結果が表示されますか？
2023/06/07(水) 01:20:58.20
何をどうしたいのかさっぱり分らんけど、
まず3種類の値を3列に分ける方が先なんでねーの？
328名無しさん＠そうだ選挙にいこう
垢版 |
2023/06/07(水) 07:18:28.24
>>327
やはり一つの列には一つの値しか存在してはいけないのですね
私もア以外の値をなくすことによってピボットテーブルで集計する事ができました

https://i.imgur.com/bTwA4tg.jpg


https://i.imgur.com/z7yJTK3.jpg

この様に列を並べて一つのピボットテーブルで集計したいです
2023/06/07(水) 08:26:02.56
>>328
「一つの列に一つの値」は基本中の基本、イロハの「イ」やで。
それと、他人に説明するとき、示す文章と絵を一致させるのも、イロハの「イ」や。

よーく、覚えとけ。
330名無しさん＠そうだ選挙にいこう
垢版 |
2023/06/07(水) 08:45:36.27
>>329
一つの列に一つの値が基本中の基本なので一つの列しか並べられない
という事はわかりました

ではなぜこれがア　イ　ウの値のある列ではなく、アしかない複数の列を並べる場合だと上手く機能するのでしょうか？
331名無しさん＠そうだ選挙にいこう
垢版 |
2023/06/08(木) 21:58:54.72
ピボットテーブルにもデータモデルに追加すると動作が重くなる　とか　メモリ不足等のエラーメッセージが出る　という事はありますか？
2023/06/08(木) 22:08:42.54
データモデルとワークシートの両方に読込むと
おかしくなる時がある
って話でねーの？
2023/06/08(木) 22:09:20.85
>>330
この馬鹿はあちこちで聞き回って聞かれたことに答えもしないクズ
2023/06/09(金) 00:00:23.22
かまってちゃんはお断り
335名無しさん＠そうだ選挙にいこう
垢版 |
2023/06/09(金) 20:55:11.42
>>331
データの格納効率(圧縮率？)はワークシートよりデータモデルの方が良いらしい
でもその処理で余計にCPUやメモリは消費するかもしれないね
336名無しさん＠そうだ選挙にいこう
垢版 |
2023/06/11(日) 10:42:42.93
列にフィルターを掛けます
フィルターされた　のステップが挿入されます
このステップのコードをコピーすれば何回でも流用できますか？
337名無しさん＠そうだ選挙にいこう
垢版 |
2023/06/14(水) 22:38:53.61
SQLの基本を解説する本を読んでいてパワークエリに通じるものを感じます
つまりそもそもSQLを覚えましょうという事なのでしょうか？
2023/06/14(水) 22:40:42.75
どゆこと
2023/06/15(木) 01:16:22.70
ギンガイザーガムでも食ってろという意味では?
340名無しさん＠そうだ選挙にいこう
垢版 |
2023/06/15(木) 21:07:17.33
パワークエリはSQLでできる事をさもすごい新機能の様に言っているだけでしょうか？
2023/06/16(金) 04:39:41.55
M言語とSQLが同じに見えるかわいそうな子
SQLのQがなんだか分かるようになるといいね
2023/06/16(金) 09:03:52.63
データベースの知識はあって損ないわな
2023/06/16(金) 10:05:08.67
むしろコレ扱う人ってSQLくらい分かるでしょう
344名無しさん＠そうだ選挙にいこう
垢版 |
2023/06/16(金) 12:14:32.96
Pythonを少し触りはじめた人がExcelでは上手く作れない複雑な計算（数学？）のグラフがPythonだったら簡単に作れると驚いていたのですが、それはデータベースとかライブラリだからだとしたらそういう事だったのかという感じ
2023/06/18(日) 10:08:57.13
まだこのスレあった！よかったー
346名無しさん＠そうだ選挙にいこう
垢版 |
2023/06/19(月) 22:48:24.89
パワークエリでPythonを動かす人もいるのでしょう？
347名無しさん＠そうだ選挙にいこう
垢版 |
2023/08/04(金) 09:28:17.84
リレーション先のテーブルにある別カラムを条件に、棒グラフの1本だけを色変えたりできないかな？
2023/08/04(金) 12:22:46.80
ん？
どういう集計のどういうグラフか、詳細が分らないが、

条件使ってPowerQueryか関数で別系列に出来るなら、
色付け自体は固定的にグラフの設定

棒グラフじゃないけど、例えば、
最大値を別の色のマーカーにしたいとき、
最大値の系列を別に作って、他データを#N/Aにして
その1点だけ、あたかも別系列で重ねて表示させる、
なんてことはよくやる。

系列名が動的に変わるならVBA

とか。
2023/08/23(水) 09:28:23.14
やっぱり、出たよ

Introducing Python in Excel: The Best of Both Worlds for Data Analysis and Visualization
https://techcommunity.microsoft.com/t5/microsoft-365-blog/introducing-python-in-excel-the-best-of-both-worlds-for-data/ba-p/3905482?ocid=usoc_TWITTER_M365_spl100004503643083

Microsoft、「Python in Excel」を発表 〜Windows向けベータ版でテスト開始
https://forest.watch.impress.co.jp/docs/news/1525532.html
2023/09/26(火) 06:21:44.76
List.TransformManyの存在理由が納得できるような使い方の例を示してください。
2023/10/02(月) 03:26:16.21
pdf表のデータ・スクレイピングは難儀だけど、
例えば、毎月発表される東電パワーグリッドの再エネ接続量のこのファイル、
https://www.tepco.co.jp/pg/consignment/system/pdf/newenergy_hondo_backnumber.pdf

これから%の表は抜きで
電源種別、申込ステータス、年月ごとの容量(万kW)をリスト化にするには、
・ページ番号・行番号・列番号使って、
・いったん時系列・項目・データ部に分け、
・番号を頼りにマージで再合成
すれば、PowerQueryでも出来んのね。
2024/02/06(火) 00:00:20.41
Power BIで使える
Web.BrowserContents
は、ExcelのPower Quertでは使えないのか？

https://learn.microsoft.com/ja-jp/powerquery-m/web-browsercontents
2024/02/29(木) 16:59:07.69
Webデータを読み込むクエリーで
ステップを重ねていって、途中でエラーになったとき（例：該当するデータがない等）、
その後のステップを飛ばして(if then elseでやるか・・・)
データがないのテーブルを#tableで生成して終わるような処理は
出来るかな？
2024/02/29(木) 17:13:55.05
あ、出来そうだわ
355名無しさん＠そうだ選挙にいこう
垢版 |
2024/03/08(金) 15:47:22.05
初歩的な質問かもしれんのですが、この動画https://youtu.be/tFrjr3IiIlM?si=nYS3GRwPgYwMprmO　見ながら勉強しててドリルダウンまで行ったんだけど何故か綺麗に左からGDP順でドリルダウにならず…国名の五十音順になってしまうのって何故だと思う？
356名無しさん＠そうだ選挙にいこう
垢版 |
2024/03/08(金) 16:05:36.54
>>355
軸の並び替えで対処できたわ
2024/06/16(日) 09:30:22.60
総務省が発表している年次都道県別の年齢階層人口データ（Excelブック）を
2段の関数クエリ（シート別・男女別）を使ってリストに変換する説明
www.youtube.com/watch?v=jDIwpBCVibY

関数クエリの使い方をコンパクトにまとめていた
2024/08/27(火) 12:57:53.96
powerBI詳しい方いますか？
2024/08/28(水) 21:33:28.21
いない
2024/08/28(水) 21:53:16.48
BIは、
・自分が欲しいグラフは作れなかった
・Web共有するには結局有料版が必要
などで挫折した。

Excel版Power Queryの範囲内で、
Webスクレイピンクやパラメータークエリーくらいまでなら、
なんとか答えられるかも。
2024/08/30(金) 21:36:33.90
ホントそれ
共有の為だけに有料版入れてくれなんて言えないしな
2024/08/30(金) 22:30:33.58
>>358
詳しいよ
2024/09/16(月) 19:32:43.50
>>362
基本操作以外のおすすめの本ありますか？
もしくはサイト
売ってる本って基本動作だけでやりたいことが載ってないので困ってます
例えば
・スライサーで選択したデータの画像だけを表示したい
・そもそも画像の取り込み方はどうやるのか
・同じビジュの複数のグラフの差異を表示したい

などです
2024/09/17(火) 08:18:16.14
>>363
他のETLツール→PowerQueryだったから、本は読んでない
なんか、PowerQueryの用途を根本的に間違えてる気がする。これはデータを成形するためのツールなので、相手にするのはRDBやcsv、Excelのテーブルだよ
2024/09/21(土) 10:17:45.98
>>363
質問内容はPower Queryに関するものではなくて
IMAGE関数とPower Pivot、グラフの問題
2024/09/21(土) 10:20:51.82
ようつべのExcelドカタあたりがいいんでね？
あそこはpowerqueryだけでもないし。
ただ、vbaは扱っていなかったかと。
2024/09/22(日) 15:18:15.74
YouTubeはあまり参考にならないとの書き込みあったがな
2024/09/25(水) 22:21:08.58
>>364
返事が遅くなりました
画像はPowerBIでマインドマップを作るために必要だったんです
2024/09/26(木) 12:14:09.62
>>368
PowerBIの質問だね
ググったら出てくるけど、直接データとして取り込むことは出来なくて、web上やSharPoint上の画像ならリンクの文字列、ローカルの画像ならエンコードした文字列をデータとして食わせる

状況としては後者な気がするけど、たぶんめんどくさいよ
フィルターしたいだけだったら、NotionやConfluenceのデータベース機能を使うか、Excelでも十分かもね
2024/10/06(日) 14:54:04.04
- Webからcsv等で取得した複数項目・複数地点の30分値データを、
　・過去から最新リアルタイム値まで、
　・月単位、週単位、日単位の30分刻みグラフ表示で、
　素早く行き来したい。
- 2016年度から～2024年度前月月末まで（2016/4～2024/9末）のPastDB-30分値は、
　ローカルのcsvファイルをダウンロード済みで、1,937,520行ある。
　データモデルで更新するのに2分程度かかる。
　基本的に一度読み込めば、遡及修正がない限り、更新する必要はない。
- 至近のデータ（LastDB）はWebのcsvファイルをDLせずに直接クエリーで読み込むが、
　最大月31日×直近2ヶ月分としても、2,976行でしかない。
- 統合して運用したいが、Power Pivotのフィルター変更するたびに2分以上も更新にかかるのは避けたい。
- PastDB、LastDBとも「接続のみ」のクエリーのままにしたのでは、更新に時間が掛かるので
　・それぞれデータエリアに格納し、
　・これを繋げるクエリーを作って、これもまたデータエリアに格納して、
　これをPower Pivotのデータ元にしたら良いんだべか？
2024/10/06(日) 16:54:51.48
copilot に聞けば
2024/10/07(月) 00:46:00.27
Copilotに聞いてみた。

やっぱり、
1. PastDB、LastDBをそれぞれデータエリアに格納し、
2. さらにPower Queryで両者を「結合クエリ」オプションを使用して結合し、
　・・・ここはマージの「結合」ではなくて、「追加」の間違いでないの？と再質問したら、
　　　「ご指摘ありがとうございます。その通りです。『追加』が正しいですね。」だって・・・
　それもデータモデルに格納して、
3. ここからPower Pivotでピボットテーブル、グラフ化せよ。
と。

やってみるわ。
2024/10/09(水) 18:20:08.38
>>372
結果：
・読み込み済みのデータに対しては、1週間分絞込みのピボットテーブルでは
　フィルター（スライサー）変更で気持ちよく動く。
・最新のWebデータをLastDB経由で取り込むために、結合クエリを更新すると、
　やっぱりPastDBも最初から読み込み、合計で190万行超あるので
　更新に2分＋αかかってしまう。

Copilotが「PastDBの最後にTable.Buffer入れてみ？」と提案してきたが、
メモリー26GBも食うわ、CPU使用率90%前後に張り付くわで、
ちっとも速くならない。

なんでも、Power BI版Power Queryには、
個別のクエリ プロパティに「このクエリを更新しない」オプションがあるんだけど・・・
とか言っていた。
2024/10/28(月) 10:36:51.31
データの並び順がPower Query エディターで指定した順にならない場合の対応方法はどうしていますか。よろしくお願いいたします。
2024/10/29(火) 00:35:25.53
>>374
っTable.bufferかTable.stopfolding
2024/10/30(水) 14:29:00.71
>>375
レスありがとうございました。参考にさせていただきます。
2024/10/30(水) 18:45:40.88
>>376
別にあんたのためじゃないんだからね。
自分も困ってたからレスしただけなんだからね(｀･ω･´)
2024/10/31(木) 14:15:48.13
BI でX軸にしている時系列が例えば8月、7月と逆転している場合はどうしたら直りますか？
2024/10/31(木) 18:59:42.24
上記の件判明したので大丈夫ですお騒がせしてすみません。
2024/11/01(金) 02:11:53.46
時代はマクロ（笑）よりパワークエリだな！
2024/11/01(金) 06:36:52.31
何で広まらないんだ？
2024/11/01(金) 07:54:33.32
機能の存在すら知らない人が多数だから
ExcelスレにもExcelって全然進化がないってレスがあった
2024/11/02(土) 05:51:18.12
接続文字列がよくわからん文字になるのが嫌
SharepointのExcelからadoでデータ取り出そうとしても上手くいかないから
パワーQの接続文字列をパクろうとしたら
変な文字列になってヘコー
2024/11/02(土) 13:57:20.96
>>380
Power QueryとVBA組み合わせるねん。
PQにはブックの保存とか、
ファイル操作機能なんかはないさかい。
なんだったらPythonも組み合わせるねん。
対話型のWebサイトで条件指定してスクレイピングするとか、
高度なグラフ機能は、
PQやExcelにはないねん。
2024/11/02(土) 13:59:32.15
>>384
あと、WebサイトがCSSの作りだと、
PQでは厳しいわな。
2024/11/02(土) 14:26:54.16
>>382
それだね。ベテラン程昔のExcelの知識のまま止まっている。
面倒臭いとか余計な機能だの言ってさ。
2024/11/02(土) 14:55:24.61
なんで公共の場で地方言葉なの？
2024/11/02(土) 16:38:47.37
公共の場？
アホかいな
茨城弁にすっか？
2024/11/02(土) 16:58:55.46
東京弁はご遠慮下さい
2024/11/03(日) 03:01:36.80
使う言葉に惑わされて、
中身を見ない／見ようとしない／分からないアホへの
最大の皮肉
2024/11/03(日) 12:27:44.24
>>387
時代はとうに多様性　右へ倣わないで
って工藤静香も言ってる
2024/11/05(火) 00:19:34.88
新しい機能に否定的な老害Excelユーザーwww
2024/11/25(月) 20:52:07.67
ネットから30分間隔で更新されるようなデータを取り込んで
1ブックにシーケンシャルなクエリーが20個くらいで処理する時、
途中のクエリーをちょっと修正して全て更新かけると
下流のクエリーにエラーが出て
糞詰まりみたいに止まってしまうことがある。

修正に齟齬がなくても、
上から順に詳細エディターで一つ一つクエリー開いて
辛抱強くプレビュー更新していくと、解消されるのだけれど。
394393
垢版 |
2024/11/30(土) 13:52:22.89
>>393 の自己解決

「接続のみ」でシーケンシャルにクエリー動かすと
容量32GBでも、速度が2,666MHzのメモリーでは
あっぷあっぷになるのかしら？
タスクマネージャーでは、容量自体は余裕があるみたいけど。

なので、
1. 「接続のみ」にしてある、
　keyとなる被参照のクエリーは最大行数がせいぜい2万行程度なので、
　いちいちワークシートに読み込ませ（bookの容量自体は増えるが、まだ余裕）
2. これらを参照するクエリーの中では、クエリー名だけで指定せず、
　ワークシートのテーブルとして読み込ませたら、
　　Excel.CurrentWorkbook(){[Name="クエリー名"]}[Content]

あら不思議。
糞詰まりせず、爆速で流れおった。

ワークシートでなく、データモデルとして読み込ませると、
なんか、いちいち外を読み込みに行ってたような気がするので
また遅くなるかも？

後で比べてみるけど。
2025/01/01(水) 08:46:53.71
むぅ・・・

M式言語の週番号を求める関数
　Date.WeekOfYear([年月日], Day.Monday)
は、オプション指定で月曜始点にできても、ISO 8600基準の、
・その年の第1週は、1月4日を含む週
　（あるいは「最初の木曜日を含む週が、その年の第1週である」）
にならない。

Excelワークシート関数の
　WEEKNUM(年月日, 21)
なら、月曜始点でISO8600準拠になり、
2024/12/31はちゃんと「2025年の第1週」になるのだけれど。

Copilotに相談したら
「こういうカスタム関数にしたら？」と案の提示を受け、
何度か修正のやりとりをしたものの、なかなかの苦戦ちぅ。

2024/12/31が「2025年の第1週」になっても、
今度は2025/1/2(木)が「2025年の第2週」になってしまう。

そのまま年越ししてもうた。
396395
垢版 |
2025/01/01(水) 08:56:34.87
今のカスタム関数 fx_ISOWeekOfYear の暫定案：

AdjustedWeekNumber で4つの場合分けをして調整をかけているが、
どうしても2025/1/2(木)～5(日)が、「第2週」になってしまう・・・
ISOWeekNumber で切り捨ての丸め計算を入れているが、ここもこれで良いのかしら？

======
// ISO 8601週番号を計算する関数
(date as date) as number =>
let
// その年の最初の木曜日を取得
FirstThursday = Date.AddDays(Date.StartOfWeek(Date.StartOfYear(date), Day.Thursday), 3),
// その年の最初の木曜日を含む週の月曜日を取得
FirstISOWeekStart = Date.AddDays(FirstThursday, -3),
// 日付から年初の月曜日までの日数差を計算
DaysSinceFirstISOWeekStart = Duration.Days(Date.From(date) - FirstISOWeekStart),
// ISO 8601基準の週番号を計算
ISOWeekNumber = Number.RoundDown(DaysSinceFirstISOWeekStart / 7) + 1,

// 年末および年初の日付が適切な週に含まれるかどうかを判定
AdjustedWeekNumber =
if (Date.Month(date) = 1 and ISOWeekNumber > 52)
then 1
else if Date.Month(date) = 12 and ISOWeekNumber >= 52 and Date.DayOfWeek(Date.AddDays(date, 1), Day.Monday) <= 3
then 1
else if Date.Month(date) = 1 and Date.Day(date) <= 4 and Date.DayOfWeek(Date.StartOfWeek(date, Day.Monday), Day.Thursday) = 1
then Date.WeekOfYear(Date.AddDays(date, -7), Day.Monday) + 1
else ISOWeekNumber
in
AdjustedWeekNumber
=====
2025/01/01(水) 10:40:02.27
グーグルGeminiに聞いてみた

// 年月日を日付型に変換（必要であれば）
let date = #date(2024, 12, 31);

// その年の1月4日の曜日を数値で取得
let firstThursday = Date.DayOfWeek(#date(Year.From(date), 1, 4));

// 1月4日が木曜日より前の場合、前年の最終週となる
let weekNum = if firstThursday < 5 then
Date.WeekOfYear(#date(Year.From(date) - 1, 12, 31), Day.Monday)
else
// 1月4日が木曜日以降の場合、その週から週番号を計算
let adjustedDate = Date.AddDays(date, 5 - firstThursday); // 最初の木曜日へ調整
Date.WeekOfYear(adjustedDate, Day.Monday) + 1;
2025/01/01(水) 11:15:47.14
>>397
ありがとう。
まだ理解しきれていないけれど、
どうもCopilot案はGemini案と比べて
「その年の」の扱いが雑なような・・・
2025/01/01(水) 12:56:34.69
powerqueryに限らず日付処理はifで条件分岐し始めたら失敗と考えてる
2025/01/01(水) 13:17:42.68
>>399
閏年判定
401395
垢版 |
2025/01/02(木) 05:11:29.62
聞き方を改めてみた。

Q. ExcelのPower QueryにおけるM式言語にて、ある日付に対してISO8601に準拠した週番号を求めるカスタム関数を教えて下さい。
具体的には「その年の第1週は、1月4日を含む週である。」というものです。

===Copilotの再回答例===
(Date as date) as number =>
let
// 年の始まりの日付を取得
StartOfYear = Date.FromText(Text.From(Date.Year(Date)) & "-01-01"),
// 年の第4日を取得
FourthOfJanuary = Date.AddDays(StartOfYear, 3),
// 第4日が含まれる週の開始日（ISO週は月曜日開始）
StartOfFirstWeek = Date.StartOfWeek(FourthOfJanuary, Day.Monday),
// 指定された日付の週の開始日
StartOfCurrentWeek = Date.StartOfWeek(Date, Day.Monday),
// 第1週からの週番号を計算
WeekNumber = Number.RoundDown(Duration.Days(StartOfCurrentWeek - StartOfFirstWeek) / 7) + 1
in
WeekNumber
======
402395
垢版 |
2025/01/02(木) 05:12:25.96
===ChatGPIの回答例===
// ISO 8601準拠の週番号を計算するカスタム関数
(let
GetISOWeekNumber = (inputDate as date) as number =>
let
// その年の1月4日を含む週の開始日を取得
year = Date.Year(inputDate),
jan4 = #date(year, 1, 4),
firstThursdayOfYear = Date.AddDays(jan4, 3 - Date.DayOfWeek(jan4, Day.Monday)),
startOfWeek1 = Date.AddDays(firstThursdayOfYear, -3),

// 入力日付の週番号を計算
daysSinceStart = Duration.Days(inputDate - startOfWeek1),
weekNumber = Number.IntegerDivide(daysSinceStart, 7) + 1
in
weekNumber
in
GetISOWeekNumber)
======
403395
垢版 |
2025/01/02(木) 05:31:21.77
GPIにしてた...orz

これだと2024/12/30(月)、2024/12/31(火)等は
「2024年の第53週」となって、週カレンダーとしては年越しが分断されてしまうか・・・

「2025年の第1週」扱いにするよう、
もう一回聞き直してくる。
2025/01/02(木) 05:57:35.04
テストを先に組んだ方がいいんじゃないのか？
紀元前から1000年先まで、単純に総当たりするとか
405395
垢版 |
2025/01/02(木) 06:37:43.50
ChatGPTとも何度かやり取りして条件を再整理

1. ISO 8601準拠で
　・月曜基準
　・その年の第1週は、1月4日を含む週
2. (特注仕様) 年跨ぎで7日間が揃わない年末の数日は、翌年第1週として繰り上げ
　例：2024/12/30(月)、2024/12/31(火)などの場合
2025/01/02(木) 06:43:40.06
で、最終的に希望がかなったChatGPT回答例 (ご丁寧にlet in入れ子で、YearとWeek(週番号)の2つセットのrecordが出力する仕様なので、展開が必要)
======
// ISO 8601準拠で月曜日開始の週番号を計算するカスタム関数
let
GetISOWeekNumber = (inputDate as date) as record =>
let
// 入力日の年
year = Date.Year(inputDate),
// 入力日の属する週の判定用データ
jan4CurrentYear = #date(year, 1, 4),
firstMondayOfCurrentYear = Date.AddDays(jan4CurrentYear, -Date.DayOfWeek(jan4CurrentYear, Day.Monday)),
jan4NextYear = #date(year + 1, 1, 4),
firstMondayOfNextYear = Date.AddDays(jan4NextYear, -Date.DayOfWeek(jan4NextYear, Day.Monday)),
// 前年の第1週開始日を計算
jan4PreviousYear = #date(year - 1, 1, 4),
firstMondayOfPreviousYear = Date.AddDays(jan4PreviousYear, -Date.DayOfWeek(jan4PreviousYear, Day.Monday)),
// 週番号を計算するための基準年の決定
adjustedYear =
if inputDate >= firstMondayOfNextYear then year + 1
else if inputDate < firstMondayOfCurrentYear then year - 1
else year,
// 基準年に応じた1月4日と第1週の開始日を取得
adjustedJan4 = #date(adjustedYear, 1, 4),
adjustedFirstMonday = Date.AddDays(adjustedJan4, -Date.DayOfWeek(adjustedJan4, Day.Monday)),
// 週番号を計算
daysSinceStart = Duration.Days(inputDate - adjustedFirstMonday),
weekNumber = Number.IntegerDivide(daysSinceStart, 7) + 1
in
[Year = adjustedYear, Week = weekNumber]
in
GetISOWeekNumber
======
2025/02/16(日) 13:04:55.37
クエリーで累計計算は鬼門
408名無しさん＠そうだ選挙にいこう
垢版 |
2025/04/29(火) 07:46:04.53
VBAで一通りの事ができる様になったらPowerQueryがめちゃくちゃ遅い事に気がついてしまった
2025/05/09(金) 09:52:48.46
それだわ
新着レスの表示
レスを投稿する

5ちゃんねるの広告が気に入らない場合は、こちらをクリックしてください。
ニューススポーツなんでも実況