【Excel】Power Queryを語るスレ【Power BI】 (356ﾚｽ)
上下前次1-新
257(1): 2023/04/30(日)21:17 AAS
初歩的な質問になるとは思いますが
ブックを読み込んだ時に10万行も読み込まれてしまう時があります
これは一番下の行まで0か何かしらの値が入力されているからなのでしょうか？
258(1): 2023/04/30(日)22:21 AAS
>>257
元ブックの、テーブルでなくてワークシートを読込んでいるの？
元ブックのワークシートで、本来読込みたい行数は何行？
元ブックの当該ワークシート開いて、Ctrl+Endで最右下行に移動したら、何行目まで行く？
259(1): 2023/05/04(木)15:37 AAS
>>258
なるほど
テーブルではなくブックやワークシートそのものを読み込んだ場合に何もない空白の行が読み込まれてしまう場合があるという事ですね
それは盲点でした
クエリのマージに頼るよりもリレーションシップで出来る事は極力（最大限に）リレーションシップで済ませた方が動作は軽いですか？
260(1): 2023/05/04(木)15:59 AAS
powerqueryでリレーションシップて何？
261: 2023/05/04(木)16:02 AAS
>>260
ピボットテーブル（？）でしたね！
262(1): 2023/05/04(木)19:20 AAS
全てのデータがデータベースに（一行、1レコード）揃っている状態が正規化されていない状態、あるいは第一正規化で
これ以上ないくらいに重複を排除した、それぞれ別のデータベースにしたのが第三正規化ですか？
263(1): 2023/05/04(木)20:14 AAS
>>259
実データが数万行程度までなら、
ピボットテーブル（PowerPivot）でのリレーションでも
PowerQueryのマージでも速さは同じぐらいの印象。
104万行超とかの大きなデータを扱うんだったら、リレーションの方が速いか？
ようつべで比較動画上がってたような？
ただ、ピボットテーブルにすると、その後はグラフにするしかなく、
ピボットテーブルのまとめ方が使えそうだったら、最後の段階に使っている。
まだ加工や二次利用が続くんだったらPowerQueryにしている。
264: 2023/05/04(木)21:26 AAS
>>263
そうですよね
リレーションシップはピボットテーブルで活用するしか使い道はないですよね（？）
結合に比べると自由度が低い（？）
265(1): 2023/05/04(木)21:41 AAS
>>262
第一正規形は繰り返し項目の排除
つまりフィールドの数が同じ形に整えたもの
第三正規形は大体あってる
266: 2023/05/04(木)22:00 AAS
>>265
ありがとうございます
267(1): 2023/05/04(木)23:04 AAS
第三正規化はこれ以上ないくらいに細分化している
第二正規化は複数の状況証拠（条件）があるから推移的関数従属が決定する
第一正規化はデータベース
ってコト、、、？
268(1): 2023/05/05(金)21:35 AAS
>>267
第一正規化はフィールドを定義するために必要
買い物した人のレコードに商品1、商品2、商品3、…って無限にフィールドを作るのを防ぐ
第二正規化で重複データを分離
経理システムかなにかのCMでやってる、何度も同じデータを入力する必要がなくなる
第三正規化し第三正規形にすることでマスタデータとトランザクションデータを完全に分離できる
非正規形含む正規形はすべてデータベース
今は第六正規形まで定義されてる
269: 2023/05/06(土)08:30 AAS
>>268
ありがとうございます
今までデータベースなんて考えた事はあまりなかったです
270(1): 2023/05/12(金)07:31 AAS
二つの表があって変更された行を表示させるのはやはりパワークエリでしょうか？
271(1): 2023/05/12(金)11:11 AAS
>>270
数百行程度なら、チェックする列数にもよるけど、ワークシート関数でも可能かと。
ただ、基準表と比較対象表をいろいろ入れ替えるんだったら、
ちと面倒かも。
行数が万単位なら、PowerQueryかな。
同じ表形式なら、基準表と比較対象表の入れ替えも楽だし。
20列とかの全列チェックするんだったら、
クエリー更新もそれなりに時間かかると思う。
272: 2023/05/12(金)12:45 AAS
>>271
ありがとうございます
273: 2023/05/17(水)18:14 AAS
https:linmoa.net
https://oraksil.cc/
https://todaync.com/
Microsoft Jpan destroyer
274: 2023/05/18(木)23:07 AAS
共通の処理まで進めて処理を分岐させようと思います。
そのときに、「複製」を使って処理を分岐させるのと
コピーを使って処理を分岐させるのでは複製の場合はバグが発生しますか？
参照だと分岐元に変更を加えない限りはエラーが発生しませんか？
275: 2023/05/19(金)21:31 AAS
パワークエリで消費メモリを削減するためにはどの様なポイントがありますか？
276(1): 2023/05/20(土)10:04 AAS
ステップの早い段階で不要な列や行を削除する
277: 2023/05/20(土)12:07 AAS
>>276
ありがとうございます
278: 2023/05/20(土)12:42 AAS
ちなみになのですが、たった3000行未満の表をList.Containsでフィルターしたり、マージしたりした後に2つ複製をして複製したクエリをそれぞれステップを書き加えてもエラーは起きませんよね？
279(1): 2023/05/20(土)14:40 AAS
複製？参照じゃなくて？
280: 2023/05/20(土)15:47 AAS
>>279
はい
複製です
私は複製のことをコピーそのものだと勘違いしておりました
複製では依存先（？）依存関係にあるクエリまで複製されないので複製したクエリを実行しても動かないのですね
参照で別のクエリを作成し（わかりにくい表現かもしれませんが）作成したクエリに追加の作業を行うと参照元のクエリが書き換わる、変更されるものだと勘違いしておりました
複製ではなくコピーの方がいいのですね
281(1): 2023/05/20(土)21:02 AAS
データソースであるexcelファイルを参照してるんだけど
上書きしても同じファイル名なら更新すれば読み込んでくれる？
282: 2023/05/24(水)06:51 AAS
>>281
参照する中身の構造が同じなら、
個別セルの値が変わっていても
レコード数に増減があっても
読み込むけど。
283(1): 2023/05/25(木)18:43 AAS
フォルダからファイルを接続して読み込んでいましたが不要になったので該当するクエリを削除し、フォルダを削除しました
しかしクエリを更新する時にファイルがありませんとエラーメッセージが表示されます
どうしたらいいでしょうか？
284(1): 2023/05/25(木)20:55 AAS
>>283
もしかして、そのクエリー、
「読込み先」の時に、ワークシートにテーブルとして読込んだだけでなくて、
「このデータをデータ モデルに追加する」にもチェック入れて読込んでない？
なので、テーブルとクエリーを削除しても「接続」が残っているのかと。
よく分らないけど、
メニューバーの「データ」から「クエリと接続」の右ペインで、
「接続」のタブに何か残っているようだったらそれを削除、
あるいは、メニューバーに「Power Pivot」が出るんだったら、
そこの「管理」開いて、何らかのスプレッドシートみたいなのがあれば、タブで削除、
省1
285: 2023/05/25(木)21:30 AAS
>>284
ありがとうございます
データモデルに追加する
にチェックを入れると動作が軽くなるとか早くなると聞いていたので何でもかんでも追加していました
286: 2023/05/25(木)21:49 AAS
ファイルをもう一度用意する
287: 2023/05/25(木)22:24 AAS
頻繁にメモリ不足エラーを吐かれるんだけど一体なんなんだろうか？
私がデータモデルに追加しているからなんだろうか、それともデータソースを追加して、そのまま編集して、そこから次に読み込むで接続オンリーにしているからなんだろうか？
288: 2023/05/25(木)22:28 AAS
クエリのコピーや参照もいいですが、
ファイルそのものをコピーして分けて作成するのもいいですね
289: 2023/05/26(金)16:42 AAS
「データモデル」と「ワークシートにテーブル」の両方読込むと、
メモリーの制約からか、ワークシート側のソート、
特に複数列ソートが思い通りにならなくなったりしない？
104万行以上もワークシートには当然読み込めないし。
なんとなく、
・リレーションシップやDAX関数使わないんだったら、「データモデル」には追加しない。
・使うんだったら接続のみ（それでもソースとして参照したクエリーはワークシートにテーブルとして落とせたような・・・）
にしてるんだけど。
290: 2023/05/26(金)21:32 AAS
ワークシートに出力しているクエリをデータモデルに追加するとバグを起こす可能性があるのですね
291(1): 2023/05/29(月)19:49 AAS
サーバーから吐き出されるCSVのファイル名を変えられちゃったんだけど
powerqueryのクエリで違うファイル名に変更できる？
292: 2023/05/29(月)20:19 AAS
できる
293: 2023/05/29(月)22:43 AAS
パス変えるのと同じ
294: 2023/05/30(火)01:30 AAS
サンキュー
調べてみる
295: 2023/05/30(火)22:37 AAS
>>291
中身自体が違えば難しいのでは
296: 2023/05/30(火)23:56 AAS
カラムが違ったら無理だわなｗ
297(2): 2023/06/01(木)20:08 AAS
欧州の電力需給実績リアルタイム15分値のチャートがWeb公開されているんだけど、
https://energy-charts.info/charts/power/chart.htm?l=en&c=DE&stacking=stacked_absolute_area
これってPower BIベースなのだろうか？
日本の電力需給1時間値でマネして、
「折れ線グラフおよび積上げ棒グラフ」で再現できないかやってみた。
が、
1時間値の積上げ棒グラフの幅が、
最初はこのくらい狭く表示できたんだけど、
何かの拍子に広がってしもうた。
視覚化の「列」が積み上げ棒グラフのプロパティっぽいんだが、
省3
298(1): 2023/06/01(木)20:10 AAS
パワークエリでフォルダからブックを読み込む時にシート2のみ取り込む方法はありますか？
299: 297 2023/06/01(木)20:25 AAS
ベースはPower BIではなく
これとか
https://www.highcharts.com/
これら
https://d3js.org/
みたいだった。
失礼。
にしても、Power BIで近づけることはできないかしら？
300(1): 2023/06/01(木)20:36 AAS
>>298
目的のフォルダ指定してPower Queryエディター開いたら、
ステップを最初の「ソース」だけ残す。
kind列でsheetだけ絞るフィルターかけて
Item列にsheet名が出てるはずだから
またフィルターでそれだけ絞る。
1行だけになったらData列だけ残して他の列を削除。
中の緑色の文字"Table"をクリックすれば、
目的のsheetが開く。
そのステップも自動で追加される。
省2
301: 2023/06/01(木)20:41 AAS
>>300
ありがとうございます😊
Bing君に聞いても上手く答えを出してくれなくて
302: 2023/06/01(木)22:52 AAS
テンプレみたいなのがあるサイトない？
303(1): 2023/06/03(土)06:09 AAS
完全外部結合させた時にキー列のnullに右部のキー列を入れるにはどうしたらいいでしょうか？
完全外部結合と論理和は何が違うのでしょうか？
Bing AIくんお尋ねしたら完全外部結合は表を結合させるもので、論理和はAUBだから概念は違うけどどちらも全てという意味では似てると言われました
304: 2023/06/03(土)08:17 AAS
マージする前の右と左のクエリーの、
それぞれの列はどうなっている？
305(1): 2023/06/03(土)10:00 AAS
>>303
条件列作って、[キー列]がnullなら[右のキー列]、それ以外は[キー列]
キー列を削除
作った条件列をキー列の名前に変更
M言語を直接編集する気があるならキー列を直接変更する方法はあるよ
306(1): 2023/06/03(土)17:51 AAS
>>305
M言語？はちょくちょく触らせてもらっています
それ自体は簡単そうなので気になりますね
話が変わりますがテーブルに読み込んでいるクエリだけ（？）データモデルに追加するを止めるようにしたらあれだけ出ていたメモリ不足エラーやその他のエラーがぱったり消えました
やはりバグなんですね
307(1): 2023/06/03(土)18:25 AAS
>>306
= Table.ReplaceValue(前のステップ, each [キー列],each if [キー列] = null then [右のキー列] else [キー列],Replacer.ReplaceValue,{"キー列"})
308: 2023/06/03(土)18:32 AAS
あれ？これで十分なのかな？
= Table.ReplaceValue(前のステップ, null, [右のキー列],Replacer.ReplaceValue,{"キー列"})
試してみて
309: 2023/06/03(土)18:32 AAS
一つ聞きたいのですが
パワークエリで横に長いテーブルを読み込み編集します
そのシートの下でも上でもいいので集計行を追加する事は可能でしょうか？
次にそのクエリを参照したクエリで行列入れ替えを行い、見出し列を行に並び替えます
見出し行の隣に参照元の集計行を行列入れ替えで追加できますか？
310: 2023/06/03(土)18:41 AAS
5ちゃん GTPもBing AIも凄いなぁ
311(1): 2023/06/03(土)19:36 AAS
ちなみになのですが昨日サイトで完全外部結合した後にキー列をカスタム関数のifで結合すればいいという事のを見て自分で実践してみましたが＝が二つになってしまい、なんとかトークンが必要ですというエラーが出てきました
あれは何が問題だったのでしょうか？
BingAIに聞けばよかったですね
312: 2023/06/03(土)22:24 AAS
>>307
eachが必要
= Table.ReplaceValue(前のステップ, null, each [右のキー列],Replacer.ReplaceValue,{"キー列"})
313(1): 2023/06/03(土)22:48 AAS
>>311
「トークンが必要」というエラーは
詳細エディターで手入力でM言語式を書いたとき、
うっかり間違える「前のステップ」名。
でなきゃ、途中ステップの最後に "," を忘れたか、
最終ステップ（in前）の最後の記述に余計な "," を入れた
だったか。
314: 2023/06/03(土)23:22 AAS
>>313
そうだったのですね
ありがとうございます
まだまだパワークエリのM言語は触り始めたばかりでルールを知りませんでした
315: 2023/06/04(日)13:13 AAS
パワークエリのグループ化の集計方法は何が違うのでしょうか？
316: 2023/06/05(月)12:30 AAS
B列からH列まであって
それぞれの列の値は被らないとします
この場合B列からH列までを一つの列にまとめる事は可能でしょうか？
317: 2023/06/05(月)18:50 AAS
データモデルに追加はしない方がよさそうだね
なんでクライアントのメモリ食ってんの？
318(1): 2023/06/05(月)19:09 AAS
データモデル(PowerPivot)はデータをブックに読み込んでしまう
ファイルサイズ見ればわかるよ
PowerPivotの編集画面で表示されるデータがそのままブックに保存されてる
PowerQueryだけならそうならない
319: 2023/06/05(月)21:44 AAS
>>318
データモデルに追加はよく省メモリとか軽量化のための手段として紹介されていますが
全くの出鱈目だったんですね
320(2): 2023/06/06(火)06:57 AAS
データモデルは、
ワークシート限界の104万行以上を扱える。
Power Pivotと併用すると、計算が速い。
その代わり、データが大きいほどメモリーは食う。
321: 2023/06/06(火)07:34 AAS
>>320
リレーションシップだから処理が軽く早いんだと聞いていましたが違ったのですね
322: 320 2023/06/06(火)10:15 AAS
たとえば、>>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行。
省15
323: 2023/06/06(火)16:36 AAS
データモデルに入れちゃったテーブルを外せる？
324(1): 2023/06/06(火)17:13 AAS
外せるよ。
「クエリと接続」の右ペイン出して、
目的のクエリー右クリックして「読込み先」、
「このデータをデータモデルに追加する」のチェックを外す
だったか。
Power Pivot開いて目的のクエリーのタブ右クリックして削除でも行けたかな？
325: 2023/06/06(火)17:42 AAS
>>324
ありがと！
326: 2023/06/06(火)20:01 AAS
パワーピボットで3種類の値の入った列を複数列、行フィールドに入れてフィルターをかけたらどうなりますか？
ANDでフィルターをかけられた結果が表示されますか？
327(1): 2023/06/07(水)01:20 AAS
何をどうしたいのかさっぱり分らんけど、
まず3種類の値を3列に分ける方が先なんでねーの？
328(1): 2023/06/07(水)07:18 AAS
>>327
やはり一つの列には一つの値しか存在してはいけないのですね
私もア以外の値をなくすことによってピボットテーブルで集計する事ができました
https://i.imgur.com/bTwA4tg.jpg
https://i.imgur.com/z7yJTK3.jpg
この様に列を並べて一つのピボットテーブルで集計したいです
329(1): 2023/06/07(水)08:26 AAS
>>328
「一つの列に一つの値」は基本中の基本、イロハの「イ」やで。
それと、他人に説明するとき、示す文章と絵を一致させるのも、イロハの「イ」や。
よーく、覚えとけ。
330(1): 2023/06/07(水)08:45 AAS
>>329
一つの列に一つの値が基本中の基本なので一つの列しか並べられない
という事はわかりました
ではなぜこれがア イ ウの値のある列ではなく、アしかない複数の列を並べる場合だと上手く機能するのでしょうか？
331(1): 2023/06/08(木)21:58 AAS
ピボットテーブルにもデータモデルに追加すると動作が重くなる とか メモリ不足等のエラーメッセージが出る という事はありますか？
332: 2023/06/08(木)22:08 AAS
データモデルとワークシートの両方に読込むと
おかしくなる時がある
って話でねーの？
333: 2023/06/08(木)22:09 AAS
>>330
この馬鹿はあちこちで聞き回って聞かれたことに答えもしないクズ
334: 2023/06/09(金)00:00 AAS
かまってちゃんはお断り
335: 2023/06/09(金)20:55 AAS
>>331
データの格納効率(圧縮率？)はワークシートよりデータモデルの方が良いらしい
でもその処理で余計にCPUやメモリは消費するかもしれないね
336: 2023/06/11(日)10:42 AAS
列にフィルターを掛けます
フィルターされた のステップが挿入されます
このステップのコードをコピーすれば何回でも流用できますか？
337: 2023/06/14(水)22:38 AAS
SQLの基本を解説する本を読んでいてパワークエリに通じるものを感じます
つまりそもそもSQLを覚えましょうという事なのでしょうか？
338: 2023/06/14(水)22:40 AAS
どゆこと
339: 2023/06/15(木)01:16 AAS
ギンガイザーガムでも食ってろという意味では?
340: 2023/06/15(木)21:07 AAS
パワークエリはSQLでできる事をさもすごい新機能の様に言っているだけでしょうか？
341: 2023/06/16(金)04:39 AAS
M言語とSQLが同じに見えるかわいそうな子
SQLのQがなんだか分かるようになるといいね
342: 2023/06/16(金)09:03 AAS
データベースの知識はあって損ないわな
343: 2023/06/16(金)10:05 AAS
むしろコレ扱う人ってSQLくらい分かるでしょう
344: 2023/06/16(金)12:14 AAS
Pythonを少し触りはじめた人がExcelでは上手く作れない複雑な計算（数学？）のグラフがPythonだったら簡単に作れると驚いていたのですが、それはデータベースとかライブラリだからだとしたらそういう事だったのかという感じ
345: 2023/06/18(日)10:08 AAS
まだこのスレあった！よかったー
346: 2023/06/19(月)22:48 AAS
パワークエリでPythonを動かす人もいるのでしょう？
347: 2023/08/04(金)09:28 AAS
リレーション先のテーブルにある別カラムを条件に、棒グラフの1本だけを色変えたりできないかな？
348: 2023/08/04(金)12:22 AAS
ん？
どういう集計のどういうグラフか、詳細が分らないが、
条件使ってPowerQueryか関数で別系列に出来るなら、
色付け自体は固定的にグラフの設定
棒グラフじゃないけど、例えば、
最大値を別の色のマーカーにしたいとき、
最大値の系列を別に作って、他データを#N/Aにして
その1点だけ、あたかも別系列で重ねて表示させる、
なんてことはよくやる。
系列名が動的に変わるならVBA
省1
349: 2023/08/23(水)09:28 AAS
やっぱり、出たよ
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 AAS
List.TransformManyの存在理由が納得できるような使い方の例を示してください。
351: 2023/10/02(月)03:26 AAS
pdf表のデータ・スクレイピングは難儀だけど、
例えば、毎月発表される東電パワーグリッドの再エネ接続量のこのファイル、
https://www.tepco.co.jp/pg/consignment/system/pdf/newenergy_hondo_backnumber.pdf
これから%の表は抜きで
電源種別、申込ステータス、年月ごとの容量(万kW)をリスト化にするには、
・ページ番号・行番号・列番号使って、
・いったん時系列・項目・データ部に分け、
・番号を頼りにマージで再合成
すれば、PowerQueryでも出来んのね。
352: 02/06(火)00:00 AAS
Power BIで使える
Web.BrowserContents
は、ExcelのPower Quertでは使えないのか？
https://learn.microsoft.com/ja-jp/powerquery-m/web-browsercontents
353: 02/29(木)16:59 AAS
Webデータを読み込むクエリーで
ステップを重ねていって、途中でエラーになったとき（例：該当するデータがない等）、
その後のステップを飛ばして(if then elseでやるか・・・)
データがないのテーブルを#tableで生成して終わるような処理は
出来るかな？
354: 02/29(木)17:13 AAS
あ、出来そうだわ
355(1): 03/08(金)15:47 AAS
初歩的な質問かもしれんのですが、この動画https://youtu.be/tFrjr3IiIlM?si=nYS3GRwPgYwMprmO 見ながら勉強しててドリルダウンまで行ったんだけど何故か綺麗に左からGDP順でドリルダウにならず…国名の五十音順になってしまうのって何故だと思う？
356: 03/08(金)16:05 AAS
>>355
軸の並び替えで対処できたわ
上下前次1-新書関写板覧索設栞歴
ｽﾚ情報 赤ﾚｽ抽出 画像ﾚｽ抽出 歴の未読ｽﾚ AAｻﾑﾈｲﾙ
ぬこの手 ぬこTOP 0.904s*