【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
軸の並び替えで対処できたわ
115 KB
新着レスの表示
掲示板に戻る
全部
前100
次100
最新50
read.cgi ver 2014.07.20.01.SC 2014/07/20 D ★