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

1 ：名無しさん＠そうだ選挙にいこう：2019/10/20(日) 11:59:28.17 .net

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/

307 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 18:25:50.26 .net

>>306
= Table.ReplaceValue(前のステップ, each [キー列],each if [キー列] = null then [右のキー列] else [キー列],Replacer.ReplaceValue,{"キー列"})

308 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 18:32:00.29 .net

あれ？これで十分なのかな？
= Table.ReplaceValue(前のステップ, null, [右のキー列],Replacer.ReplaceValue,{"キー列"})
試してみて

309 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 18:32:02.67 .net

一つ聞きたいのですが
パワークエリで横に長いテーブルを読み込み編集します
そのシートの下でも上でもいいので集計行を追加する事は可能でしょうか？


次にそのクエリを参照したクエリで行列入れ替えを行い、見出し列を行に並び替えます
見出し行の隣に参照元の集計行を行列入れ替えで追加できますか？

310 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 18:41:24.85 .net

5ちゃん　GTPもBing AIも凄いなぁ

311 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 19:36:50.80 .net

ちなみになのですが昨日サイトで完全外部結合した後にキー列をカスタム関数のifで結合すればいいという事のを見て自分で実践してみましたが＝が二つになってしまい、なんとかトークンが必要ですというエラーが出てきました
あれは何が問題だったのでしょうか？
BingAIに聞けばよかったですね

312 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 22:24:43.95 .net

>>307
eachが必要
= Table.ReplaceValue(前のステップ, null, each [右のキー列],Replacer.ReplaceValue,{"キー列"})

313 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 22:48:35.75 .net

>>311
「トークンが必要」というエラーは
詳細エディターで手入力でM言語式を書いたとき、
うっかり間違える「前のステップ」名。

でなきゃ、途中ステップの最後に "," を忘れたか、
最終ステップ（in前）の最後の記述に余計な "," を入れた
だったか。

314 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 23:22:22.76 .net

>>313
そうだったのですね
ありがとうございます
まだまだパワークエリのM言語は触り始めたばかりでルールを知りませんでした

315 ：名無しさん＠そうだ選挙にいこう：2023/06/04(日) 13:13:19.77 .net

パワークエリのグループ化の集計方法は何が違うのでしょうか？

316 ：名無しさん＠そうだ選挙にいこう：2023/06/05(月) 12:30:31.25 .net

B列からH列まであって
それぞれの列の値は被らないとします
この場合B列からH列までを一つの列にまとめる事は可能でしょうか？

317 ：名無しさん＠そうだ選挙にいこう：2023/06/05(月) 18:50:38.07 .net

データモデルに追加はしない方がよさそうだね
なんでクライアントのメモリ食ってんの？

318 ：名無しさん＠そうだ選挙にいこう：2023/06/05(月) 19:09:10.88 .net

データモデル(PowerPivot)はデータをブックに読み込んでしまう
ファイルサイズ見ればわかるよ
PowerPivotの編集画面で表示されるデータがそのままブックに保存されてる
PowerQueryだけならそうならない

319 ：名無しさん＠そうだ選挙にいこう：2023/06/05(月) 21:44:11.31 .net

>>318
データモデルに追加はよく省メモリとか軽量化のための手段として紹介されていますが
全くの出鱈目だったんですね

320 ：名無しさん＠そうだ選挙にいこう：2023/06/06(火) 06:57:57.49 .net

データモデルは、
ワークシート限界の104万行以上を扱える。
Power Pivotと併用すると、計算が速い。
その代わり、データが大きいほどメモリーは食う。

321 ：名無しさん＠そうだ選挙にいこう：2023/06/06(火) 07:34:36.14 .net

>>320
リレーションシップだから処理が軽く早いんだと聞いていましたが違ったのですね

322 ：320：2023/06/06(火) 10:15:46.29 .net

たとえば、>>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

323 ：名無しさん＠そうだ選挙にいこう：2023/06/06(火) 16:36:36.54 .net

データモデルに入れちゃったテーブルを外せる？

324 ：名無しさん＠そうだ選挙にいこう：2023/06/06(火) 17:13:19.88 .net

外せるよ。

「クエリと接続」の右ペイン出して、
目的のクエリー右クリックして「読込み先」、
「このデータをデータモデルに追加する」のチェックを外す
だったか。
Power Pivot開いて目的のクエリーのタブ右クリックして削除でも行けたかな？

325 ：名無しさん＠そうだ選挙にいこう：2023/06/06(火) 17:42:40.53 .net

>>324
ありがと！

326 ：名無しさん＠そうだ選挙にいこう：2023/06/06(火) 20:01:51.66 .net

パワーピボットで3種類の値の入った列を複数列、行フィールドに入れてフィルターをかけたらどうなりますか？
ANDでフィルターをかけられた結果が表示されますか？

327 ：名無しさん＠そうだ選挙にいこう：2023/06/07(水) 01:20:58.20 .net

何をどうしたいのかさっぱり分らんけど、
まず3種類の値を3列に分ける方が先なんでねーの？

328 ：名無しさん＠そうだ選挙にいこう：2023/06/07(水) 07:18:28.24 .net

>>327
やはり一つの列には一つの値しか存在してはいけないのですね
私もア以外の値をなくすことによってピボットテーブルで集計する事ができました

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


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

この様に列を並べて一つのピボットテーブルで集計したいです

329 ：名無しさん＠そうだ選挙にいこう：2023/06/07(水) 08:26:02.56 .net

>>328
「一つの列に一つの値」は基本中の基本、イロハの「イ」やで。
それと、他人に説明するとき、示す文章と絵を一致させるのも、イロハの「イ」や。

よーく、覚えとけ。

330 ：名無しさん＠そうだ選挙にいこう：2023/06/07(水) 08:45:36.27 .net

>>329
一つの列に一つの値が基本中の基本なので一つの列しか並べられない
という事はわかりました

ではなぜこれがア　イ　ウの値のある列ではなく、アしかない複数の列を並べる場合だと上手く機能するのでしょうか？

331 ：名無しさん＠そうだ選挙にいこう：2023/06/08(木) 21:58:54.72 .net

ピボットテーブルにもデータモデルに追加すると動作が重くなる　とか　メモリ不足等のエラーメッセージが出る　という事はありますか？

332 ：名無しさん＠そうだ選挙にいこう：2023/06/08(木) 22:08:42.54 .net

データモデルとワークシートの両方に読込むと
おかしくなる時がある
って話でねーの？

333 ：名無しさん＠そうだ選挙にいこう：2023/06/08(木) 22:09:20.85 .net

>>330
この馬鹿はあちこちで聞き回って聞かれたことに答えもしないクズ

334 ：名無しさん＠そうだ選挙にいこう：2023/06/09(金) 00:00:23.22 .net

かまってちゃんはお断り

335 ：名無しさん＠そうだ選挙にいこう：2023/06/09(金) 20:55:11.42 .net

>>331
データの格納効率(圧縮率？)はワークシートよりデータモデルの方が良いらしい
でもその処理で余計にCPUやメモリは消費するかもしれないね

336 ：名無しさん＠そうだ選挙にいこう：2023/06/11(日) 10:42:42.93 .net

列にフィルターを掛けます
フィルターされた　のステップが挿入されます
このステップのコードをコピーすれば何回でも流用できますか？

337 ：名無しさん＠そうだ選挙にいこう：2023/06/14(水) 22:38:53.61 .net

SQLの基本を解説する本を読んでいてパワークエリに通じるものを感じます
つまりそもそもSQLを覚えましょうという事なのでしょうか？

338 ：名無しさん＠そうだ選挙にいこう：2023/06/14(水) 22:40:42.75 .net

どゆこと

339 ：名無しさん＠そうだ選挙にいこう：2023/06/15(木) 01:16:22.70 .net

ギンガイザーガムでも食ってろという意味では?

340 ：名無しさん＠そうだ選挙にいこう：2023/06/15(木) 21:07:17.33 .net

パワークエリはSQLでできる事をさもすごい新機能の様に言っているだけでしょうか？

341 ：名無しさん＠そうだ選挙にいこう：2023/06/16(金) 04:39:41.55 .net

M言語とSQLが同じに見えるかわいそうな子
SQLのQがなんだか分かるようになるといいね

342 ：名無しさん＠そうだ選挙にいこう：2023/06/16(金) 09:03:52.63 .net

データベースの知識はあって損ないわな

343 ：名無しさん＠そうだ選挙にいこう：2023/06/16(金) 10:05:08.67 .net

むしろコレ扱う人ってSQLくらい分かるでしょう

344 ：名無しさん＠そうだ選挙にいこう：2023/06/16(金) 12:14:32.96.net

Pythonを少し触りはじめた人がExcelでは上手く作れない複雑な計算（数学？）のグラフがPythonだったら簡単に作れると驚いていたのですが、それはデータベースとかライブラリだからだとしたらそういう事だったのかという感じ

345 ：名無しさん＠そうだ選挙にいこう：2023/06/18(日) 10:08:57.13.net

まだこのスレあった！よかったー

346 ：名無しさん＠そうだ選挙にいこう：2023/06/19(月) 22:48:24.89.net

パワークエリでPythonを動かす人もいるのでしょう？

347 ：名無しさん＠そうだ選挙にいこう：2023/08/04(金) 09:28:17.84.net

リレーション先のテーブルにある別カラムを条件に、棒グラフの1本だけを色変えたりできないかな？

348 ：名無しさん＠そうだ選挙にいこう：2023/08/04(金) 12:22:46.80.net

ん？
どういう集計のどういうグラフか、詳細が分らないが、

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

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

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

とか。

349 ：名無しさん＠そうだ選挙にいこう：2023/08/23(水) 09:28:23.14 .net

やっぱり、出たよ

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

350 ：名無しさん＠そうだ選挙にいこう：2023/09/26(火) 06:21:44.76 .net

List.TransformManyの存在理由が納得できるような使い方の例を示してください。

351 ：名無しさん＠そうだ選挙にいこう：2023/10/02(月) 03:26:16.21 .net

pdf表のデータ・スクレイピングは難儀だけど、
例えば、毎月発表される東電パワーグリッドの再エネ接続量のこのファイル、
https://www.tepco.co.jp/pg/consignment/system/pdf/newenergy_hondo_backnumber.pdf

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

352 ：名無しさん＠そうだ選挙にいこう：2024/02/06(火) 00:00:20.41 .net

Power BIで使える
Web.BrowserContents
は、ExcelのPower Quertでは使えないのか？

https://learn.microsoft.com/ja-jp/powerquery-m/web-browsercontents

353 ：名無しさん＠そうだ選挙にいこう：2024/02/29(木) 16:59:07.69 .net

Webデータを読み込むクエリーで
ステップを重ねていって、途中でエラーになったとき（例：該当するデータがない等）、
その後のステップを飛ばして(if then elseでやるか・・・)
データがないのテーブルを#tableで生成して終わるような処理は
出来るかな？

354 ：名無しさん＠そうだ選挙にいこう：2024/02/29(木) 17:13:55.05 .net

あ、出来そうだわ

355 ：名無しさん＠そうだ選挙にいこう：2024/03/08(金) 15:47:22.05 .net

初歩的な質問かもしれんのですが、この動画https://youtu.be/tFrjr3IiIlM?si=nYS3GRwPgYwMprmO　見ながら勉強しててドリルダウンまで行ったんだけど何故か綺麗に左からGDP順でドリルダウにならず…国名の五十音順になってしまうのって何故だと思う？

356 ：名無しさん＠そうだ選挙にいこう：2024/03/08(金) 16:05:36.54 .net

>>355
軸の並び替えで対処できたわ