【Excel】Power Queryを語るスレ【Power BI】 YouTube動画>13本 ->画像>15枚

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/

ワークシート上の名前定義した特定セルを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
ダウンロード＆関連動画＞＞

@YouTube



M言語の Table.Sort() の引数内に2つの列を入れ込む。

例) データ取得日を降順で、その中で観測日時は昇順で並べ替えたい場合
並べ替えられた行 = Table.Sort([前のステップ名],{{"データ取得日", Order.Descending},{"観測日時", Order.Ascending}})

# にはサンプル云々の意味がある模様・・・

�@同じフォルダ内に複数のEXCELファイルがある。
�Aそのすべての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


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

>>21
ヒントありがとう！試してみます。

プライバシーレベルの設定で実際にどのような影響(効果)が出ますか？
社内のネットワークドライブに置いてある複数のブックからクエリを作ってそれを結合する様な使い方です。

パラメータの管理ってどういう使い方が想定されてるの？
パラメータに設定されてる値の変更はクエリエディタ開かないとできないよね？

あれはクエリエディタで汎用の関数やクエリを作るときの道具だと思う
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/

これはExcelアドオンソフトなのかしら？

https://seotoolsforexcel.com/xpathonurl/

（備忘）
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://2chb.net/r/newsplus/1614745763/

ノーコードらしい

意味のわからん物ばかり作らないでくれ
標準のVBAですらバージョン互換が事実上無いのに。
それはそうとPowerBIをまともに使ってる会社は本当にあるのか
弊社はコンサル料だけ払って放置中

マイクロソフトはコンサルの飯の種の開発に熱心だからね

PowerBIの様な素晴らしいツールがあるのに使えてないのが日本企業の駄目さ加減を表していると思う。
PowerQuery/BIの存在すら知らないのが大半かと。

Excel2016やねんけど新しいクエリの項目にPDF無いんやけどなんでか解る？
なんで無いんやろ？

なんで公共の場で地元言葉なの？

2013、2016でpdf読込みは出来なさそうな・・・
Wordで読込んでコピペする荒技の動画あり
https://kokodane.com/tec3_96m.htm

>>40
そうかあかんか

なんで公共の場で方言なの？

神経症でも発症したのか？

「公共、公共」とかギャースカわめくなら、
もっと国際的に通用する、英語にでもしよか？

失せろ

ﾆﾔﾆﾔ

仕事でバリバリ使ってる人はいますか？

経理職。結構使ってる。

>>46
PowerQueryのどの機能が必要で利用してるんですか

46 じゃないが元経理として、
Power Query は大きな組織では、
・ERPから吐き出した大量の仕訳データを分析する (PCでの計算負荷分散、定型化)
・ERP化出来ていない細かい集計を各部署・現場から集める
なんてところで使えたろうな、と。

リレーションシップでハマってます。
どなたかお知恵を貸していただけないでしょうか。

↓のテーブルで担当者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つのチェックを外したらどうか？


クエリ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で読み込んで書式設定してやればいい気がします。

初めての書き込みです
クエリとピボットどちらにもブックを開く時に自動更新がかかるようにしているファイルがあるのですが
ブックを開く→ピボット更新→クエリ読込 の順で処理されてしまうようでピボットが最新の状態になりません
クエリの読み込み結果を待ってからピボット更新とする方法はないでしょうか？
手動で更新すればいいだけの話なのですがそれでは自動化とはいえず困っています

VBA挟む

私もわからないことが。

Query1（外部ファイル参照・ビュー整形）
↓
Query2（ファイル内のテーブルとQ1を参照）

Q2を更新したときに、
単純にQ1の結果だけを見に行ってほしい。
Q1のプロセスをイチから辿らないでほしい。
外部ファイルを参照しないようにしてほしい。

Table.Bufferも効いていないようだし、
VBAは選択肢に入れたくないし。

仕方ないから、Q1を参照する式を埋め込んだセルを用意してそれをテーブル化し、Q2はそれを参照するようにしたけど、良い方法では無いしね。

>>76
Query1の「クエリ プロパティ」は、
>>57 の画像にあるように
「バックグランドで更新する」
「すべて更新でこの接続を更新する」
の2つのチェックを外してる？

バックグラウンドは付けてますね。
全ては外してます。

56は同じ悩みのようですね。ありがとうございます。ロムってきます。

>>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言語にこんな機能はあるのだろうか？

>>83
グループ化で重複排除できない？

プライバシー

間違えた

>>84
グループ化では重複数そのものは出せるんだけど
たとえば重複数が3だったとして
ソートしてCOUNTIF(A$1:A3,A3)のように
1
2
3
という芸当は出来なかったような。

DAX関数だと出来るらしいが、今度はその後のクエリー処理が続かなさそう。

>>84
「最新版を抽出する」という意味で、それらしき事例があったわ。
PowerQueryでやる方法は、ちょっとトリッキーだけど。

ダウンロード＆関連動画＞＞

@YouTube

>>87
グループ化して上から順番に連番を付けるのはできる。

誰か教えて下さい
フォルダから同じ体裁のデータを複数取得し、
クエリで一行目を削除して二行目をヘッダーに昇格させてるのですが、
どうしても2つ目以降のデータの先頭2行がレコードとして残ってしまいます
フィルタリングすれば消せるっちゃ消せるのですが、、、
これは仕様なのでしょうか？
内部的に先にデータを合体させてるからこんなことに？

ヘルパークエリのfxの内部を編集するとキレイに行くと思われ。

fxのクエリの詳細画面で、2行目と、接尾に2個あるin〜の2個めの方と、一番最後の行、この3つを//でコメント化してあげると、普通のクエリとして認識されるから、その上で編集して、最後にその//を削除すれば編集完了。あとは本来のクエリ側で、問題なく順番にクエリが評価されていくかを確認して。

勤務実績(従業員名、日付、残業時間)から残業時間を５日毎かつ従業員別に集計したい

Sumlfsを再現するにはどの関数を使えばよいですか？

>>93
PowerPivotのDAX関数ものか？
M数式の Date.WeekOfYear あたりで週番号振っといて
SUMX(FILTER())使うとか？

Power BIでの説明だけど。
ダウンロード＆関連動画＞＞

@YouTube


ダウンロード＆関連動画＞＞

@YouTube

年月、商品、数量のリストが数年分あります
数量の多い商品順にランク付けしたいのですが年月別に集計するやり方がわかりません
どのように範囲指定すればよいでしょうか？

スマホの有料版で、パワークエリ使えますか？

事故解決
使えないっぽい

これ四捨五入すると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書いたり出来ると更に出来る仕事の幅が増えるよ。

まぁスレ違いだからこの話はここではこれ以上しないけどね。

終わった話を一々盛り返す自己中野郎

それが5chクォリティ

すみませんが、どうしてもわからないので教えてください。

Powerqueryで以下の式を”リストでの数値カウント”ではなくて、
原文そのままのテキストで表示したいと思っています。
どういう式を書けばいいのでしょうか？

「 ピボットされた列1 = Table.Pivot(削除された列1, List.Distinct(削除された列1[Source.Name]), "Source.Name", "Column1", List.Count)」
※上の式は「列のピボット」のGUIボタンで自動作成されました。

>>118
こういうこと？
たとえば、こんなテーブル：T_元表(一番左の青テーブル)があったとする。
ここでは「項目1：あ」−「項目2：a」の値が、「あああ」と「AAA」が重複している。


これをクエリーに置換えたのが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で、重複していない他行他列は文字列の「値」を拾ってくる。


何をやりたいのかよく分からないけど、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新規作成　�Aデータの取得-ﾌｫﾙﾀから　�B結合　�Cクエリエディタその他のクエリから「変換」列のピボット　�D数式バーでList.Reverseに変換とかを

情報小出しすみません。
データ取得からのデータ成形したいだけなので、クエリが得意とすることだとは考えています。

>>123
俺はエスパーじゃない。

ニュータイプでもないしなあ

俺はニュータイプのエスパーだけど無理でした

話題変わるけど、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
ダウンロード＆関連動画＞＞

@YouTube



とりあえずやってみたら、再度動き出すにはしたものの、
機械翻訳された大本営の説明がよくわからず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が原因で崩れてしまうことがわかり無事統合できました｡
今までは不要な行は手動で消していましたがフィルタ機能を使えばできましたね……

帳票上の使いたいデータが必ず所定のセルにあるなら、まだなんとかできる可能性はある。

>>156
ようわからんけど、

>1.
ピボットテーブルを参照「元」にすることは、経験的に避けている。
フィルターや構成変えるだけで指定した参照範囲が壊れてしまうから。
何かフィルターを変えたら104万行を超えてしまったとかも。
ピボットテーブルは参照元にはせず、それを最終出力とするかグラフに繋げるかだけにしている。
でなければGETPIVOTDATA関数を使ったこんな場合とか。
https://dekiru.net/article/4481/
さらにいじりたいのであれば、クエリーで絞るかPowerPivot内(DAX関数)でやるか。

>2.
クエリーを作る時、最後に「データモデルに追加する」にチェックを入れてワークシートに読込ませると
シート上のクエリーテーブルとは別に、
PowerPivotで扱われる「データモデル」としても同様のテーブルが作られると理解。
これがワークシート上限104万行を超えて扱えるヤツ(その場合は「接続のみ」で利用)。

当該クエリーも削除した後で、後者を削除するには、
(他のクエリーがあれば、そこをアクティブにするとメニューに「Power Pivot」が現れる)
　Power Pivot - 管理
で、PowerPivot上の当該シートタブを右クリックで削除。

ピボットテーブルの参照元データはpowerqueryから見られないの？
powerqueryで参照元データを直接いじれば良いのでは？

>>158
力技だけど、列の追加で売上100以下なら他、そうじゃないなら商品入れる。
あとはその列でグループ化

>>160 が言うとおり、グループ化でやってみた。

とりあえず成果品の画像。


1. 最初のQ_DataはテーブルT_Data(青)をクエリー化しただけ。(接続のみ)

2. 2つ目のQ_合計売上100超は商品でグループ化して、その売上合計を出し

フィルターで100超だけに絞ったもの。(接続のみ)

3. 3つ目のQ_合計売上100未満は、同様に、売上合計が100未満に絞って(商品BとD)、
その合計の答えをリスト形式で出せるから、

テーブルに変換、

行タイトルを"Column1"から2つめと同じ「カウント.売上 の合計」に変更、
「商品」列を追加して値を"その他"とし、
列順を2つ目と同様に並べ直した。(接続のみ)

4. 4つ目のQ_追加統合が2つ目に3つ目を追加したもの。(シートに読込み)

ちなみに「その他」の合計は180ではなく160の模様。

これなら二次利用の参照元にも出来るべ。

>>161 の追記
2.と3.のクエリーは1.を参照

>>161 の訂正
100超と100未満じゃ、「100」が抜けてまう...orz
そのあたりはよしなに。

質問させてください

折れ線グラフで毎日の温度データを表示しています
・基準値を超えた日に任意の文字を表示させる
・位置はデータに追従させる
ということはできますか
ヒントに入れてカーソルを合わせたら表示されるのではなく、常に表示した状態にしたいです

>>164
「任意の文字」とは何？

データラベルとして、日付や温度以外の、
元表中の「任意の何か」の列の値を
ラベルオプションの「セルの値」設定で表示すること？

であれば、PowerQueryというより、
VBAで系列と要素番号とかを指定して
データラベル表示させることになるのかと。

どっちかというとPowerappsとかの領域かもね。Powerqueryでは無理。

>>163
ありがとうございます！
素晴らしい！

某アプリから出力されたCSVファイルを使って、その右側に何列にも渡ってピボットテーブルに使用できる関数の入った
セルを作ったエクセルファイルがあります。
これに毎週追加されるCSVファイルのデータを追加していくのですが、当初、単純に作ったエクセルファイルに
追加CSVファイルの値だけをコピペして、右側の関数セルはオートフィルで追加すればいいかと考えていたのですが、
PowerQueryなる便利なツールでもっと効率的にできそうというのがわかりました。
質問は以下になります。

1.すでに作られたエクセルファイルの書式（テーブル化されてます）で、CSVの追加データをPowerQueryでインポートするにはどうすればいいでしょうか。
（各列のタイトルは追加CSVと全く一緒です）
2.PowerQueryにまったくの無知でも、PowerQuery上で関数の入った列も入れてしまった方が良いでしょうか。

色々ググってみましたがさっぱりわかりませんでした。
Amazonで参考書注文してきたので今後勉強していきますが、とりあえず上記がサルでもできるほど簡単なら教えてください。
よろしくおねがいします。

↓こういう状況です。

>>168
すみません、図の追加CSVにある左の番号は11から続く出力データです。

さらにすみません、これ実際は上から下の順ではなく、下から上方向の順で最新のデータが入ってます

>>168
1. 週ごとのcsvを(あるいはそれを読込んだExcelファイルを作って)、
同じフォルダに入れておく。

2. これらを通しで集計するには、別ブックから「データの取得 - フォルダから」で、
全てのファイルを結合出来る。
・「パラメータ」を使う「ヘルパークエリ」が自動作成されるが、
　カスタマイズはそんなに難しくない。
・参考動画
　ダウンロード＆関連動画＞＞

@YouTube


　ダウンロード＆関連動画＞＞

@YouTube


　ダウンロード＆関連動画＞＞

@YouTube


　ダウンロード＆関連動画＞＞

@YouTube


　等。
・このクエリーの中で「新しい順」にソートするステップを入れておけば良い。
・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の高度な分析機能の紹介【主要なインフルエンサー】
ダウンロード＆関連動画＞＞

@YouTube

>>176
ありがとうございます。
変数の選定は出来ますね。直接式を求めることは出来なそうです。
式は別途求めればいいのかなぁ。

お願いします
組織アカウントのPower BIワークスペース上に、いつからか他者が作成したレポートが表示されています
どうにかワークスペース内から表示を消したいのですが、方法をご存知の方いらっしゃいませんでしょうか

ExcelでPower Queryを触りはじめたのですが、
Table1とTable2それぞれの特定列が一致した場合に
一致した行だけを集めてTable3を作る、
といったことも可能でしょうか

それが基本機能です。

ありがとうございます！
引き続きリファレンス読んでやってみます！

>>179
マージで

>>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に戻ってしまいます。表示させたい事は年度関係なしの累計です。

1から10まで答えてほしいってこと？

アホすぎ

あ、分からないなら答えなくていいから

要件を書き込むとソリューションが出てくるスレです。

答えられないなら黙ってて

過年度最終累計を別のクエリーに蓄えといて、
過年度累計＋当年度日次データ
にしとけばいいだけの話でねぇーの？

それをどうやるか聞いてんだけど

1から10まで答えてほしいってこと？

相手にしないことにした

ぷっ、わかんないから教えられないw

教えられないのに知った顔して出てくるからこうなる

別に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の値、の様なことをしたいです。スライサーを軸別に設定するようなこと可能でしょうか？もしくは、データテーブルの作り直しから必要でしょうか。

PowerPivotでメジャー作成かな

属性を列にしてフィルター掛けるかな

>>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で色を変えることくらいしか
思いつかない。

>>230
背景透明にして2枚重ねれば？

PowerBIなんですが、カードで値を表示してフィルタしたときに、レコードがない場合(空白)って出てカッチョ悪いのですがこれを非表示にしたり別の文字に置き換えることって出来ますか？

>>233
DAX関数のALLSELECTEDとFILTER組み合わせてnullの除外する
と予想。(まだそこまで追いついていない・・・)

DAXのisblankを使う

詳しい人おるん？ここ

日付の列を作ったけど
日付の範囲で選択出来るようにするにはどうすれば良いんでしょ？

PowerBIなんですが、フィルタされたときに連動するグラフY軸の最小値や最大値を指定できますか？

なるほど経理が使うのに大事なのは
クエリーで読み込む時に
日付を元にして期首からの経過月の列を作っておくことなのか
EXCELはアメリカ産だからデフォルトが12月決算になってしまうのな

自然科学や工学系ではExcelをメインでは使ってないだろうけど、
分析するには、秒や分単位時系列のカレンダーは要るのかと。

ロガーからデータ取って散布図や相関図作るにしても、
データがNAのところも表記しないと、
おかしな事になりそうだと想像。

powerBIすごいな
散らばってるDBをそのまま使って集計できる

>>237
PowerBIなんですが、フィルタされたときに連動するグラフY軸の最小値や最大値を指定できますか？

はい、Power BIではフィルタリングされたときに連動するグラフのY軸の最小値や最大値を指定することができます。以下の手順で設定できます。

レポートの編集モードに移動します。
フィルタリングされたいデータを選択し、フィルターを適用します。
フィルタリングされたグラフを選択します。
右側のプロパティウィンドウで、軸の設定をクリックします。
「軸の最小値」または「軸の最大値」をクリックし、値を入力します。
「変更を保存」をクリックして、変更を適用します。
これで、フィルタリングされたときに連動するグラフのY軸の最小値や最大値を指定することができます。

>>233
そういう拘りは捨てたほうがいい
トリックが増えすぎてメンテ困難になる

マージする時にキー列が一意でない場合、左×右＝で行、レコードが増殖するバグは解決不可能でしょうか？

そもそもそれバグか？

>>244
申し訳ありません
左×右の左が多い場合、左の行分右の行が増えるというのは仕組み上分かります
しかしながら左の行が増幅する仕組みがまだよく分かっていません

バカにつける薬はありません

すげえの来ちゃった

キー列が一意でない、
つまり、重複しちゃってるんだよね？
それでどうやって名寄せしろと？

VLOOKUPでもできないかと。

重複の削除はなぜTable.Bufferがデフォルトで用意されていないのでしょうか？
重複の削除で削除対象に選ばれる行、レコードになにか法則性はあるのでしょうか？

Table.Bufferと
グループ化からのインデックス追加
する方法で重複を削除する時に何か違いはありますか？
グループ化からインデックスを追加する方がステップ数が増えて処理が多くなりそうな気がします

Table.Bufferって重複削除するの？

>>251
重複する列があって、その隣に日付順やインデックスの列があったとして
日付やインデックスを降順に並び替えた後に重複を削除したとしても
その並び替えは無効でなんらかの規則性に基づいて重複行が削除されます
しかしTableバッファを組み込むと並び替えられた状態で一番上の行だけが残ります

この方法とグループ化→インデックス列追加→0だけフィルターする
の違いは何かなと

Table.Buffer方式は内部実装任せ
グループ化→インデックス列追加方式は自分で完全制御したい

>>253
ありがとうございます
助かります

Table.Buffer と Round.AwayFromZero は内部でデフォでやって欲しいよなー

Table.Bufferは、ストリーミングというpowerqueryの基本戦略に反するから、内部的にやることは絶対に無いと思う

初歩的な質問になるとは思いますが
ブックを読み込んだ時に10万行も読み込まれてしまう時があります
これは一番下の行まで0か何かしらの値が入力されているからなのでしょうか？

>>257
元ブックの、テーブルでなくてワークシートを読込んでいるの？

元ブックのワークシートで、本来読込みたい行数は何行？
元ブックの当該ワークシート開いて、Ctrl+Endで最右下行に移動したら、何行目まで行く？

>>258
なるほど
テーブルではなくブックやワークシートそのものを読み込んだ場合に何もない空白の行が読み込まれてしまう場合があるという事ですね
それは盲点でした


クエリのマージに頼るよりもリレーションシップで出来る事は極力（最大限に）リレーションシップで済ませた方が動作は軽いですか？

powerqueryでリレーションシップて何？

>>260
ピボットテーブル（？）でしたね！

全てのデータがデータベースに（一行、1レコード）揃っている状態が正規化されていない状態、あるいは第一正規化で
これ以上ないくらいに重複を排除した、それぞれ別のデータベースにしたのが第三正規化ですか？

>>259
実データが数万行程度までなら、
ピボットテーブル（PowerPivot）でのリレーションでも
PowerQueryのマージでも速さは同じぐらいの印象。

104万行超とかの大きなデータを扱うんだったら、リレーションの方が速いか？
ようつべで比較動画上がってたような？

ただ、ピボットテーブルにすると、その後はグラフにするしかなく、
ピボットテーブルのまとめ方が使えそうだったら、最後の段階に使っている。

まだ加工や二次利用が続くんだったらPowerQueryにしている。

>>263
そうですよね
リレーションシップはピボットテーブルで活用するしか使い道はないですよね（？）
結合に比べると自由度が低い（？）

>>262
第一正規形は繰り返し項目の排除
つまりフィールドの数が同じ形に整えたもの
第三正規形は大体あってる

>>265
ありがとうございます

第三正規化はこれ以上ないくらいに細分化している
第二正規化は複数の状況証拠（条件）があるから推移的関数従属が決定する
第一正規化はデータベース
ってコト、、、？

>>267
第一正規化はフィールドを定義するために必要
買い物した人のレコードに商品1、商品2、商品3、…って無限にフィールドを作るのを防ぐ
第二正規化で重複データを分離
経理システムかなにかのCMでやってる、何度も同じデータを入力する必要がなくなる
第三正規化し第三正規形にすることでマスタデータとトランザクションデータを完全に分離できる
非正規形含む正規形はすべてデータベース
今は第六正規形まで定義されてる

>>268
ありがとうございます
今までデータベースなんて考えた事はあまりなかったです

二つの表があって変更された行を表示させるのはやはりパワークエリでしょうか？

>>270
数百行程度なら、チェックする列数にもよるけど、ワークシート関数でも可能かと。
ただ、基準表と比較対象表をいろいろ入れ替えるんだったら、
ちと面倒かも。

行数が万単位なら、PowerQueryかな。
同じ表形式なら、基準表と比較対象表の入れ替えも楽だし。
20列とかの全列チェックするんだったら、
クエリー更新もそれなりに時間かかると思う。

>>271
ありがとうございます

https:linmoa.net
https://oraksil.cc/
https://todaync.com/

Microsoft Jpan destroyer

共通の処理まで進めて処理を分岐させようと思います。
そのときに、「複製」を使って処理を分岐させるのと
コピーを使って処理を分岐させるのでは複製の場合はバグが発生しますか？
参照だと分岐元に変更を加えない限りはエラーが発生しませんか？

パワークエリで消費メモリを削減するためにはどの様なポイントがありますか？

ステップの早い段階で不要な列や行を削除する

>>276
ありがとうございます

ちなみになのですが、たった3000行未満の表をList.Containsでフィルターしたり、マージしたりした後に2つ複製をして複製したクエリをそれぞれステップを書き加えてもエラーは起きませんよね？

複製？参照じゃなくて？

>>279
はい
複製です

私は複製のことをコピーそのものだと勘違いしておりました
複製では依存先（？）依存関係にあるクエリまで複製されないので複製したクエリを実行しても動かないのですね

参照で別のクエリを作成し（わかりにくい表現かもしれませんが）作成したクエリに追加の作業を行うと参照元のクエリが書き換わる、変更されるものだと勘違いしておりました

複製ではなくコピーの方がいいのですね

データソースであるexcelファイルを参照してるんだけど
上書きしても同じファイル名なら更新すれば読み込んでくれる？

>>281
参照する中身の構造が同じなら、
個別セルの値が変わっていても
レコード数に増減があっても
読み込むけど。

フォルダからファイルを接続して読み込んでいましたが不要になったので該当するクエリを削除し、フォルダを削除しました
しかしクエリを更新する時にファイルがありませんとエラーメッセージが表示されます
どうしたらいいでしょうか？

>>283
もしかして、そのクエリー、
「読込み先」の時に、ワークシートにテーブルとして読込んだだけでなくて、
「このデータをデータ モデルに追加する」にもチェック入れて読込んでない？
なので、テーブルとクエリーを削除しても「接続」が残っているのかと。

よく分らないけど、
メニューバーの「データ」から「クエリと接続」の右ペインで、
「接続」のタブに何か残っているようだったらそれを削除、
あるいは、メニューバーに「Power Pivot」が出るんだったら、
そこの「管理」開いて、何らかのスプレッドシートみたいなのがあれば、タブで削除、
するとか？

>>284
ありがとうございます
データモデルに追加する
にチェックを入れると動作が軽くなるとか早くなると聞いていたので何でもかんでも追加していました

ファイルをもう一度用意する

頻繁にメモリ不足エラーを吐かれるんだけど一体なんなんだろうか？
私がデータモデルに追加しているからなんだろうか、それともデータソースを追加して、そのまま編集して、そこから次に読み込むで接続オンリーにしているからなんだろうか？

クエリのコピーや参照もいいですが、
ファイルそのものをコピーして分けて作成するのもいいですね

「データモデル」と「ワークシートにテーブル」の両方読込むと、
メモリーの制約からか、ワークシート側のソート、
特に複数列ソートが思い通りにならなくなったりしない？
104万行以上もワークシートには当然読み込めないし。

なんとなく、
・リレーションシップやDAX関数使わないんだったら、「データモデル」には追加しない。
・使うんだったら接続のみ（それでもソースとして参照したクエリーはワークシートにテーブルとして落とせたような・・・）
にしてるんだけど。

ワークシートに出力しているクエリをデータモデルに追加するとバグを起こす可能性があるのですね

サーバーから吐き出されるCSVのファイル名を変えられちゃったんだけど
powerqueryのクエリで違うファイル名に変更できる？

できる

パス変えるのと同じ

サンキュー
調べてみる

>>291
中身自体が違えば難しいのでは

カラムが違ったら無理だわなｗ

欧州の電力需給実績リアルタイム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,{"キー列"})
試してみて

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


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

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

ちなみになのですが昨日サイトで完全外部結合した後にキー列をカスタム関数の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負荷率とメモリー消費量がもっと跳ね上がって、ちょっと時間が掛かります、
　出来てしまえば、スライサー切替えによる再計算は速いです・・・

って話。

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

外せるよ。

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

>>324
ありがと！

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

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

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






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

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

よーく、覚えとけ。

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

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

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

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

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

かまってちゃんはお断り

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

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

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

どゆこと

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

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

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

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

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

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

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

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

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

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

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

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

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

とか。

やっぱり、出たよ

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

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

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

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

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

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

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

あ、出来そうだわ

初歩的な質問かもしれんのですが、この動画ダウンロード＆関連動画＞＞

@YouTube

　見ながら勉強しててドリルダウンまで行ったんだけど何故か綺麗に左からGDP順でドリルダウにならず…国名の五十音順になってしまうのって何故だと思う？

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

総務省が発表している年次都道県別の年齢階層人口データ（Excelブック）を
2段の関数クエリ（シート別・男女別）を使ってリストに変換する説明

@YouTube


関数クエリの使い方をコンパクトにまとめていた

powerBI詳しい方いますか？

いない

BIは、
・自分が欲しいグラフは作れなかった
・Web共有するには結局有料版が必要
などで挫折した。

Excel版Power Queryの範囲内で、
Webスクレイピンクやパラメータークエリーくらいまでなら、
なんとか答えられるかも。

ホントそれ
共有の為だけに有料版入れてくれなんて言えないしな

>>358
詳しいよ

>>362
基本操作以外のおすすめの本ありますか？
もしくはサイト
売ってる本って基本動作だけでやりたいことが載ってないので困ってます
例えば
・スライサーで選択したデータの画像だけを表示したい
・そもそも画像の取り込み方はどうやるのか
・同じビジュの複数のグラフの差異を表示したい

などです

>>363
他のETLツール→PowerQueryだったから、本は読んでない
なんか、PowerQueryの用途を根本的に間違えてる気がする。これはデータを成形するためのツールなので、相手にするのはRDBやcsv、Excelのテーブルだよ

>>363
質問内容はPower Queryに関するものではなくて
IMAGE関数とPower Pivot、グラフの問題

ようつべのExcelドカタあたりがいいんでね？
あそこはpowerqueryだけでもないし。
ただ、vbaは扱っていなかったかと。

YouTubeはあまり参考にならないとの書き込みあったがな

>>364
返事が遅くなりました
画像はPowerBIでマインドマップを作るために必要だったんです

>>368
PowerBIの質問だね
ググったら出てくるけど、直接データとして取り込むことは出来なくて、web上やSharPoint上の画像ならリンクの文字列、ローカルの画像ならエンコードした文字列をデータとして食わせる

状況としては後者な気がするけど、たぶんめんどくさいよ
フィルターしたいだけだったら、NotionやConfluenceのデータベース機能を使うか、Excelでも十分かもね

- Webからcsv等で取得した複数項目・複数地点の30分値データを、
　・過去から最新リアルタイム値まで、
　・月単位、週単位、日単位の30分刻みグラフ表示で、
　素早く行き来したい。
- 2016年度から～2024年度前月月末まで（2016/4～2024/9末）のPastDB-30分値は、
　ローカルのcsvファイルをダウンロード済みで、1,937,520行ある。
　データモデルで更新するのに2分程度かかる。
　基本的に一度読み込めば、遡及修正がない限り、更新する必要はない。
- 至近のデータ（LastDB）はWebのcsvファイルをDLせずに直接クエリーで読み込むが、
　最大月31日×直近2ヶ月分としても、2,976行でしかない。
- 統合して運用したいが、Power Pivotのフィルター変更するたびに2分以上も更新にかかるのは避けたい。
- PastDB、LastDBとも「接続のみ」のクエリーのままにしたのでは、更新に時間が掛かるので
　・それぞれデータエリアに格納し、
　・これを繋げるクエリーを作って、これもまたデータエリアに格納して、
　これをPower Pivotのデータ元にしたら良いんだべか？

copilot に聞けば

>>372
結果：
・読み込み済みのデータに対しては、1週間分絞込みのピボットテーブルでは
　フィルター（スライサー）変更で気持ちよく動く。
・最新のWebデータをLastDB経由で取り込むために、結合クエリを更新すると、
　やっぱりPastDBも最初から読み込み、合計で190万行超あるので
　更新に2分＋αかかってしまう。

Copilotが「PastDBの最後にTable.Buffer入れてみ？」と提案してきたが、
メモリー26GBも食うわ、CPU使用率90%前後に張り付くわで、
ちっとも速くならない。

なんでも、Power BI版Power Queryには、
個別のクエリ プロパティに「このクエリを更新しない」オプションがあるんだけど・・・
とか言っていた。

データの並び順がPower Query エディターで指定した順にならない場合の対応方法はどうしていますか。よろしくお願いいたします。

>>374
っTable.bufferかTable.stopfolding

>>375
レスありがとうございました。参考にさせていただきます。

>>376
別にあんたのためじゃないんだからね。
自分も困ってたからレスしただけなんだからね(｀･ω･´)

BI でX軸にしている時系列が例えば8月、7月と逆転している場合はどうしたら直りますか？

上記の件判明したので大丈夫ですお騒がせしてすみません。

時代はマクロ（笑）よりパワークエリだな！

何で広まらないんだ？

機能の存在すら知らない人が多数だから
ExcelスレにもExcelって全然進化がないってレスがあった

接続文字列がよくわからん文字になるのが嫌
SharepointのExcelからadoでデータ取り出そうとしても上手くいかないから
パワーQの接続文字列をパクろうとしたら
変な文字列になってヘコー

>>380
Power QueryとVBA組み合わせるねん。
PQにはブックの保存とか、
ファイル操作機能なんかはないさかい。
なんだったらPythonも組み合わせるねん。
対話型のWebサイトで条件指定してスクレイピングするとか、
高度なグラフ機能は、
PQやExcelにはないねん。

>>384
あと、WebサイトがCSSの作りだと、
PQでは厳しいわな。

>>382
それだね。ベテラン程昔のExcelの知識のまま止まっている。
面倒臭いとか余計な機能だの言ってさ。

なんで公共の場で地方言葉なの？

公共の場？
アホかいな
茨城弁にすっか？

東京弁はご遠慮下さい

使う言葉に惑わされて、
中身を見ない／見ようとしない／分からないアホへの
最大の皮肉

>>387
時代はとうに多様性　右へ倣わないで
って工藤静香も言ってる

新しい機能に否定的な老害Excelユーザーwww

ネットから30分間隔で更新されるようなデータを取り込んで
1ブックにシーケンシャルなクエリーが20個くらいで処理する時、
途中のクエリーをちょっと修正して全て更新かけると
下流のクエリーにエラーが出て
糞詰まりみたいに止まってしまうことがある。

修正に齟齬がなくても、
上から順に詳細エディターで一つ一つクエリー開いて
辛抱強くプレビュー更新していくと、解消されるのだけれど。

むぅ・・・

M式言語の週番号を求める関数
　Date.WeekOfYear([年月日], Day.Monday)
は、オプション指定で月曜始点にできても、ISO 8600基準の、
・その年の第1週は、1月4日を含む週
　（あるいは「最初の木曜日を含む週が、その年の第1週である」）
にならない。

Excelワークシート関数の
　WEEKNUM(年月日, 21)
なら、月曜始点でISO8600準拠になり、
2024/12/31はちゃんと「2025年の第1週」になるのだけれど。

Copilotに相談したら
「こういうカスタム関数にしたら？」と案の提示を受け、
何度か修正のやりとりをしたものの、なかなかの苦戦ちぅ。

2024/12/31が「2025年の第1週」になっても、
今度は2025/1/2(木)が「2025年の第2週」になってしまう。

そのまま年越ししてもうた。

今のカスタム関数 fx_ISOWeekOfYear の暫定案：

AdjustedWeekNumber で4つの場合分けをして調整をかけているが、
どうしても2025/1/2(木)～5(日)が、「第2週」になってしまう・・・
ISOWeekNumber で切り捨ての丸め計算を入れているが、ここもこれで良いのかしら？

======
// ISO 8601週番号を計算する関数
(date as date) as number =>
let
// その年の最初の木曜日を取得
FirstThursday = Date.AddDays(Date.StartOfWeek(Date.StartOfYear(date), Day.Thursday), 3),
// その年の最初の木曜日を含む週の月曜日を取得
FirstISOWeekStart = Date.AddDays(FirstThursday, -3),
// 日付から年初の月曜日までの日数差を計算
DaysSinceFirstISOWeekStart = Duration.Days(Date.From(date) - FirstISOWeekStart),
// ISO 8601基準の週番号を計算
ISOWeekNumber = Number.RoundDown(DaysSinceFirstISOWeekStart / 7) + 1,

// 年末および年初の日付が適切な週に含まれるかどうかを判定
AdjustedWeekNumber =
if (Date.Month(date) = 1 and ISOWeekNumber > 52)
then 1
else if Date.Month(date) = 12 and ISOWeekNumber >= 52 and Date.DayOfWeek(Date.AddDays(date, 1), Day.Monday) <= 3
then 1
else if Date.Month(date) = 1 and Date.Day(date) <= 4 and Date.DayOfWeek(Date.StartOfWeek(date, Day.Monday), Day.Thursday) = 1
then Date.WeekOfYear(Date.AddDays(date, -7), Day.Monday) + 1
else ISOWeekNumber
in
AdjustedWeekNumber
=====

グーグルGeminiに聞いてみた

// 年月日を日付型に変換（必要であれば）
let date = #date(2024, 12, 31);

// その年の1月4日の曜日を数値で取得
let firstThursday = Date.DayOfWeek(#date(Year.From(date), 1, 4));

// 1月4日が木曜日より前の場合、前年の最終週となる
let weekNum = if firstThursday < 5 then
Date.WeekOfYear(#date(Year.From(date) - 1, 12, 31), Day.Monday)
else
// 1月4日が木曜日以降の場合、その週から週番号を計算
let adjustedDate = Date.AddDays(date, 5 - firstThursday); // 最初の木曜日へ調整
Date.WeekOfYear(adjustedDate, Day.Monday) + 1;

>>397
ありがとう。
まだ理解しきれていないけれど、
どうもCopilot案はGemini案と比べて
「その年の」の扱いが雑なような・・・

powerqueryに限らず日付処理はifで条件分岐し始めたら失敗と考えてる

>>399
閏年判定

聞き方を改めてみた。

Q. ExcelのPower QueryにおけるM式言語にて、ある日付に対してISO8601に準拠した週番号を求めるカスタム関数を教えて下さい。
具体的には「その年の第1週は、1月4日を含む週である。」というものです。

===Copilotの再回答例===
(Date as date) as number =>
let
// 年の始まりの日付を取得
StartOfYear = Date.FromText(Text.From(Date.Year(Date)) & "-01-01"),
// 年の第4日を取得
FourthOfJanuary = Date.AddDays(StartOfYear, 3),
// 第4日が含まれる週の開始日（ISO週は月曜日開始）
StartOfFirstWeek = Date.StartOfWeek(FourthOfJanuary, Day.Monday),
// 指定された日付の週の開始日
StartOfCurrentWeek = Date.StartOfWeek(Date, Day.Monday),
// 第1週からの週番号を計算
WeekNumber = Number.RoundDown(Duration.Days(StartOfCurrentWeek - StartOfFirstWeek) / 7) + 1
in
WeekNumber
======

===ChatGPIの回答例===
// ISO 8601準拠の週番号を計算するカスタム関数
(let
GetISOWeekNumber = (inputDate as date) as number =>
let
// その年の1月4日を含む週の開始日を取得
year = Date.Year(inputDate),
jan4 = #date(year, 1, 4),
firstThursdayOfYear = Date.AddDays(jan4, 3 - Date.DayOfWeek(jan4, Day.Monday)),
startOfWeek1 = Date.AddDays(firstThursdayOfYear, -3),

// 入力日付の週番号を計算
daysSinceStart = Duration.Days(inputDate - startOfWeek1),
weekNumber = Number.IntegerDivide(daysSinceStart, 7) + 1
in
weekNumber
in
GetISOWeekNumber)
======

GPIにしてた...orz

これだと2024/12/30(月)、2024/12/31(火)等は
「2024年の第53週」となって、週カレンダーとしては年越しが分断されてしまうか・・・

「2025年の第1週」扱いにするよう、
もう一回聞き直してくる。

テストを先に組んだ方がいいんじゃないのか？
紀元前から1000年先まで、単純に総当たりするとか

ChatGPTとも何度かやり取りして条件を再整理

1. ISO 8601準拠で
　・月曜基準
　・その年の第1週は、1月4日を含む週
2. (特注仕様) 年跨ぎで7日間が揃わない年末の数日は、翌年第1週として繰り上げ
　例：2024/12/30(月)、2024/12/31(火)などの場合

で、最終的に希望がかなったChatGPT回答例 (ご丁寧にlet in入れ子で、YearとWeek(週番号)の2つセットのrecordが出力する仕様なので、展開が必要)
======
// ISO 8601準拠で月曜日開始の週番号を計算するカスタム関数
let
GetISOWeekNumber = (inputDate as date) as record =>
let
// 入力日の年
year = Date.Year(inputDate),
// 入力日の属する週の判定用データ
jan4CurrentYear = #date(year, 1, 4),
firstMondayOfCurrentYear = Date.AddDays(jan4CurrentYear, -Date.DayOfWeek(jan4CurrentYear, Day.Monday)),
jan4NextYear = #date(year + 1, 1, 4),
firstMondayOfNextYear = Date.AddDays(jan4NextYear, -Date.DayOfWeek(jan4NextYear, Day.Monday)),
// 前年の第1週開始日を計算
jan4PreviousYear = #date(year - 1, 1, 4),
firstMondayOfPreviousYear = Date.AddDays(jan4PreviousYear, -Date.DayOfWeek(jan4PreviousYear, Day.Monday)),
// 週番号を計算するための基準年の決定
adjustedYear =
if inputDate >= firstMondayOfNextYear then year + 1
else if inputDate < firstMondayOfCurrentYear then year - 1
else year,
// 基準年に応じた1月4日と第1週の開始日を取得
adjustedJan4 = #date(adjustedYear, 1, 4),
adjustedFirstMonday = Date.AddDays(adjustedJan4, -Date.DayOfWeek(adjustedJan4, Day.Monday)),
// 週番号を計算
daysSinceStart = Duration.Days(inputDate - adjustedFirstMonday),
weekNumber = Number.IntegerDivide(daysSinceStart, 7) + 1
in
[Year = adjustedYear, Week = weekNumber]
in
GetISOWeekNumber
======