【Excel】Power Queryを語るスレ【Power BI】
>>195
自己解決した
Table.AddColumn(ソース, "新列名", each Record.Field(_, "旧列名")*1000)
>>197
列名の変更は {"旧列名", "新列名"} で両方とも文字列で指定だから問題の質が異なる
テーブルから列名検索したいなら
探した列名=List.Select(Table.ColumnNames(前の処理), each 検索条件)){0} //見つかった最初のもの
で得た列目使って {探した列名, "新列名"} とかにすればいい >>198
ん？
よく分からんけど、それで「旧列名」の部分が変数扱いになったの？
Record.Field関数の2番目の引数はレコードの中の特定の列名だから、
思い切り定数で"旧列名"と指定していることになってるのかと。
1番目の引数のアンダースコアはeachとセットみたいだけど、
レコードの代名詞みたいになっているのかしら？
列名を変数にするって例えば、
column1、Column2、column3・・・
と元データを読込むたびに列が増えるような場合で、
最後の列が合計かなんかで、
それを1000倍にする列をさらに追加したい時、
元データの、変化する最後の列名を変数で扱いたいのかと思ってた。 Power BIのテーブルで、列名を縦書きにできませんか？ ピボット解除じゃなくて、まさか1セルの中での縦書き？ 列名を文字のリストに分割してから改行文字を間に挟んで結合する
M言語の関数でできる
得られた文字列で列名変更 Power Queryは書式設定には使えませんか？ 連続した1時間ごとのデータがあって、
ある閾値を下回った連続時間帯が年間で最大何コマだったのかを調べたい。
（例えば、風力発電の年間出力データとか、気温の年間データとか）
下回った時間帯のフラグ列作って、
グループ化してその中でインデックス振って、
List.Sum(List.FirstN())で累計を取ろうかと思ったが、なんか上手く行かない。
List.Accumulateで累計するにも、
グループ化とインデックス、List.FirstN()は要るよね？
List.Accumulateの公式説明がよく分からない。
助けて。 PowerBI初心者です。表示したいグラフを表示する方法にたどり着けないので教えて下さい。 表示したいグラフは意味的にはソフトウェア開発における不具合検出数のグラフです。X軸は日付、Y軸は検出した不具合の数の累計と解決した不具合の数の累計です。 不具合は課題管理システムで管理していますが、PowerBIへの入力は課題管理システムからエクスポートした不具合一覧です。1行が1件の不具合を表します。1行を構成する列のうち、不具合の連番、不具合の状態(解決済か未解決か)を元データに不具合の累計件数と解決済み不具合の累計件数を時系列に表示させたいです。 X軸に不具合報告の作成日を指定し、Y軸にクイックメジャーのタイムインテリジェンスから何か選ぼうとしましたが、一番期間が長い選択肢でも年度累計までです。年度累計を選ぶと年度の変わり目で累計件数が0に戻ってしまいます。表示させたい事は年度関係なしの累計です。 要件を書き込むとソリューションが出てくるスレです。 過年度最終累計を別のクエリーに蓄えといて、
過年度累計＋当年度日次データ
にしとけばいいだけの話でねぇーの？ 別に104万行超えるわけでねぇーべ？
全期間のファイル、クエリーで読込んで結合したらよろし。
1つのフォルダにファイル突っ込んどいて
フォルダ指定で全部結合する方法は
あっちこっちで解説されとるがな。
104万行超えでも
データモデルにしてから集計クエリーで期間絞ればよろし。
遅いけど。 月の売上の横に前月比を出したいですがうまくいきません。 0から始まるインデックスを各行にふって
直前のステップ名が「A」とした場合、
前月比 = [売上] / A[売上]{[インデックス]-1}
最初の行がErrorになるのがイヤなら、後からエラーをnullに置換するか、
前月比 = if [インデックス] = 0 then null else [売上] / A[売上]{[インデックス]-1}
とか。
https://analytic-vba.com/power-query/m-code/begin-previous-ref/ 属性の列にA B C D E…と項目があり、値列に対応する数値が入っています。散布図の縦軸横軸どちらも、値を選択して、X軸はAの値、Ｙ軸はBの値、の様なことをしたいです。スライサーを軸別に設定するようなこと可能でしょうか？もしくは、データテーブルの作り直しから必要でしょうか。 >>225
仕様で、ピボットテーブルから直接、散布図やヒストグラム、箱ひげ図、株価あたりのグラフは作れなかったかと。
例えば、気象庁サイトからDLできる気象観測データがイメージが似ているか？
1時間値の場合、
対象年月日時間帯 観測項目 値
2022/12/19 12:00 気温 9.6
2022/12/19 12:00 降水量 0
2022/12/19 13:00 気温 10.1
2022/12/19 13:00 降水量 1
2022/12/19 14:00 気温 11.3
2022/12/19 14:00 降水量 2
・・・みたいなリスト型データに加工済みとか？
この場合、ピボットテーブルのフィルターだけ設定してスライサーを作る、例えば、
・観測項目を属性にして(スライサー対象可)
・対象年月日時間帯から年や月、時間帯を取り出して別列のパラメータにし(スライサー対象可)
・時間帯をX軸、値をY軸にして、
・スライサー選択のたびに、別に用意した属性(観測項目)フィルターするグラフ集計用クエリーをVBAで更新する
Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Dim tbl As ListObject
Set tbl = ThisWorkbook.Sheets("グラフ用集計").ListObjects("Q_グラフ用集計")
tbl.QueryTable.Refresh BackgroundQuery:=False
End Sub
なんてことで、時間帯推移での気温、降水量を切り替えられる(年または月切替えも可)散布図を作る
みたいなことは可能だけど。
X軸を対象年月日時間帯にすることも可能だが、
それじゃ散布図でなくてただの折れ線グラフで十分で、
ピボットテーブルでも可能。 たとえば風速の観測地点・年別の月展開箱ひげ図を
このやり方 >>228 でやるより、
PowerBIでやった方が遥に軽かった。 月別の集合グラフで半期だけ色変えたりフィルタ変えることはできますか？
４～９月は実績で１０～３月は計画値を表示したいです。 「集合グラフ」って、積み上げ棒グラフでない、通常の棒グラフ？
実績値と計画値の系列を分けたら色は変えられるけど、
ひと月の幅がやたらと広がってしまうので、
実績月/計画月の判断をシート上のセルで計算して、
それを頼りにVBAで色を変えることくらいしか
思いつかない。 PowerBIなんですが、カードで値を表示してフィルタしたときに、レコードがない場合(空白)って出てカッチョ悪いのですがこれを非表示にしたり別の文字に置き換えることって出来ますか？ >>233
DAX関数のALLSELECTEDとFILTER組み合わせてnullの除外する
と予想。(まだそこまで追いついていない・・・) 詳しい人おるん？ここ
日付の列を作ったけど
日付の範囲で選択出来るようにするにはどうすれば良いんでしょ？ PowerBIなんですが、フィルタされたときに連動するグラフY軸の最小値や最大値を指定できますか？ なるほど経理が使うのに大事なのは
クエリーで読み込む時に
日付を元にして期首からの経過月の列を作っておくことなのか
EXCELはアメリカ産だからデフォルトが12月決算になってしまうのな 自然科学や工学系ではExcelをメインでは使ってないだろうけど、
分析するには、秒や分単位時系列のカレンダーは要るのかと。
ロガーからデータ取って散布図や相関図作るにしても、
データがNAのところも表記しないと、
おかしな事になりそうだと想像。 powerBIすごいな
散らばってるDBをそのまま使って集計できる >>237
PowerBIなんですが、フィルタされたときに連動するグラフY軸の最小値や最大値を指定できますか？
はい、Power BIではフィルタリングされたときに連動するグラフのY軸の最小値や最大値を指定することができます。以下の手順で設定できます。
レポートの編集モードに移動します。
フィルタリングされたいデータを選択し、フィルターを適用します。
フィルタリングされたグラフを選択します。
右側のプロパティウィンドウで、軸の設定をクリックします。
「軸の最小値」または「軸の最大値」をクリックし、値を入力します。
「変更を保存」をクリックして、変更を適用します。
これで、フィルタリングされたときに連動するグラフのY軸の最小値や最大値を指定することができます。 >>233
そういう拘りは捨てたほうがいい
トリックが増えすぎてメンテ困難になる マージする時にキー列が一意でない場合、左×右＝で行、レコードが増殖するバグは解決不可能でしょうか？ >>244
申し訳ありません
左×右の左が多い場合、左の行分右の行が増えるというのは仕組み上分かります
しかしながら左の行が増幅する仕組みがまだよく分かっていません キー列が一意でない、
つまり、重複しちゃってるんだよね？
それでどうやって名寄せしろと？
VLOOKUPでもできないかと。 重複の削除はなぜTable.Bufferがデフォルトで用意されていないのでしょうか？
重複の削除で削除対象に選ばれる行、レコードになにか法則性はあるのでしょうか？ Table.Bufferと
グループ化からのインデックス追加
する方法で重複を削除する時に何か違いはありますか？
グループ化からインデックスを追加する方がステップ数が増えて処理が多くなりそうな気がします >>251
重複する列があって、その隣に日付順やインデックスの列があったとして
日付やインデックスを降順に並び替えた後に重複を削除したとしても
その並び替えは無効でなんらかの規則性に基づいて重複行が削除されます
しかしTableバッファを組み込むと並び替えられた状態で一番上の行だけが残ります
この方法とグループ化→インデックス列追加→0だけフィルターする
の違いは何かなと Table.Buffer方式は内部実装任せ
グループ化→インデックス列追加方式は自分で完全制御したい Table.Buffer と Round.AwayFromZero は内部でデフォでやって欲しいよなー Table.Bufferは、ストリーミングというpowerqueryの基本戦略に反するから、内部的にやることは絶対に無いと思う 初歩的な質問になるとは思いますが
ブックを読み込んだ時に10万行も読み込まれてしまう時があります
これは一番下の行まで0か何かしらの値が入力されているからなのでしょうか？ >>257
元ブックの、テーブルでなくてワークシートを読込んでいるの？
元ブックのワークシートで、本来読込みたい行数は何行？
元ブックの当該ワークシート開いて、Ctrl+Endで最右下行に移動したら、何行目まで行く？ >>258
なるほど
テーブルではなくブックやワークシートそのものを読み込んだ場合に何もない空白の行が読み込まれてしまう場合があるという事ですね
それは盲点でした
クエリのマージに頼るよりもリレーションシップで出来る事は極力（最大限に）リレーションシップで済ませた方が動作は軽いですか？ 全てのデータがデータベースに（一行、1レコード）揃っている状態が正規化されていない状態、あるいは第一正規化で
これ以上ないくらいに重複を排除した、それぞれ別のデータベースにしたのが第三正規化ですか？ >>259
実データが数万行程度までなら、
ピボットテーブル（PowerPivot）でのリレーションでも
PowerQueryのマージでも速さは同じぐらいの印象。
104万行超とかの大きなデータを扱うんだったら、リレーションの方が速いか？
ようつべで比較動画上がってたような？
ただ、ピボットテーブルにすると、その後はグラフにするしかなく、
ピボットテーブルのまとめ方が使えそうだったら、最後の段階に使っている。
まだ加工や二次利用が続くんだったらPowerQueryにしている。 >>263
そうですよね
リレーションシップはピボットテーブルで活用するしか使い道はないですよね（？）
結合に比べると自由度が低い（？） >>262
第一正規形は繰り返し項目の排除
つまりフィールドの数が同じ形に整えたもの
第三正規形は大体あってる 第三正規化はこれ以上ないくらいに細分化している
第二正規化は複数の状況証拠（条件）があるから推移的関数従属が決定する
第一正規化はデータベース
ってコト、、、？ >>267
第一正規化はフィールドを定義するために必要
買い物した人のレコードに商品1、商品2、商品3、…って無限にフィールドを作るのを防ぐ
第二正規化で重複データを分離
経理システムかなにかのCMでやってる、何度も同じデータを入力する必要がなくなる
第三正規化し第三正規形にすることでマスタデータとトランザクションデータを完全に分離できる
非正規形含む正規形はすべてデータベース
今は第六正規形まで定義されてる >>268
ありがとうございます
今までデータベースなんて考えた事はあまりなかったです 二つの表があって変更された行を表示させるのはやはりパワークエリでしょうか？ >>270
数百行程度なら、チェックする列数にもよるけど、ワークシート関数でも可能かと。
ただ、基準表と比較対象表をいろいろ入れ替えるんだったら、
ちと面倒かも。
行数が万単位なら、PowerQueryかな。
同じ表形式なら、基準表と比較対象表の入れ替えも楽だし。
20列とかの全列チェックするんだったら、
クエリー更新もそれなりに時間かかると思う。 https:linmoa.net
https://oraksil.cc/
https://todaync.com/
Microsoft Jpan destroyer 共通の処理まで進めて処理を分岐させようと思います。
そのときに、「複製」を使って処理を分岐させるのと
コピーを使って処理を分岐させるのでは複製の場合はバグが発生しますか？
参照だと分岐元に変更を加えない限りはエラーが発生しませんか？ パワークエリで消費メモリを削減するためにはどの様なポイントがありますか？ ちなみになのですが、たった3000行未満の表をList.Containsでフィルターしたり、マージしたりした後に2つ複製をして複製したクエリをそれぞれステップを書き加えてもエラーは起きませんよね？ >>279
はい
複製です
私は複製のことをコピーそのものだと勘違いしておりました
複製では依存先（？）依存関係にあるクエリまで複製されないので複製したクエリを実行しても動かないのですね
参照で別のクエリを作成し（わかりにくい表現かもしれませんが）作成したクエリに追加の作業を行うと参照元のクエリが書き換わる、変更されるものだと勘違いしておりました
複製ではなくコピーの方がいいのですね データソースであるexcelファイルを参照してるんだけど
上書きしても同じファイル名なら更新すれば読み込んでくれる？ >>281
参照する中身の構造が同じなら、
個別セルの値が変わっていても
レコード数に増減があっても
読み込むけど。 フォルダからファイルを接続して読み込んでいましたが不要になったので該当するクエリを削除し、フォルダを削除しました
しかしクエリを更新する時にファイルがありませんとエラーメッセージが表示されます
どうしたらいいでしょうか？ >>283
もしかして、そのクエリー、
「読込み先」の時に、ワークシートにテーブルとして読込んだだけでなくて、
「このデータをデータ モデルに追加する」にもチェック入れて読込んでない？
なので、テーブルとクエリーを削除しても「接続」が残っているのかと。
よく分らないけど、
メニューバーの「データ」から「クエリと接続」の右ペインで、
「接続」のタブに何か残っているようだったらそれを削除、
あるいは、メニューバーに「Power Pivot」が出るんだったら、
そこの「管理」開いて、何らかのスプレッドシートみたいなのがあれば、タブで削除、
するとか？ >>284
ありがとうございます
データモデルに追加する
にチェックを入れると動作が軽くなるとか早くなると聞いていたので何でもかんでも追加していました 頻繁にメモリ不足エラーを吐かれるんだけど一体なんなんだろうか？
私がデータモデルに追加しているからなんだろうか、それともデータソースを追加して、そのまま編集して、そこから次に読み込むで接続オンリーにしているからなんだろうか？ クエリのコピーや参照もいいですが、
ファイルそのものをコピーして分けて作成するのもいいですね 「データモデル」と「ワークシートにテーブル」の両方読込むと、
メモリーの制約からか、ワークシート側のソート、
特に複数列ソートが思い通りにならなくなったりしない？
104万行以上もワークシートには当然読み込めないし。
なんとなく、
・リレーションシップやDAX関数使わないんだったら、「データモデル」には追加しない。
・使うんだったら接続のみ（それでもソースとして参照したクエリーはワークシートにテーブルとして落とせたような・・・）
にしてるんだけど。 ワークシートに出力しているクエリをデータモデルに追加するとバグを起こす可能性があるのですね サーバーから吐き出されるCSVのファイル名を変えられちゃったんだけど
powerqueryのクエリで違うファイル名に変更できる？ 欧州の電力需給実績リアルタイム15分値のチャートがWeb公開されているんだけど、
https://energy-charts.info/charts/power/chart.htm?l=en&c=DE&stacking=stacked_absolute_area
これってPower BIベースなのだろうか？
日本の電力需給1時間値でマネして、
「折れ線グラフおよび積上げ棒グラフ」で再現できないかやってみた。
が、
1時間値の積上げ棒グラフの幅が、
最初はこのくらい狭く表示できたんだけど、
何かの拍子に広がってしもうた。
視覚化の「列」が積み上げ棒グラフのプロパティっぽいんだが、
「カテゴリの最小幅(px)」の最小値が「20」で、
これより細かくできない。
何かやり方あるのだろうか？ 
