【Excel】Power Queryを語るスレ【Power BI】
ワークシート上の名前定義した特定セルをPower Query内で参照する方法。
「テーブルまたは範囲から」データの取得をするのと似ている。
PowerQuery use a cell values in a URL
https://stackoverflow.com/questions/49281763/powerquery-use-a-cell-values-in-a-url
定義名を宣言して
M言語内引数のパラメータ部分にその定義名を置き換える。 テーブル内の2列の組み合わせでソートする方法。
How to sort multiple columns using the M Language in Power Query
https://youtu.be/_5HKai6oR78
M言語の Table.Sort() の引数内に2つの列を入れ込む。
例) データ取得日を降順で、その中で観測日時は昇順で並べ替えたい場合
並べ替えられた行 = Table.Sort([前のステップ名],{{"データ取得日", Order.Descending},{"観測日時", Order.Ascending}}) Excel関数の "COUNTIF(A$1:A2,A2)以下コピペ" 【Running Count手法】で、
重複を上から順に計算するのをPower Query内で実現する方法。
Power Query Running Count Based on Row Condition ? Excel Magic Trick 1588
https://youtu.be/kjOmNPoVDYs
説明が早口なので、ヒアリングが弱い自分はまだ何回か見直さなければならないけど、
1. 基準となる列をソートした後にステップで
BufferedTable = Table.Buffer("基準列") ・・・COUNTIFの"A列"に相当
と、メモリー上に返す宣言するステップを作っておく。
2. カスタム列で1列追加して基準列をもとにした「内部表」を作る
=Table.AddColumn(BufferedTable, "RunningCount", each 1)
と、全部 "1" を入力する列を仮に追加しておいて、
3. カスタムファンクションの "each" の部分を省略表現の
(OT) =>
に変え？("Outside Table" "Inside Table"を略してそれぞれ (OT) (IT) のことか？)
以下、後はなんとなくだけど、当初テーブルを「外部表」として、
「外部表」の基準列に紐付けられた、入れ子の「内部表」を各行に作って
この内部表について、それぞれの行数を数えるCount関数をかぶせて、仕上がりは、
= Table.AddColumn(BufferdTable. "RunningCount",
(OT) => Table.RowCount(Table.SelectRows(BufferedTable, (IT) => IT[基準列]
<= OT[基準列] and IT[内部表の集計対象列] = OT[外部表の集計対象列])))
みたいな？
=> <= は「順次代入する」の意味？
""で囲まれた列名のアタマに付いた#は、列名にブランクが含まれている場合の特殊記号？ ①同じフォルダ内に複数のEXCELファイルがある。
②そのすべてのEXCELファイルに全く同じ構成のテーブルがある（テーブル名、カラムの定義も一緒）。
そのすべてのテーブルをマージしたいんですが、ソース取得のコードってどう書けばいいでしょう？ >>6
行数を除いて、同じフォーマットの表であれば、
読み込むときに先に結合させては？
入り口は、このあたりも参考になるかと。
ExcelでPowerQueryを使ってデータ収集分析
https://qiita.com/mosugi/items/71df310b35db81722d1e
「データの取得－ファイルから－フォルダ－から」入って
フォルダーパスを指定したら、「結合」ボタンも出る。
その中のメニュー「データの結合と変換」を選ぶと
読み方(xlsxファイルだと、どのsheet/範囲/テーブルか、余計な行は削除するか、ヘッダーはどうするか、カスタム列の追加、列の型等)
をサンプルで指定できそう。
※ たぶん、エディターで自動生成される「サンプル ファイルの変換」で読み方を指定するのかと。
Power Query内に自動でパラメーター関係のフォルダーやクエリー等が生成されるけど、
このあたりの仕組みは、当方まだ解明できていない。
これをやってから読み込めば
出来上がるクエリーが1つになり、以後の扱いが楽になるかと。
ただし、
1. xlsx表内の目的のsheet名が異なっていると、面倒かも？
同じテーブル名だったら直接指定できるかもしれない。
2. 目的のフォルダーにさらにフォルダーがあって、別置きのものが入っていたりすると、
それも読み込んでしまう模様。 >>6
で、コードは >>7 で出来た詳細エディターを参考に。 Power Pivot使ってる人ってどの位いるんだろう?
自分は何とか使えるようになったけど、セミナーに出席しても使えてそうな人殆どいなさそう... >>9
日本語の解説書籍やWeb記事がまだ少ないからねぇ・・・
セル結合とかで、紙の帳票っぽく仕上げるとか、
IFの入れ子が多数な、複雑な関数を組んで作業列をなくすとかで、
見た目をよくすることばかりに血道を上げるより、
まず、テーブル形式(≒クエリー)がどれだけ便利かに気付いて貰えばと。
これ使うと、出来ることがだいぶ増える。
他の人がステップ(マクロ)の仕組みを理解するのは、
VBAよりも比較的わかりやすいし。
WebスクレイピングではPhythonより出来ることは限られるけど、
敷居の低さ、わかりやすさでは、こっちの方が良さそうに見える。
ビッグデータと呼ぶのが正しいかどうかは知らんけど、
特に、いろんなWebサイトで公開されてるデータ処理。
計算がバッチ処理・カスケード処理だから、
何十万行でも、普通のPCで出来るし。
VBAやPythonとの補完も出来そうだし。 パワーピボット使ってる。カラムの異なるデータがみるみる集計できるから多用してる。
でもあれも変なところで物足りなくて、時間表記でセル書式の[h]:mmなんかがサポートされてなかったり。 >>10
同感。
普通の関数を追求するやつもそうだけど、VBAで自動化とかいうのもあまり好きじゃないな。
重い大量データを処理するなら絶対DAXが良い。
それに、どうせスクリプト書くなら汎用的なPythonやシェルスクリプト覚えた方がマシな気がする。
ところでPower Pivotでスクレイピングって、こういうのの事？
https://docs.microsoft.com/ja-jp/power-bi/desktop-tutorial-importing-and-analyzing-data-from-a-web-page
Power Pivotでこういう事をやろうとはあまり考えてなかったな。Pythonでは考えてたけど。
今度試してみよう。 >>12
そう。
「メニュー－データ－データの取得と変換－Webから」
でURLを指定するヤツ。
Power QueryではPythonみたいに、
Web画面がテキスト入力させたりとかボタンを押させたりのものだとタイヤ雨出来ないけど、
複数ページあって、URLがページ数を反映しているものだったら、
対応できる模様。
こんなのとか。
[Power BI / Excel] 複数にまたがる Web ページからデータを取得する
https://road2cloudoffice.blogspot.com/2017/07/power-bi-excel-web.html 2016vbaでcsvから作ったクエリのrefreshallしても更新されないことがある？
うちの環境で一旦endしても更新されない時があるのは謎。
ボタン分けて継続の作業をしていますが、その間に手動ですべて更新しています >>14
vbaを噛ましてないけど、Office soloのExcelで、
1つのcsvから多段にクエリーを展開していくとき、
「すべて更新」で上手くいかないことはある。
正直、謎。
自分なりの回避方法は、
1. シーケンシャルに再計算させたいクエリーはそれぞれのプロパティで
「バックグラウンドで更新する」：OFF
「すべての更新でこの接続を更新する」：ON
https://i.imgur.com/ZBFAHRz.png
2. 「クエリと接続」の一覧で上から順番に並べる。
3. クエリーテーブルをsheetに置いてある場合は、
計算順に左からsheetのタブを並べ替える。
これでも「最後の再計算が終わった」と思っても、
まだ中間のクエリーの1つが再計算しだしたりする・・・
保険で「すべて更新」は2回することにしている。
MSに提案してもいいかも、
もっと確実にしてくれと。 >>15
ありがとうございます
同様の事象があることがわかり安心しました。
仕事で使えると思って頑張って学習しています。
確認しながら進めて聞きます。 外部データ取り込みで他EXCELファイルのテーブルを読みに行く時、
そのテーブルのカラムのデータ型はanyで、nullも文字列も小数点以下の数値（正数・負数）も含むんだけど、
Table.ExpandTableColumnのタイミングで小数点以下が丸められてしまう。
追っていくと、SampleFileでファイルを呼び出した時点で丸められてしまっているようだ。
なった事あるって人居ませんか？ 自己解決したので勝手に語る。
読み込み元ファイルの問題だった。
でも謎が残る。
元データのテーブルの該当データは通貨型。単純な掛け算式が入っていて、丸め処理はしていないから、セル表示では丸められているが、内部的には小数点以下を持っている。
これを、表示設定で小数点をセル表示上で見えるようにしてあげたら、クエリの結果に小数点以下が表示されるようになった。
因果はわかったけど、仕様が謎。
データ型変更、ステップのどこにも入ってないのにね。
他、関連するかどうかもわからないけど、勝手に型変更をしているステップがあって、Int64になってた箇所があった。この型も小数点以下の取り扱いが無く、丸められるので注意が必要。 >>18
thx.
取り込む時、手動で全列の書式は指定する。
M関数で日時を扱う時も、元の書式が違うと、
違う関数になるし。 読み捨てで読み込んだデータでは直接ピボット作れないのね
何十万行もあるデータファイルのデータ、容量を軽くしようて思ったけどダメだった
データモデルで読み込んでも半分も軽くならない yahoo知恵袋に、「読み込み先を”データモデルへの読み込み”に変更して,ブッククエリのみを作製するとそのまま『PowerPivot』で読み込みできます」とあったのを見つけたんだけど、自分では未検証です。
↓ソース
Power Queryを使って、２００万行ほどのデータを扱うことはできませんか？Power Pivotでの重複の削除の仕方がわからず、Power Query… - Yahoo!知恵袋 https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q11154335451?fr=and_other プライバシーレベルの設定で実際にどのような影響(効果)が出ますか？
社内のネットワークドライブに置いてある複数のブックからクエリを作ってそれを結合する様な使い方です。 パラメータの管理ってどういう使い方が想定されてるの？
パラメータに設定されてる値の変更はクエリエディタ開かないとできないよね？ あれはクエリエディタで汎用の関数やクエリを作るときの道具だと思う
Power BIのレポートのパラメータ編集なんかやるときに使うものでしょ 容量の大きなテキストから2つのテーブルを作るとき、今はクエリを2つ作ってそれぞれ実行するので、2回データの読み込み待ちがあるのが気になります。
読み込み1回で出来るよとか、なにか時間を短縮する方法を知ってる人がいたら教えて下さい。 pdfファイルも読み込めることを今頃気付いた。
Webページの特定項目をXPath指定でもして読み込めないかしら？ これ読んで、辿るのもしんどいけど
Webページの構造自体が変えられちゃったら、正直ツラい・・・
Web Scraping In Power BI And Excel Power Query
https://datachant.com/2017/03/30/web-scraping-power-bi-excel-power-query/ （備忘）
VBAでクエリーを更新するとき、
個別のクエリーを「最新の情報に更新」
の手動操作を「マクロの記録」でやると
ActiveWorkbook.Connections("クエリ - 【クエリー名】").Refresh
となる。
これだと前後のsheetやVBA内での計算が
きちんと実効されないケースがあった。
こうではなく、
1. 事前にクエリーの変数を
Dim tbl As ListObject
等と宣言しておいて、
2. 変数に当該クエリーを代入
Set tbl = ThisWorkbook.Sheets("【シート名】").ListObjects("【クエリー名】")
3. 実際に更新する命令は "QueryTable"オブジェクトの"Refresh"メソッドを使って
tbl.QueryTable.Refresh BackgroundQuery:=False
とすれば期待通りの結果を得られた。
ここでの、"BackgroundQuery:=False"と、
明示的なパラメーター指定が重要。
省略するとデフォルトがTrueなので、
元のクエリーがプロパティで「バックグラウンドで更新する」のチェックが外れていても
チェックが入った状態と同等で実効される(？)ため、
シーケンシャルに計算が実行されないのか？ エクセルのpowe queryに関してです。 Table1
A列
3
1
2
Table2
A列 B列
リンゴ 1
イチゴ 2
バナナ 3
結合して
Table1へ
A列
バナナ
リンゴ
イチゴ
このようにしたいです。 お知恵をかしていただけないでしょうか。 >>31
今ひとつ質問の趣旨が読み取れないのだけれど、
Table1-A列、Table-B列の数値は何？
品目コード？ 数量？
それとは別に「品目の表示順」を一定にしたいのだったら、
A列：表示番号、B列：品目のTable3
を別に作って、これに結合してソートかけちゃうけど。 【Microsoft】Excel関数ベースのプログラミング言語「Microsoft Power Fx」登場 オープンソースで公開予定 [少考さん★]
http://asahi.5ch.net/test/read.cgi/newsplus/1614745763/
ノーコードらしい 意味のわからん物ばかり作らないでくれ
標準のVBAですらバージョン互換が事実上無いのに。
それはそうとPowerBIをまともに使ってる会社は本当にあるのか
弊社はコンサル料だけ払って放置中 マイクロソフトはコンサルの飯の種の開発に熱心だからね PowerBIの様な素晴らしいツールがあるのに使えてないのが日本企業の駄目さ加減を表していると思う。
PowerQuery/BIの存在すら知らないのが大半かと。 Excel2016やねんけど新しいクエリの項目にPDF無いんやけどなんでか解る？
なんで無いんやろ？ 2013、2016でpdf読込みは出来なさそうな・・・
Wordで読込んでコピペする荒技の動画あり
https://kokodane.com/tec3_96m.htm 神経症でも発症したのか？
「公共、公共」とかギャースカわめくなら、
もっと国際的に通用する、英語にでもしよか？
失せろ >>46
PowerQueryのどの機能が必要で利用してるんですか 46 じゃないが元経理として、
Power Query は大きな組織では、
・ERPから吐き出した大量の仕訳データを分析する (PCでの計算負荷分散、定型化)
・ERP化出来ていない細かい集計を各部署・現場から集める
なんてところで使えたろうな、と。 項目 4月 5月 … 3月
あれ 123 456 … …
それ … … … …
…
みたいな表を、
データとして再利用するのに
項目 月 値
あれ 4月 123
あれ 5月 456
…
みたいに線形にするのに使ってる。
ピボットを分解できるのが一番ありがたい機能。
回収した見込情報や作成した予算の、整理、分析。そういったことに流用してますね。
他にも諸々。 リレーションシップでハマってます。
どなたかお知恵を貸していただけないでしょうか。
↓のテーブルで担当者1と社員マスタは繋げられるのですが、担当者2を繋げようとすると循環参照だとエラーになってしまい困ってます
データテーブル
・担当者1
・担当者2
社員マスタ
・社員 「データテーブル」というのは、
例えば、ある「部署」に属する担当者のリスト？ データソースをWEBからとした場合proxy認証はどうやって設定されてますか？ twitterやQuiitaっていうwebサイト見たほうが情報量多いので。 Excelの話。
複数のcsvをFile結合したクエリAがあって、
そのクエリAを「参照」して集約データをつくるクエリBをこしらえた。
これでクエリBだけを更新すると
更新状況を示す欄にcsvを全部読み込んでる表示が出てくる。
クエリAの読み込み先はデータモデルにしてあるのだけど、それでもいちいちcsv読み直しに行ってるみたいでめっちゃ時間かかる。
クエリAの結果を保持したままクエリBだけ動いて欲しいなあ…大したことしてないから一瞬で終わるはずなのに。何かいい手ありません？
データモデルの意味も良くわからんなあ…
読み込み先データモデルにしとくとOnMemoryじゃなくてDiskに保存されてるものと思ってたから、いちいち読み込み直すんかい！て思った。 >>56
csvまで遡っての再読み込みで時間がかかるほど大きなデータなら、
csvを統合するクエリAまでで1つのbook
それを読込みに行くクエリBを別のbookにすれば
よろしいかと。
クエリAもBも1つのbookで、
「すべて更新」ボタンで更新しているなら、
クエリの構成によっては先にBの更新をしてしまい、
エラーになることがある。
シーケンシャルにA→Bの更新を実行させたい※なら、
クエリAのプロパティを開いて
「バックグランドで更新する」
「すべて更新でこの接続を更新する」
の2つのチェックを外したらどうか？
https://i.imgur.com/ckSj5fa.png
クエリBの更新は「すべて更新」で
クエリAの更新は「クエリと接続」の一覧で当該クエリを右クリックで「最新の情報に更新」
で出来ないか？
※ 多重なクエリを厳密にエラーなしにシーケンシャルに更新したいなら、
VBAで"tbl"をListObjectで変数宣言して、クエリ一覧表から1つ1つ
tbl.QueryTable.Refresh BackgroundQuery:=False
で更新させている。 >>57
ありがとうございます。
bookを分割するのが確実そうですね。
月初の繁忙期に入ってしまうので来週くらいに時間取って試してみます。
あとこれsharepointに上げておいたら定時更新してくれたりせんかなあ。
パソコンカチャカチャ仕事を減らしたくていろいろやってるのだけど、もうコード書くどころか「すべて更新」押すのすら面倒になってきた笑
面倒なのを「やっとけ」言うだけの人はいいよね
やらされる方はほんとたまったもんじゃないわ… やっとマージでINDEX(MATCH)と同等のことが出来ると分かった。
レコードが大きくなると計算がおそくなるので
これで負荷を分散できるか？
けど、
世の中にはM数式で強引にINDEX(MATCH)を実現しちゃう人もいるようで・・・
https://community.powerbi.com/t5/Power-Query/Index-Match-in-Power-Query-M-Code-for-a-MS-Excel-Brain/td-p/1519512 >>59 に追記
マージでの名寄せで完全一致以外のあいまい照合は、
閾値設定とか別リスト参照か。
番号範囲に対する近似値（うちわの最大）とかは出来ないので、
ここはヤッパリ、ソートしてMATCHとかXMATCHになりそう。 初心者です
みなさま教えて下さい
PowerQueryで吐き出したシートに対して、列ごとに名前の定義をして使うことを考えています
しかし、元データを直して更新すると、吐き出したシートの列の名前の定義が消えてしまうようです
どうやったら解決できるでしょうか？
VBA組むか、その都度定義してやるしかないでしょうか
よろしくおねがいします！ >>61
> 元データを直して更新すると、吐き出したシートの列の名前の定義が消えてしまうよう
状況がよく分からないのだけれど、
列名変更をPower Query エディターでやらずに、
ワークシート上で直接変更した
ということ？
クエリーとして最初に認識した列名が
エディター内のその後のステップで扱われるから、
エディターで列名変更しないとクエリーエラーになるのかと。
元のシートの列名と、変換したい列名が固定しているなら
エディター内で列名を変更するステップを追加すれば良いのでは？
元表をクエリーに変換するとき、先頭行をテーブルの見出しにしないか、
例えば、
・元表は別のbook
・ワークシート名は同じ
・対象となるテーブルや定義範囲のタイトル行は、ワークシート上の開始位置は同じ
・列の基本構成や順番は同じで、「8月合計」→「9月合計」に変わる程度
ということなら、
クエリーとして読込む対象を、テーブルではなくワークシートにすると
A列、B列・・・がColumn1、Column2・・・として固定して認識されるので、
エディター内で
・このColumn1、Column2・・・の列名変更する
・データ本体1行目までの間にある余計な行(旧見出し行を含む)を削除
するステップを入れれば良いのでは？ PowerQueryで、
「セルがすべてnull」の列を判定して、その列を削除する
みたいなことってできますかね？
M関数をどうやって組めばよいでしょうか。 >>64
やっぱりだめですかありがとうございます
VBAでやるしかないですかね・・・ 全てnullのを消したい？
アホみたいな方法だが、できなくはない。 >>66
PowerQueryでなにか方法ありますか？ >>69
おお、その手がありましたか
ちょっと試してみようかな
ありがとうございます 行列入れ替え→AddColumnで条件式追加（その行が全てnullかどうか） 日付を和暦（2021/10/17→R3/10/17みたいに）するにはどうすればよいですか？ >>72
テキストにして「2021/」を「R3」に置き換えるか、
西暦を「-2018」して頭に「R」付けるのはだめでつか。
てかExcelで読み込んで書式設定してやればいい気がします。 初めての書き込みです
クエリとピボットどちらにもブックを開く時に自動更新がかかるようにしているファイルがあるのですが
ブックを開く→ピボット更新→クエリ読込 の順で処理されてしまうようでピボットが最新の状態になりません
クエリの読み込み結果を待ってからピボット更新とする方法はないでしょうか？
手動で更新すればいいだけの話なのですがそれでは自動化とはいえず困っています 私もわからないことが。
Query1（外部ファイル参照・ビュー整形）
↓
Query2（ファイル内のテーブルとQ1を参照）
Q2を更新したときに、
単純にQ1の結果だけを見に行ってほしい。
Q1のプロセスをイチから辿らないでほしい。
外部ファイルを参照しないようにしてほしい。
Table.Bufferも効いていないようだし、
VBAは選択肢に入れたくないし。
仕方ないから、Q1を参照する式を埋め込んだセルを用意してそれをテーブル化し、Q2はそれを参照するようにしたけど、良い方法では無いしね。 >>76
Query1の「クエリ プロパティ」は、
>>57 の画像にあるように
「バックグランドで更新する」
「すべて更新でこの接続を更新する」
の2つのチェックを外してる？ バックグラウンドは付けてますね。
全ては外してます。 56は同じ悩みのようですね。ありがとうございます。ロムってきます。 同じ行に、同じ文字列が出た場合、左から読んで一番左のものだけ生かして、ほかはnullにする方法とかありますでしょうか。
パワークエリでできたらありがたいです。
A B C A D C E C D F
↓
A B C D E F
みたいなイメージです
どうぞよろしくご指導お願いいたします！ >>80
一行だけなら縦横変換してダブリ排除
複数行なら比較列をひたすら挿入していく >>81
ありがとうございます
> 複数行なら比較列をひたすら挿入していく
具体的にはどんな比較列になるでしょうか m関数を示していただければ幸いです 日本語のYouTubeチャンネルでも
Power Query、Power Pivot、M数式、DAX関数を扱うところが増えてきた。
以下、さらっと紹介するんでなくて、個別機能を紹介していると思われるところ。
・ExcelドカタCH
https://www.youtube.com/c/Excel%E3%83%89%E3%82%AB%E3%82%BFCH%E3%83%89%E3%83%83%E3%83%88%E3%82%B3%E3%83%A0/videos
経理屋さん？からの視点
・ITツール学習
https://www.youtube.com/channel/UCRlt1tx7EA5HCSH6l22YisQ/videos
ここはPower BIからの切り口。
Power Automate Desktopなんかも扱っている。
こんなところか？
海外のような、
裏技的／かゆいところに手が届くような解説チャンネルは
まだか？
・クエリと接続の違い
・パラメーターの説明
なんかもまだなさそう。
個人的には、
クエリをワークシートに読込んで右列に重複チェックのCOUNTIF関数計算して
再度、右列を含んだ範囲をクエリにして重複チェック列値をフィルタしている自己流を
なんとかしたい。
M言語にこんな機能はあるのだろうか？ >>84
グループ化では重複数そのものは出せるんだけど
たとえば重複数が3だったとして
ソートしてCOUNTIF(A$1:A3,A3)のように
1
2
3
という芸当は出来なかったような。
DAX関数だと出来るらしいが、今度はその後のクエリー処理が続かなさそう。 >>84
「最新版を抽出する」という意味で、それらしき事例があったわ。
PowerQueryでやる方法は、ちょっとトリッキーだけど。
https://youtu.be/2DJI9V1kBqg >>87
グループ化して上から順番に連番を付けるのはできる。 誰か教えて下さい
フォルダから同じ体裁のデータを複数取得し、
クエリで一行目を削除して二行目をヘッダーに昇格させてるのですが、
どうしても2つ目以降のデータの先頭2行がレコードとして残ってしまいます
フィルタリングすれば消せるっちゃ消せるのですが、、、
これは仕様なのでしょうか？
内部的に先にデータを合体させてるからこんなことに？ ヘルパークエリのfxの内部を編集するとキレイに行くと思われ。 fxのクエリの詳細画面で、2行目と、接尾に2個あるin～の2個めの方と、一番最後の行、この3つを//でコメント化してあげると、普通のクエリとして認識されるから、その上で編集して、最後にその//を削除すれば編集完了。あとは本来のクエリ側で、問題なく順番にクエリが評価されていくかを確認して。 勤務実績(従業員名、日付、残業時間)から残業時間を５日毎かつ従業員別に集計したい
Sumlfsを再現するにはどの関数を使えばよいですか？ >>93
PowerPivotのDAX関数ものか？
M数式の Date.WeekOfYear あたりで週番号振っといて
SUMX(FILTER())使うとか？
Power BIでの説明だけど。
https://youtu.be/pf5ImoMh6fU
https://youtu.be/cP-Cw613014 年月、商品、数量のリストが数年分あります
数量の多い商品順にランク付けしたいのですが年月別に集計するやり方がわかりません
どのように範囲指定すればよいでしょうか？ これ四捨五入すると5が切り捨てられるんだけどどうにかならないですか？
Round関数 パワクエ
0.234 0.23 0.23
0.345 0.35 0.34 ←←←
0.456 0.46 0.46 どっかの記事
パワークエリの四捨五入は「銀行型丸め」が適用されており、ワークシート上のROUNDと結果が異なる場合があります。
この対策として、M関数の「Number.Round」の丸めモードを算術型丸めになるように設定する必要があります。 >>99
ありがとう御座います
色々検索しててAwayFromZeroを入れてみて一応期待する動作にはなってそうですがこれで果たして正解なのか。。 「後ろにステップを挿入」の使い方、少し分かってきた。
例えば、csvファイルが単純なマトリックスだけでなくて、
ヘッダーみたいに集計日とかデータ更新日とかが入っているような複合構成の場合、
上位行削除とか列削除/追加してなど加工して、本体部をひとまず整形し、
最後のステップ名が「変更された型」だったとする。<-ココ大事
ここで全行に共通する「集計日」列を追加したいとすると、
右クリックで「後ろにステップを挿入」して、処理をいったん分岐(？)出来る。
元ステップ冒頭の「ソース = ・・・」をコピペして「ソース2 =・・・」とすれば、
csvを読込んだ最初の状態をまた読み込める。
今度はヘッダー部分の「集計日」だけに削って行って、
集計日
2022/3/12
の2行×1列だけのテーブルにし、
ここでの最後のステップ名が「変更された型2」だったとすると、
R_集計日 = Table.FirstValue(変更された型2),
とかにして、1行目の値を名前定義の変数にしておく。
で、元の本体部への戻り方は、「カスタム列の追加」の場合だと
カスタム列の追加 = Table.AddColumn(変更された型, "追加列名", each R_集計日)
で、全行共通の集計日列が追加出来た。
・複数行/列にした場合はどうすればいいのか、
・他のクエリーの任意の行/列の値を取得するにはどうすればいいのか、
は今後の課題。
M式はList.なんちゃらを使う？
引数は{0から始まる行番号}[列名]を使った何かか？ 僭越ながら教えてください。
パワークエリで見た目の整形の話です。
Excel2019でパワークエリで大量の
テキストファイルを読み込ませてるのですが、そこまではうまくいきました
見栄えのためにテキストファイルごとに列を分割して並べて表示も時々利用したいと考えています。
そのようなことはできるでしょうか？
イメージとしてはテキストコピペしてエクセルのA1から横に張って行くイメージです。
※背景として利用したいテキストファイルが合計15000ほどあり各システム別に分けられているので一つのくぎりとしては100程度になります。
利用目的としてpivot機能も含むチェック業務利用です。 1行目だけで判断してやるなよと思ったら2行目以降もヤバかった >>104
powerquery内でTable.Pivot関数突っ込めば列にその情報ソースを出力できないこともない。リボンに列のピボット？があるはずだから、それ突っ込めば希望に近いものができるかもしれないので自分で調べて。その整形自体おすすめしないけど。15000列もエクセル側が受け皿を用意できたっけね？ 誘導されてきました
エクセルのクエリで、一つのファイルにある複数のシートから、たとえば１つ目から４つ目のデータ（１０００行くらい）を１枚目を１から４、２枚目を５から８に転記していくにはどのような設定でやればいいでしょうか >>108
その質問文、
VBAスレでわかりにくいって言われてたのに
そのままコピペするとは
おたく、どういう神経なのか？
マージについては以下参照。
https://hamachan.info/excel2019-powerquery-kyotu/#:~:text=Power%20Query%E3%82%A8%E3%83%87%E3%82%A3%E3%82%BF%E3%83%BC%E3%81%A7%E3%82%AF%E3%82%A8%E3%83%AA%E3%81%AE%E3%83%9E%E3%83%BC%E3%82%B8,-%EF%BC%BB%E8%B2%A9%E5%A3%B2%EF%BC%BD%E3%82%AF%E3%82%A8%E3%83%AA%E3%82%92&text=Power%20Query%E3%82%A8%E3%83%87%E3%82%A3%E3%82%BF%E3%83%BC%E3%81%8C%E8%B5%B7%E5%8B%95,%E3%82%A6%E3%82%A3%E3%83%B3%E3%83%89%E3%82%A6%E3%81%8C%E8%A1%A8%E7%A4%BA%E3%81%95%E3%82%8C%E3%81%BE%E3%81%99%E3%80%82
あとは知らん。 >>108
VBAスレから誘導されたの見てて心配で見に来たけど
不特定多数のシートを扱うならクエリも多分動的制御が必要になるから結局VBAのお世話になると思うよ。
たらい回しにするつもりはないけど、
VBAで言ってることを実装出来るコードを書いといたので一応見といてくれ。
Power Queryは使っていないけどね。 >>109
質問者自身が自分が何やりたいのか分かってないと思うから丁寧に回答したところで思っていたのと違うとかそんな事言われるだけになるぞ
お互い損するだけ >>110
普段はPower Query使ってるけど、
繰り返し大量処理だとメモリー爆食いするので
VBAも参考にさせてもらってるよ。
ヘルパークエリーでパラメータ使って
さらにその都度VBAでファイル保存の
合わせ技で効率良くなるかも知れない。 この処理は難しそうということですね
ありがとうございました >>113
生半可Power Cueryでやろうとするから難しいだけ。
やる方法には適材適所というものがある。
上記の要件であればVBAならバカでチンケな俺でも数分で作ることが出来た。というかVBAスレに貼っておいた。
Power Queryの今までやってきたことは無駄にはならないだろうけど、
自分でVBAのコード書いたりSQL書いたり出来ると更に出来る仕事の幅が増えるよ。
まぁスレ違いだからこの話はここではこれ以上しないけどね。 すみませんが、どうしてもわからないので教えてください。
Powerqueryで以下の式を”リストでの数値カウント”ではなくて、
原文そのままのテキストで表示したいと思っています。
どういう式を書けばいいのでしょうか？
「 ピボットされた列1 = Table.Pivot(削除された列1, List.Distinct(削除された列1[Source.Name]), "Source.Name", "Column1", List.Count)」
※上の式は「列のピボット」のGUIボタンで自動作成されました。 >>118
こういうこと？
たとえば、こんなテーブル：T_元表(一番左の青テーブル)があったとする。
ここでは「項目1：あ」－「項目2：a」の値が、「あああ」と「AAA」が重複している。
https://i.imgur.com/QI2nbEp.png
これをクエリーに置換えたのが2つ目のQ_元表で、Q_元表を参照して「項目2」列をピボットした時、
自動生成されるのは、おっしゃるとおり、List.Countによる「値のカウント」。
数値だとList.Sumで自動集計される模様。
3つ目のQ_元表_ピボット_自動生成
let
ソース = Q_元表,
ピボットされた列 = Table.Pivot(ソース, List.Distinct(ソース[項目2]), "項目2", "値", List.Count)
in
ピボットされた列
で、List.Countを、List関数のうちから適当に、List.Firstに置換えてみた
ピボットされた列 = Table.Pivot(ソース, List.Distinct(ソース[項目2]), "項目2", "値", List.First)
のが、4つ目の、Q_元表_ピボット_手動_ListFirst。
重複していた「あ」行－「a」列の値は、1文字目の文字コードで若い番号の「AAA」を拾ってきた？
なお、自動生成で個数を拾ってきた3つ目も、列ピボットの詳細オプションで「集計しない」を選ぶと
「あ」行「a」列はErrorで、重複していない他行他列は文字列の「値」を拾ってくる。
https://i.imgur.com/nnDKqPz.png
何をやりたいのかよく分からないけど、ListのM関数でよさげなものがあれば、
https://docs.microsoft.com/ja-jp/powerquery-m/list-functions
目的のものが出来上がるかも知れない。
「『値』列が数値ではなくて文字列で、文字列として全て集めたい(項目1の重複はOK)」
というなら、項目2について1つ1つマージかしら？ それだったら元表をソートすれば済んじゃいそう・・・ >>119
レスありがとうございます。
pngの通り類似の処理はしたのですが、残念ながらうまくいきませんでした。
緑文字でListととなり、一括に束ねられてしまいます。
※束ねられたものを成形する方法ありそうですが、わかっていません・・・
List.Firstやとかではテキストが表示するのですが・・・
List.SourceやList.ReverseだとListになってしまいます・・・
違いは適用したステップが自動生成されているくらいだとは考えています。 PowerQueryをGUI操作した結果で作られたコードを成形しようとしたら物凄く難しくないですか？
皆さん入り始めはどういう風に触られたんですか？
Microsoftが標準にGUIとして用意された機能を触っていると、
どうにもすこぶる遠回りして覚えてる気がしてならないです・・・
※それぞれ定義された名称が凄く長くなってるのが難易度あげてる気はします
ーーーーーーーーGUIでﾌｫﾙﾀﾞ取得時のコードーーーーーーーーーー
let
ソース = Folder.Files("C:\Users\max\Documents\Power"),
#"フィルター選択された非表示の File1" = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
カスタム関数の呼び出し1 = Table.AddColumn(#"フィルター選択された非表示の File1", "ファイルの変換", each ファイルの変換([Content])),
#"名前が変更された列 1" = Table.RenameColumns(カスタム関数の呼び出し1, {"Name", "Source.Name"}),
削除された他の列1 = Table.SelectColumns(#"名前が変更された列 1", {"Source.Name", "ファイルの変換"}),
展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプル ファイル"))),
変更された型 = Table.TransformColumnTypes(展開されたテーブル列1,{{"Source.Name", type text}, {"Column1", type text}})
in
変更された型
ーーーーーーーーーーーーーーーーーーーー >>120
元のデータがどういうものか、
もうちょっと説明がないと
対応しようがない。 >>122
おはようございます。
知識がなさすぎてどういう情報を提供すればいいのかさえわかっていませんが、
元のデータはﾌｫﾙﾀﾞ上にある「ただのテキストデータ」で、
GUIでﾌｫﾙﾀﾞごと選択して複数ファイルを引っ張ったものとなります。
丁度121のソースとほぼ同じだとおもいます。
数値とかは入力値としてはありますが、PowerQueryでは計算とかは求めないものと考えています。
諦めの選択にはなっていますが、powerqueryにここまでてこずると思っていなかったこともあり、
直近ではテーブルデータに起こしてから細かい計算とかはExcelの関数で手入力しようと考えています。
Excelの操作手順を以下に記載します。
①Excel新規作成 ②データの取得-ﾌｫﾙﾀから ③結合 ④クエリエディタその他のクエリから「変換」列のピボット ⑤数式バーでList.Reverseに変換とかを
情報小出しすみません。
データ取得からのデータ成形したいだけなので、クエリが得意とすることだとは考えています。 話題変わるけど、GUIまんまだと読みにくいからSlcClmとかに俺は名前変えてる。 クエリの関数は使えるものと使えないものが分かりづらいよね
所詮数々の企業をだめにしてきたマイクロソフトか そう言えば、ADOだとワイルドカードが%だから、
Accessも%だろうと思ったら実は*で、
フザケンナちゃんと統一しろムキー!ってなったことあるわ。 クエリ勉強用に書籍を購入検討していますが何かしら比較したことのある書籍あれば教えてください。
レスつくのが時間いると思いますので、
今時点は適当に購入してみて後日感想記載します。 >>130
クエリってpowerqueryを略して言ってるの？ ﾃﾃﾃﾝﾃﾝﾃﾝﾃﾝﾃﾝﾃｰﾝ ﾃｰﾝﾃﾃｰﾝﾃﾝﾃﾝﾃﾝﾃﾝﾃﾝﾃｰﾝﾃｰﾝ 月次更新を半年くらい繰り返してきたクエリーを今月更新したら、
こんなエラーメッセージが出てきて更新不能になったでござる。
> Formula.Firewall: クエリ 'Q_Result_byMonth_M04' (ステップ '変更された型') は、
> 前回の評価時には MultipleUnclassified/Trusted データを使用しましたが、現在は
> MultipleUnclassified/Trusted データを使用しようとしています。
参照している元クエリーのステップ '変更された型' と
このクエリーの '変更された型' を作る直してみたものの、改善せず。
"MultipleUnclassified" とはなんぞや？とググった中で、
英語のYouTubeで出てきたこの動画をぼんやり見てたら、
「PowerQueryのオプションで、プライバシーレベルを"無視"にしろ」
と、ささやくではないか・・・
Solve the Formula.Firewall Error in Power Query
https://youtu.be/z_kgvkgU8iI
とりあえずやってみたら、再度動き出すにはしたものの、
機械翻訳された大本営の説明がよくわからずorz
プライバシー レベルの設定 (Power Query)
https://support.microsoft.com/ja-jp/office/%E3%83%97%E3%83%A9%E3%82%A4%E3%83%90%E3%82%B7%E3%83%BC-%E3%83%AC%E3%83%99%E3%83%AB%E3%81%AE%E8%A8%AD%E5%AE%9A-power-query-cc3ede4d-359e-4b28-bc72-9bee7900b540?ocmsassetid=ha104009800&;correlationid=506dcb43-8d8c-4234-8a1a-a798fb3cc18f&ui=ja-jp&rs=ja-jp&ad=jp 今まで動いてたんならソース元を先ずは疑ってみるべす 質問です
グループ毎に小計及び総合計を出したいですが、簡単に出来る方法はありますでしょうか？
ネットに乗ってるLetは使い方が良く分かりませんでした 列数が20ほどありピボットテーブルで横に伸ばすと操作性が著しく劣るためパワークエリで直接編アレンジしてテーブル形式で出力したいです
機能の小計が使えれば良いのですがテーブル形式になってると使えないようです Powerqueryを数字ないデータで使ってる人っていますか？ フィルターされた行 = Table.SelectRows(テーブル, each Text.StartsWith([#"RowName"], FilterValue))
変数RowNameがうまく認識されず、
「Expression.Error: レコードのフィールド 'RowName' が見つかりませんでした。」
となります。
どうするのがよいでしょうか？ Start.Withは「その文字列で始まる」という意味だから、RowNameで始まる文字列が無いんでしょ。そこ変えれば？ 回答ありがとう。
RowNameは動的に変わる変数で、フィルタ用の文字列ではなく、列名を指してます。
たとえばRowNameが「商品名」だったとき、
・[#"RowName"]→エラー
・[RowName]→エラー
・[商品名]→正常
という感じでした。
RowNameに「商品名」が正しく入っていることは確認済みで、
もちろん列名が「商品名」という列も存在します。
角括弧 [] に変数を入れるときに、とくに気にすることはないと
思っているんですが、どうなんだろう？ おおよそ認識の通り、角括弧内の文字列に演算子を含む場合には#"〇〇"としてあげるくらい。このエラーには関係ない。
StartsWithはRowNameにFilterValueが含まれるか否かをTrueかFalseで返す関数らしいからそこが問題かと。その意味で「その文字列で始まる文字列が無い」んでしょうね。これ以上はわからん >>147
ありがとう
もうちょっと考えてみますわ 最近知ってめっちゃ便利だな
こねくり回すならpythonでするけど パワークエリで利用するデータはある程度整えてから利用した方が良いのでしょうか
読み込むExcelファイル内に社名や判子を押すセルなど不要なデータがあったりしてちょっと扱いにくいです
特にマージするときに行などがズレてしまいます｡
仕方が無いので､パワークエリの扱いがまだよくわからないのもあって素データを編集しています｡
素のExcelで不要なセルを削除したりしてからマージします｡でも編集するのは面倒だし
素のデータを編集するのは好きではないけど､これは仕方ないことでしょうか｡ PowerQuery自体にデータ整形の機能はあるけど
それにも限界があるかと。
ワークシートの読込みで
行列の規則性ではダメで、罫線だけが頼りの場合だと、
VBAも使わないと整形出来ないかも。
Webページの自動認識である程度テーブルを分けてくれるので、
M言語に罫線も認識する関数があるのかしら？
htmlで配列を検出しているのかどうか、
まだ分からないけど。 もともとクエリだからね。
データベース並みに構造化されたデータを加工するのが前提。
帳票的なデータをいじるのにはあまり向いてないわな。 >>152
>>153
ありがとうございます
今日もう一度見直したらエラーやnullが原因で崩れてしまうことがわかり無事統合できました｡
今までは不要な行は手動で消していましたがフィルタ機能を使えばできましたね…… 帳票上の使いたいデータが必ず所定のセルにあるなら、まだなんとかできる可能性はある。 質問が二つありますので宜しくお願い致します
1. CurrentWorkBook内に作ったピボットテーブルは
範囲参照では取り込めないというメッセージが出たので、
Excel WorkBooｋからの取り込みで作業中のファイルを指定して
ピボットテーブルのシートをデータモデルに取り込んで作業していました。
ある程度作業は勧められましたが 突然読み込みエラーでクエリごと
消えてしまいました。
作業中のBook内のピボットテーブルからデータを読み込む
のには制限があるのでしょうか？
2. クエリを削除しても、ピボットテーブルをデータモデルから
作成するときに、削除したはずのクエリがテーブルとして出てきます。
データモデルを完全に削除する方法を教えてください >>156
ようわからんけど、
>1.
ピボットテーブルを参照「元」にすることは、経験的に避けている。
フィルターや構成変えるだけで指定した参照範囲が壊れてしまうから。
何かフィルターを変えたら104万行を超えてしまったとかも。
ピボットテーブルは参照元にはせず、それを最終出力とするかグラフに繋げるかだけにしている。
でなければGETPIVOTDATA関数を使ったこんな場合とか。
https://dekiru.net/article/4481/
さらにいじりたいのであれば、クエリーで絞るかPowerPivot内(DAX関数)でやるか。
>2.
クエリーを作る時、最後に「データモデルに追加する」にチェックを入れてワークシートに読込ませると
シート上のクエリーテーブルとは別に、
PowerPivotで扱われる「データモデル」としても同様のテーブルが作られると理解。
これがワークシート上限104万行を超えて扱えるヤツ(その場合は「接続のみ」で利用)。
当該クエリーも削除した後で、後者を削除するには、
(他のクエリーがあれば、そこをアクティブにするとメニューに「Power Pivot」が現れる)
Power Pivot - 管理
で、PowerPivot上の当該シートタブを右クリックで削除。 >>157
ありがとうございます。ピボットテーブルはやはり参照元にしない方が良いのですね。
確かに集計項目を変えた時にエラーが出ました。
今やりたいのは例えば、下記のようなテーブルがあったとして
売上１００以下の商品はその他というくくりにして集計したいのです
商品 カラー 売上
A 赤 １００
A 青 ５０
A 黄 １０
B 赤 ７０
B 青 ２０
C 赤 ９０
C 青 ２０
D 赤 ２０
D 青 ５０
上記の表を
商品 売上
A １６０
C １１０
その他 １８０
このように加工して、その表を再びテーブルとして使用したいのです。
私はピボットテーブルを使ってしまったのですが、このような場合に使える方法がありましたら、教えてください。 ピボットテーブルの参照元データはpowerqueryから見られないの？
powerqueryで参照元データを直接いじれば良いのでは？ >>158
力技だけど、列の追加で売上100以下なら他、そうじゃないなら商品入れる。
あとはその列でグループ化 >>160 が言うとおり、グループ化でやってみた。
とりあえず成果品の画像。
https://i.imgur.com/399Bh2k.png
1. 最初のQ_DataはテーブルT_Data(青)をクエリー化しただけ。(接続のみ)
2. 2つ目のQ_合計売上100超は商品でグループ化して、その売上合計を出し
https://i.imgur.com/8vxZzwH.png
フィルターで100超だけに絞ったもの。(接続のみ)
3. 3つ目のQ_合計売上100未満は、同様に、売上合計が100未満に絞って(商品BとD)、
その合計の答えをリスト形式で出せるから、
https://i.imgur.com/rtfVOYD.png
テーブルに変換、
https://i.imgur.com/VUKKfcp.png
行タイトルを"Column1"から2つめと同じ「カウント.売上 の合計」に変更、
「商品」列を追加して値を"その他"とし、
列順を2つ目と同様に並べ直した。(接続のみ)
4. 4つ目のQ_追加統合が2つ目に3つ目を追加したもの。(シートに読込み)
ちなみに「その他」の合計は180ではなく160の模様。
これなら二次利用の参照元にも出来るべ。 >>161 の追記
2.と3.のクエリーは1.を参照 >>161 の訂正
100超と100未満じゃ、「100」が抜けてまう...orz
そのあたりはよしなに。 質問させてください
折れ線グラフで毎日の温度データを表示しています
・基準値を超えた日に任意の文字を表示させる
・位置はデータに追従させる
ということはできますか
ヒントに入れてカーソルを合わせたら表示されるのではなく、常に表示した状態にしたいです >>164
「任意の文字」とは何？
データラベルとして、日付や温度以外の、
元表中の「任意の何か」の列の値を
ラベルオプションの「セルの値」設定で表示すること？
であれば、PowerQueryというより、
VBAで系列と要素番号とかを指定して
データラベル表示させることになるのかと。 どっちかというとPowerappsとかの領域かもね。Powerqueryでは無理。 某アプリから出力されたCSVファイルを使って、その右側に何列にも渡ってピボットテーブルに使用できる関数の入った
セルを作ったエクセルファイルがあります。
これに毎週追加されるCSVファイルのデータを追加していくのですが、当初、単純に作ったエクセルファイルに
追加CSVファイルの値だけをコピペして、右側の関数セルはオートフィルで追加すればいいかと考えていたのですが、
PowerQueryなる便利なツールでもっと効率的にできそうというのがわかりました。
質問は以下になります。
1.すでに作られたエクセルファイルの書式（テーブル化されてます）で、CSVの追加データをPowerQueryでインポートするにはどうすればいいでしょうか。
（各列のタイトルは追加CSVと全く一緒です）
2.PowerQueryにまったくの無知でも、PowerQuery上で関数の入った列も入れてしまった方が良いでしょうか。
色々ググってみましたがさっぱりわかりませんでした。
Amazonで参考書注文してきたので今後勉強していきますが、とりあえず上記がサルでもできるほど簡単なら教えてください。
よろしくおねがいします。
↓こういう状況です。
https://i.imgur.com/Bi79f98.png >>168
すみません、図の追加CSVにある左の番号は11から続く出力データです。 さらにすみません、これ実際は上から下の順ではなく、下から上方向の順で最新のデータが入ってます >>168
1. 週ごとのcsvを(あるいはそれを読込んだExcelファイルを作って)、
同じフォルダに入れておく。
2. これらを通しで集計するには、別ブックから「データの取得 - フォルダから」で、
全てのファイルを結合出来る。
・「パラメータ」を使う「ヘルパークエリ」が自動作成されるが、
カスタマイズはそんなに難しくない。
・参考動画
https://youtu.be/RCmoho3jU8Q
https://youtu.be/VxKoqBJWgRo
https://youtu.be/TvIsvzGiQTU
https://youtu.be/uLV85sauJRw
等。
・このクエリーの中で「新しい順」にソートするステップを入れておけば良い。
・1. を毎週やっていれば、最新の通し集計は、このクエリーを更新するだけ。
3. 関数計算の展開は、2.のファイル内で各週csv等を
結合してから展開する方が吉。
1.の各週ファイル内に関数計算を仕込んでおいても良いけど、
追加や変更が出たとき、全部直さなきゃならなくなるから。 >>171 で
> 3. 関数計算の展開は、2.のファイル内で各週csv等を
> 結合してから展開する方が吉。
とは書いたものの、
・1週分のデータが数千行以上
・結合した後の合計数万行以上
・横列に展開する関数列がやたら多い
なんてことで、2. のブックの処理速度が落ちるというのであれば、
計算負荷分散のため、
1. のファイル内で関数計算を展開しておくことはあり得る。 でなきゃ、各週分を取り込んだとき、2.のブックのワークシートには読込ませず、
データモデルにしておくのも一つの手。
データモデル内で右に列を追加しての関数計算は可能。
データモデルだとワークシート104万行の限界を超えられるし、
これをピボットテーブルのデータ元にすることも可能。
とかやってるうちに、欲が出てきてPower Pivot、DAX関数の沼にはまったが。 >>171
むちゃくちゃ親切にありがとうございます！
1週分のデータはせいぜい100～200行ぐらいで、結合後もまだ3000行弱なので最初のやつで大丈夫だと思います。
とりあえず動画見てみます！ PowerBI上で重回帰分析は実施できますでしょうか？ >>175
やったことないけど出来そうに見える
Power BIの高度な分析機能の紹介【主要なインフルエンサー】
https://youtu.be/yqJtC4_-4lE >>176
ありがとうございます。
変数の選定は出来ますね。直接式を求めることは出来なそうです。
式は別途求めればいいのかなぁ。 お願いします
組織アカウントのPower BIワークスペース上に、いつからか他者が作成したレポートが表示されています
どうにかワークスペース内から表示を消したいのですが、方法をご存知の方いらっしゃいませんでしょうか ExcelでPower Queryを触りはじめたのですが、
Table1とTable2それぞれの特定列が一致した場合に
一致した行だけを集めてTable3を作る、
といったことも可能でしょうか ありがとうございます！
引き続きリファレンス読んでやってみます！ >>182
無粋だな
>>180-181の流れで完璧だったのに webからの取得で認証フォーム突破出来なかったから調べたけどBASIC認証のみなんやね
カスタムから出来るかもしれないて回答してる人いるだけでやり方はどこにも書いてなかった
認証フォームあるようなやつスクレイピングしたいならPythonとかプログラミング学んだ方が早いんかな Power Queryで捨て仮名をナミ字に変換するにはどうすればいいんでしょう
ぁぃぅぇぉゃゅょゎっ それぞれを、
あいうえおやゆよわつ にしたいのですが、上手いやり方を思い付けません 変換テーブルを使う考え方でなんとかできました
もっと上手いやり方があるようでしたら教えてください
以下が今回やった方法です
= List.Accumulate(Table.ToRows(#table({"捨て仮名","ナミ字"}, {{"ｧ","ｱ"},{"ｨ","ｲ"},{"ｩ","ｳ"},{"ｪ","ｴ"},{"ｫ","ｵ"},{"ｬ","ﾔ"},{"ｭ","ﾕ"},{"ｮ","ﾖ"},{"ｯ","ﾂ"}})),[ｶﾅ],(x, y)=>Text.Replace(x,y{0},y{1}))) 解説記事としては、これかな？
「複数の語句をまとめて置換する」を実務で使おう｜Power Query
https://qiita.com/olt_yt/items/07a28fb8173a324b9c1c >>188
全然解説記事になってないな
読んで損した
その記事が参照した記事の方が、解説もしっかりされてる
複数の語句をまとめて置換する - Qiita
https://qiita.com/tanuki_phoenix/items/94fb489726a42ad764b5 PowerBI Proより無料のDesktopのほうが機能多いのね Power Queryでクエリの並び替えってできますか？
今は新しいExcelブックをつくってクエリをコピペするという不毛な作業をしています >>191
手動のドラッグによる並べ替えの話でいいの？
Excelの「クエリと接続」ペインだと右クリックメニューで一段ずつ上か下だけど、
いずれかのクエリーを編集で開いて
Power Query エディターの左ペインでやれば、
ドラッグで飛ばして並び替えは可能だけど。 >>192
ドラッグでできました…
まさかこんな単純な方法だったとは盲点でした
ありがとうございました！ 他のブックへのクエリーのコピーは
PowerQueryのスクリプトをまるまるコピペで。
ワークシートの定義名範囲や、
クエリーでないテーブルの参照をクエリー内でしているときは、
それらも再構築が必要だけど、
名前定義の範囲のコピペは、
「(当該)ブック」とか「(当該)ワークシート」とか、
その有効「範囲」の作り直しが必要だったかと。
でないと「他ブックのリンク」になってしまうので要注意。 Table.AddColumn(ソース, "新列名", each [旧列名]*1000)の 旧列名 の部分を変数にするにはどうしたら良い？ 指定した日数分の営業日ってDAX関数でできますかね？
指定した日までの営業日ならあるのですが。 >>195
それは俺も知りたい。
任意の列名変更は、一度ピボット解除して列名を縦にして、
if Text.Contains() then else
とかで検索と置換が出来るようにすれば良いのか？
エロい人、教えて。 >>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」で、
これより細かくできない。
何かやり方あるのだろうか？ パワークエリでフォルダからブックを読み込む時にシート2のみ取り込む方法はありますか？ ベースはPower BIではなく
これとか
https://www.highcharts.com/
これら
https://d3js.org/
みたいだった。
失礼。
にしても、Power BIで近づけることはできないかしら？ >>298
目的のフォルダ指定してPower Queryエディター開いたら、
ステップを最初の「ソース」だけ残す。
kind列でsheetだけ絞るフィルターかけて
Item列にsheet名が出てるはずだから
またフィルターでそれだけ絞る。
1行だけになったらData列だけ残して他の列を削除。
中の緑色の文字"Table"をクリックすれば、
目的のsheetが開く。
そのステップも自動で追加される。
あとは型の変更なり計算なり、
ステップを増やしていくのはお好きなように。 >>300
ありがとうございます😊
Bing君に聞いても上手く答えを出してくれなくて 完全外部結合させた時にキー列のnullに右部のキー列を入れるにはどうしたらいいでしょうか？
完全外部結合と論理和は何が違うのでしょうか？
Bing AIくんお尋ねしたら完全外部結合は表を結合させるもので、論理和はAUBだから概念は違うけどどちらも全てという意味では似てると言われました マージする前の右と左のクエリーの、
それぞれの列はどうなっている？ >>303
条件列作って、[キー列]がnullなら[右のキー列]、それ以外は[キー列]
キー列を削除
作った条件列をキー列の名前に変更
M言語を直接編集する気があるならキー列を直接変更する方法はあるよ >>305
M言語？はちょくちょく触らせてもらっています
それ自体は簡単そうなので気になりますね
話が変わりますがテーブルに読み込んでいるクエリだけ（？）データモデルに追加するを止めるようにしたらあれだけ出ていたメモリ不足エラーやその他のエラーがぱったり消えました
やはりバグなんですね >>306
= Table.ReplaceValue(前のステップ, each [キー列],each if [キー列] = null then [右のキー列] else [キー列],Replacer.ReplaceValue,{"キー列"}) あれ？これで十分なのかな？
= Table.ReplaceValue(前のステップ, null, [右のキー列],Replacer.ReplaceValue,{"キー列"})
試してみて 一つ聞きたいのですが
パワークエリで横に長いテーブルを読み込み編集します
そのシートの下でも上でもいいので集計行を追加する事は可能でしょうか？
次にそのクエリを参照したクエリで行列入れ替えを行い、見出し列を行に並び替えます
見出し行の隣に参照元の集計行を行列入れ替えで追加できますか？ ちなみになのですが昨日サイトで完全外部結合した後にキー列をカスタム関数のifで結合すればいいという事のを見て自分で実践してみましたが＝が二つになってしまい、なんとかトークンが必要ですというエラーが出てきました
あれは何が問題だったのでしょうか？
BingAIに聞けばよかったですね >>307
eachが必要
= Table.ReplaceValue(前のステップ, null, each [右のキー列],Replacer.ReplaceValue,{"キー列"}) >>311
「トークンが必要」というエラーは
詳細エディターで手入力でM言語式を書いたとき、
うっかり間違える「前のステップ」名。
でなきゃ、途中ステップの最後に "," を忘れたか、
最終ステップ（in前）の最後の記述に余計な "," を入れた
だったか。 >>313
そうだったのですね
ありがとうございます
まだまだパワークエリのM言語は触り始めたばかりでルールを知りませんでした パワークエリのグループ化の集計方法は何が違うのでしょうか？ B列からH列まであって
それぞれの列の値は被らないとします
この場合B列からH列までを一つの列にまとめる事は可能でしょうか？ データモデルに追加はしない方がよさそうだね
なんでクライアントのメモリ食ってんの？ データモデル(PowerPivot)はデータをブックに読み込んでしまう
ファイルサイズ見ればわかるよ
PowerPivotの編集画面で表示されるデータがそのままブックに保存されてる
PowerQueryだけならそうならない >>318
データモデルに追加はよく省メモリとか軽量化のための手段として紹介されていますが
全くの出鱈目だったんですね データモデルは、
ワークシート限界の104万行以上を扱える。
Power Pivotと併用すると、計算が速い。
その代わり、データが大きいほどメモリーは食う。 >>320
リレーションシップだから処理が軽く早いんだと聞いていましたが違ったのですね たとえば、>>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 外せるよ。
「クエリと接続」の右ペイン出して、
目的のクエリー右クリックして「読込み先」、
「このデータをデータモデルに追加する」のチェックを外す
だったか。
Power Pivot開いて目的のクエリーのタブ右クリックして削除でも行けたかな？ パワーピボットで3種類の値の入った列を複数列、行フィールドに入れてフィルターをかけたらどうなりますか？
ANDでフィルターをかけられた結果が表示されますか？ 何をどうしたいのかさっぱり分らんけど、
まず3種類の値を3列に分ける方が先なんでねーの？ >>327
やはり一つの列には一つの値しか存在してはいけないのですね
私もア以外の値をなくすことによってピボットテーブルで集計する事ができました
https://i.imgur.com/bTwA4tg.jpg
https://i.imgur.com/z7yJTK3.jpg
この様に列を並べて一つのピボットテーブルで集計したいです >>328
「一つの列に一つの値」は基本中の基本、イロハの「イ」やで。
それと、他人に説明するとき、示す文章と絵を一致させるのも、イロハの「イ」や。
よーく、覚えとけ。 >>329
一つの列に一つの値が基本中の基本なので一つの列しか並べられない
という事はわかりました
ではなぜこれがア イ ウの値のある列ではなく、アしかない複数の列を並べる場合だと上手く機能するのでしょうか？ ピボットテーブルにもデータモデルに追加すると動作が重くなる とか メモリ不足等のエラーメッセージが出る という事はありますか？ データモデルとワークシートの両方に読込むと
おかしくなる時がある
って話でねーの？ >>330
この馬鹿はあちこちで聞き回って聞かれたことに答えもしないクズ >>331
データの格納効率(圧縮率？)はワークシートよりデータモデルの方が良いらしい
でもその処理で余計にCPUやメモリは消費するかもしれないね 列にフィルターを掛けます
フィルターされた のステップが挿入されます
このステップのコードをコピーすれば何回でも流用できますか？ SQLの基本を解説する本を読んでいてパワークエリに通じるものを感じます
つまりそもそもSQLを覚えましょうという事なのでしょうか？ パワークエリはSQLでできる事をさもすごい新機能の様に言っているだけでしょうか？ M言語とSQLが同じに見えるかわいそうな子
SQLのQがなんだか分かるようになるといいね Pythonを少し触りはじめた人がExcelでは上手く作れない複雑な計算（数学？）のグラフがPythonだったら簡単に作れると驚いていたのですが、それはデータベースとかライブラリだからだとしたらそういう事だったのかという感じ パワークエリでPythonを動かす人もいるのでしょう？ リレーション先のテーブルにある別カラムを条件に、棒グラフの1本だけを色変えたりできないかな？ ん？
どういう集計のどういうグラフか、詳細が分らないが、
条件使ってPowerQueryか関数で別系列に出来るなら、
色付け自体は固定的にグラフの設定
棒グラフじゃないけど、例えば、
最大値を別の色のマーカーにしたいとき、
最大値の系列を別に作って、他データを#N/Aにして
その1点だけ、あたかも別系列で重ねて表示させる、
なんてことはよくやる。
系列名が動的に変わるならVBA
とか。 List.TransformManyの存在理由が納得できるような使い方の例を示してください。 pdf表のデータ・スクレイピングは難儀だけど、
例えば、毎月発表される東電パワーグリッドの再エネ接続量のこのファイル、
https://www.tepco.co.jp/pg/consignment/system/pdf/newenergy_hondo_backnumber.pdf
これから%の表は抜きで
電源種別、申込ステータス、年月ごとの容量(万kW)をリスト化にするには、
・ページ番号・行番号・列番号使って、
・いったん時系列・項目・データ部に分け、
・番号を頼りにマージで再合成
すれば、PowerQueryでも出来んのね。 Webデータを読み込むクエリーで
ステップを重ねていって、途中でエラーになったとき（例：該当するデータがない等）、
その後のステップを飛ばして(if then elseでやるか・・・)
データがないのテーブルを#tableで生成して終わるような処理は
出来るかな？ 初歩的な質問かもしれんのですが、この動画https://youtu.be/tFrjr3IiIlM?si=nYS3GRwPgYwMprmO 見ながら勉強しててドリルダウンまで行ったんだけど何故か綺麗に左からGDP順でドリルダウにならず…国名の五十音順になってしまうのって何故だと思う？ 
