【Excel】Power Queryを語るスレ【Power BI】

1 ：名無しさん＠そうだ選挙にいこう：2019/10/20(日) 11:59:28.17 .net

Excel 2016から標準装備、Excel 2010以降も追加可能なPower Query。
使ってみると驚く部分は多々あれど、普及したとは言い難い。

手探りで分かったこと、英語の資料から判明したこと等、いろいろ語って下さい。
Power Pivot、Power View、Power BIについても歓迎。

公式　「概要と学習」 (日本語)
https://support.office.com/ja-jp/article/Power-Query-%E6%A6%82%E8%A6%81%E3%81%A8%E5%AD%A6%E7%BF%92-ed614c81-4b00-4291-bd3a-55d80767f81d

Excel 2010、2013用アドイン (日本語)
https://www.microsoft.com/ja-jp/download/details.aspx?id=39379

公式　M言語レファレンス (英語)
https://docs.microsoft.com/en-us/powerquery-m/index

「Power Query メモ」 (外部サイト：M言語レファレンスの日本語化等)
https://sites.google.com/site/powerquerymemo/

2 ：名無しさん＠そうだ選挙にいこう：2019/10/25(金) 01:10:27.86 .net

ワークシート上の名前定義した特定セルをPower Query内で参照する方法。
「テーブルまたは範囲から」データの取得をするのと似ている。

PowerQuery use a cell values in a URL
https://stackoverflow.com/questions/49281763/powerquery-use-a-cell-values-in-a-url

定義名を宣言して
M言語内引数のパラメータ部分にその定義名を置き換える。

3 ：名無しさん＠そうだ選挙にいこう：2019/10/25(金) 01:18:45.31 .net

テーブル内の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}})

4 ：名無しさん＠そうだ選挙にいこう：2019/10/25(金) 02:36:24.34 .net

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[外部表の集計対象列])))
みたいな？

=> <= は「順次代入する」の意味？
""で囲まれた列名のアタマに付いた#は、列名にブランクが含まれている場合の特殊記号？

5 ：名無しさん＠そうだ選挙にいこう：2019/10/25(金) 17:57:32.47 .net

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

6 ：名無しさん＠そうだ選挙にいこう：2019/11/03(日) 12:27:45.07 .net

�@同じフォルダ内に複数のEXCELファイルがある。
�AそのすべてのEXCELファイルに全く同じ構成のテーブルがある（テーブル名、カラムの定義も一緒）。

そのすべてのテーブルをマージしたいんですが、ソース取得のコードってどう書けばいいでしょう？

7 ：名無しさん＠そうだ選挙にいこう：2019/11/03(日) 17:16:19.22 .net

>>6
行数を除いて、同じフォーマットの表であれば、
読み込むときに先に結合させては？

入り口は、このあたりも参考になるかと。
ExcelでPowerQueryを使ってデータ収集分析
https://qiita.com/mosugi/items/71df310b35db81722d1e

「データの取得−ファイルから−フォルダ−から」入って
フォルダーパスを指定したら、「結合」ボタンも出る。
その中のメニュー「データの結合と変換」を選ぶと
読み方(xlsxファイルだと、どのsheet/範囲/テーブルか、余計な行は削除するか、ヘッダーはどうするか、カスタム列の追加、列の型等)
をサンプルで指定できそう。

※ たぶん、エディターで自動生成される「サンプル ファイルの変換」で読み方を指定するのかと。
Power Query内に自動でパラメーター関係のフォルダーやクエリー等が生成されるけど、
このあたりの仕組みは、当方まだ解明できていない。

これをやってから読み込めば
出来上がるクエリーが1つになり、以後の扱いが楽になるかと。

ただし、
1. xlsx表内の目的のsheet名が異なっていると、面倒かも？
同じテーブル名だったら直接指定できるかもしれない。
2. 目的のフォルダーにさらにフォルダーがあって、別置きのものが入っていたりすると、
それも読み込んでしまう模様。

8 ：7：2019/11/03(日) 17:18:19.58 .net

>>6
で、コードは >>7 で出来た詳細エディターを参考に。

9 ：名無しさん＠そうだ選挙にいこう：2019/11/27(水) 15:26:03.51 .net

Power Pivot使ってる人ってどの位いるんだろう?
自分は何とか使えるようになったけど、セミナーに出席しても使えてそうな人殆どいなさそう...

10 ：名無しさん＠そうだ選挙にいこう：2019/11/27(水) 16:04:13.51 .net

>>9
日本語の解説書籍やWeb記事がまだ少ないからねぇ・・・

セル結合とかで、紙の帳票っぽく仕上げるとか、
IFの入れ子が多数な、複雑な関数を組んで作業列をなくすとかで、
見た目をよくすることばかりに血道を上げるより、
まず、テーブル形式(≒クエリー)がどれだけ便利かに気付いて貰えばと。

これ使うと、出来ることがだいぶ増える。
他の人がステップ(マクロ)の仕組みを理解するのは、
VBAよりも比較的わかりやすいし。

WebスクレイピングではPhythonより出来ることは限られるけど、
敷居の低さ、わかりやすさでは、こっちの方が良さそうに見える。

ビッグデータと呼ぶのが正しいかどうかは知らんけど、
特に、いろんなWebサイトで公開されてるデータ処理。

計算がバッチ処理・カスケード処理だから、
何十万行でも、普通のPCで出来るし。
VBAやPythonとの補完も出来そうだし。

11 ：名無しさん＠そうだ選挙にいこう：2019/11/28(木) 00:59:16 .net

パワーピボット使ってる。カラムの異なるデータがみるみる集計できるから多用してる。

でもあれも変なところで物足りなくて、時間表記でセル書式の[h]:mmなんかがサポートされてなかったり。

12 ：9：2019/11/28(木) 10:12:04.47 .net

>>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では考えてたけど。
今度試してみよう。

13 ：名無しさん＠そうだ選挙にいこう：2019/11/28(木) 23:08:44.86 .net

>>12
そう。
「メニュー−データ−データの取得と変換−Webから」
でURLを指定するヤツ。

Power QueryではPythonみたいに、
Web画面がテキスト入力させたりとかボタンを押させたりのものだとタイヤ雨出来ないけど、
複数ページあって、URLがページ数を反映しているものだったら、
対応できる模様。
こんなのとか。

[Power BI / Excel] 複数にまたがる Web ページからデータを取得する
https://road2cloudoffice.blogspot.com/2017/07/power-bi-excel-web.html

14 ：名無しさん＠そうだ選挙にいこう：2019/12/12(木) 18:38:41.37 .net

2016vbaでcsvから作ったクエリのrefreshallしても更新されないことがある？
うちの環境で一旦endしても更新されない時があるのは謎。
ボタン分けて継続の作業をしていますが、その間に手動ですべて更新しています

15 ：名無しさん＠そうだ選挙にいこう：2019/12/12(木) 20:14:38.01 .net

>>14
vbaを噛ましてないけど、Office soloのExcelで、
1つのcsvから多段にクエリーを展開していくとき、
「すべて更新」で上手くいかないことはある。
正直、謎。

自分なりの回避方法は、

1. シーケンシャルに再計算させたいクエリーはそれぞれのプロパティで
　「バックグラウンドで更新する」：OFF
　「すべての更新でこの接続を更新する」：ON
https://i.imgur.com/ZBFAHRz.png

2. 「クエリと接続」の一覧で上から順番に並べる。

3. クエリーテーブルをsheetに置いてある場合は、
　計算順に左からsheetのタブを並べ替える。

これでも「最後の再計算が終わった」と思っても、
まだ中間のクエリーの1つが再計算しだしたりする・・・

保険で「すべて更新」は2回することにしている。

MSに提案してもいいかも、
もっと確実にしてくれと。

16 ：名無しさん＠そうだ選挙にいこう：2019/12/12(木) 20:21:32.88 .net

>>15
ありがとうございます
同様の事象があることがわかり安心しました。
仕事で使えると思って頑張って学習しています。
確認しながら進めて聞きます。

17 ：名無しさん＠そうだ選挙にいこう：2019/12/18(水) 10:10:52.07 .net

外部データ取り込みで他EXCELファイルのテーブルを読みに行く時、
そのテーブルのカラムのデータ型はanyで、nullも文字列も小数点以下の数値（正数・負数）も含むんだけど、
Table.ExpandTableColumnのタイミングで小数点以下が丸められてしまう。

追っていくと、SampleFileでファイルを呼び出した時点で丸められてしまっているようだ。


なった事あるって人居ませんか？

18 ：名無しさん＠そうだ選挙にいこう：2019/12/18(水) 11:33:18.06 .net

自己解決したので勝手に語る。

読み込み元ファイルの問題だった。
でも謎が残る。

元データのテーブルの該当データは通貨型。単純な掛け算式が入っていて、丸め処理はしていないから、セル表示では丸められているが、内部的には小数点以下を持っている。

これを、表示設定で小数点をセル表示上で見えるようにしてあげたら、クエリの結果に小数点以下が表示されるようになった。

因果はわかったけど、仕様が謎。
データ型変更、ステップのどこにも入ってないのにね。


他、関連するかどうかもわからないけど、勝手に型変更をしているステップがあって、Int64になってた箇所があった。この型も小数点以下の取り扱いが無く、丸められるので注意が必要。

19 ：名無しさん＠そうだ選挙にいこう：2019/12/18(水) 17:20:14.25 .net

>>18
thx.

取り込む時、手動で全列の書式は指定する。
M関数で日時を扱う時も、元の書式が違うと、
違う関数になるし。

20 ：名無しさん＠そうだ選挙にいこう：2019/12/21(土) 00:08:19.02 .net

読み捨てで読み込んだデータでは直接ピボット作れないのね
何十万行もあるデータファイルのデータ、容量を軽くしようて思ったけどダメだった

データモデルで読み込んでも半分も軽くならない

21 ：名無しさん＠そうだ選挙にいこう：2019/12/21(土) 11:44:24.58 .net

yahoo知恵袋に、「読み込み先を”データモデルへの読み込み”に変更して,ブッククエリのみを作製するとそのまま『PowerPivot』で読み込みできます」とあったのを見つけたんだけど、自分では未検証です。

↓ソース
Power Queryを使って、２００万行ほどのデータを扱うことはできませんか？Power Pivotでの重複の削除の仕方がわからず、Power Query… - Yahoo!知恵袋 https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q11154335451?fr=and_other

22 ：20：2019/12/21(土) 16:24:04.15 .net

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

23 ：名無しさん＠そうだ選挙にいこう：2020/01/18(土) 14:22:44 .net

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

24 ：名無しさん＠そうだ選挙にいこう：2020/01/26(日) 20:29:57.40 .net

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

25 ：名無しさん＠そうだ選挙にいこう：2020/01/26(日) 21:57:03.50 .net

あれはクエリエディタで汎用の関数やクエリを作るときの道具だと思う
Power BIのレポートのパラメータ編集なんかやるときに使うものでしょ

26 ：名無しさん＠そうだ選挙にいこう：2020/05/28(木) 23:39:01.02 .net

容量の大きなテキストから2つのテーブルを作るとき、今はクエリを2つ作ってそれぞれ実行するので、2回データの読み込み待ちがあるのが気になります。
読み込み1回で出来るよとか、なにか時間を短縮する方法を知ってる人がいたら教えて下さい。

27 ：名無しさん＠そうだ選挙にいこう：2020/07/28(火) 07:38:56 .net

pdfファイルも読み込めることを今頃気付いた。

Webページの特定項目をXPath指定でもして読み込めないかしら？

28 ：名無しさん＠そうだ選挙にいこう：2020/07/30(木) 18:39:44.77 .net

これ読んで、辿るのもしんどいけど
Webページの構造自体が変えられちゃったら、正直ツラい・・・

Web Scraping In Power BI And Excel Power Query
https://datachant.com/2017/03/30/web-scraping-power-bi-excel-power-query/

29 ：名無しさん＠そうだ選挙にいこう：2020/07/30(木) 22:21:58.49 .net

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

https://seotoolsforexcel.com/xpathonurl/

30 ：名無しさん＠そうだ選挙にいこう：2020/08/28(金) 10:53:58.93 .net

（備忘）
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なので、
元のクエリーがプロパティで「バックグラウンドで更新する」のチェックが外れていても
チェックが入った状態と同等で実効される(？)ため、
シーケンシャルに計算が実行されないのか？

31 ：名無しさん＠そうだ選挙にいこう：2020/11/26(木) 23:17:12.97 .net

エクセルのpowe queryに関してです。 Table1
A列
3
1
2
Table2
A列　 　B列
リンゴ　 1
イチゴ　 2
バナナ　 3
結合して
Table1へ
A列
バナナ
リンゴ
イチゴ
このようにしたいです。 お知恵をかしていただけないでしょうか。

32 ：名無しさん＠そうだ選挙にいこう：2020/12/02(水) 08:51:17.92 .net

>>31
今ひとつ質問の趣旨が読み取れないのだけれど、
Table1-A列、Table-B列の数値は何？
品目コード？　数量？

それとは別に「品目の表示順」を一定にしたいのだったら、
A列：表示番号、B列：品目のTable3
を別に作って、これに結合してソートかけちゃうけど。

33 ：名無しさん＠そうだ選挙にいこう：2020/12/08(火) 23:35:19.08 .net

https://i.imgur.com/jQ0a2tJ.jpg

34 ：名無しさん＠そうだ選挙にいこう：2021/03/03(水) 14:53:17.77 .net

【Microsoft】Excel関数ベースのプログラミング言語「Microsoft Power Fx」登場　オープンソースで公開予定 [少考さん★]
http://asahi.5ch.net/test/read.cgi/newsplus/1614745763/

ノーコードらしい

35 ：名無しさん＠そうだ選挙にいこう：2021/03/06(土) 04:35:58.10 .net

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

36 ：名無しさん＠そうだ選挙にいこう：2021/03/06(土) 09:58:58.49 .net

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

37 ：名無しさん＠そうだ選挙にいこう：2021/03/06(土) 22:04:14.60 .net

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

38 ：名無しさん＠そうだ選挙にいこう：2021/03/07(日) 09:26:19.86 .net

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

39 ：名無しさん＠そうだ選挙にいこう：2021/03/07(日) 10:20:22.43 .net

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

40 ：名無しさん＠そうだ選挙にいこう：2021/03/07(日) 13:48:36.28 .net

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

41 ：名無しさん＠そうだ選挙にいこう：2021/03/09(火) 20:19:49.99 .net

>>40
そうかあかんか

42 ：名無しさん＠そうだ選挙にいこう：2021/03/09(火) 21:54:40.85 .net

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

43 ：名無しさん＠そうだ選挙にいこう：2021/03/10(水) 10:47:05.97 .net

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

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

失せろ

44 ：名無しさん＠そうだ選挙にいこう：2021/03/10(水) 19:52:31.13 .net

ﾆﾔﾆﾔ

45 ：名無しさん＠そうだ選挙にいこう：2021/03/21(日) 22:56:05.72 .net

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

46 ：名無しさん＠そうだ選挙にいこう：2021/03/21(日) 23:56:29.63 .net

経理職。結構使ってる。

47 ：名無しさん＠そうだ選挙にいこう：2021/03/22(月) 07:59:55.03 .net

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

48 ：名無しさん＠そうだ選挙にいこう：2021/03/22(月) 13:37:23.07 .net

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

49 ：名無しさん＠そうだ選挙にいこう：2021/03/22(月) 14:14:12.02 .net

項目　4月　5月　…　3月
あれ　123　456　…　…
それ　…　　…　　…　…
…

みたいな表を、
データとして再利用するのに

項目　月　値
あれ　4月　123
あれ　5月　456
…

みたいに線形にするのに使ってる。
ピボットを分解できるのが一番ありがたい機能。

回収した見込情報や作成した予算の、整理、分析。そういったことに流用してますね。

他にも諸々。

50 ：名無しさん＠そうだ選挙にいこう：2021/04/11(日) 09:54:04.09 .net

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

↓のテーブルで担当者1と社員マスタは繋げられるのですが、担当者2を繋げようとすると循環参照だとエラーになってしまい困ってます

データテーブル
・担当者1
・担当者2

社員マスタ
・社員

51 ：名無しさん＠そうだ選挙にいこう：2021/04/15(木) 23:45:05.30 .net

「データテーブル」というのは、
例えば、ある「部署」に属する担当者のリスト？

52 ：名無しさん＠そうだ選挙にいこう：2021/06/05(土) 16:36:36.40 .net

データソースをWEBからとした場合proxy認証はどうやって設定されてますか？

53 ：名無しさん＠そうだ選挙にいこう：2021/07/25(日) 23:28:58.52 .net

何故人がいないのだ

54 ：名無しさん＠そうだ選挙にいこう：2021/07/28(水) 08:12:23.13 .net

誰も興味無いから

55 ：名無しさん＠そうだ選挙にいこう：2021/07/28(水) 08:18:53.14 .net

twitterやQuiitaっていうwebサイト見たほうが情報量多いので。

56 ：名無しさん＠そうだ選挙にいこう：2021/07/29(木) 11:06:01.27 .net

Excelの話。
複数のcsvをFile結合したクエリAがあって、
そのクエリAを「参照」して集約データをつくるクエリBをこしらえた。

これでクエリBだけを更新すると
更新状況を示す欄にcsvを全部読み込んでる表示が出てくる。
クエリAの読み込み先はデータモデルにしてあるのだけど、それでもいちいちcsv読み直しに行ってるみたいでめっちゃ時間かかる。

クエリAの結果を保持したままクエリBだけ動いて欲しいなあ…大したことしてないから一瞬で終わるはずなのに。何かいい手ありません？

データモデルの意味も良くわからんなあ…
読み込み先データモデルにしとくとOnMemoryじゃなくてDiskに保存されてるものと思ってたから、いちいち読み込み直すんかい！て思った。

57 ：名無しさん＠そうだ選挙にいこう：2021/07/29(木) 16:14:49.62 .net

>>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
で更新させている。

58 ：名無しさん＠そうだ選挙にいこう：2021/08/02(月) 15:24:54.85 .net

>>57

ありがとうございます。
bookを分割するのが確実そうですね。
月初の繁忙期に入ってしまうので来週くらいに時間取って試してみます。

あとこれsharepointに上げておいたら定時更新してくれたりせんかなあ。
パソコンカチャカチャ仕事を減らしたくていろいろやってるのだけど、もうコード書くどころか「すべて更新」押すのすら面倒になってきた笑

面倒なのを「やっとけ」言うだけの人はいいよね
やらされる方はほんとたまったもんじゃないわ…

59 ：名無しさん＠そうだ選挙にいこう：2021/08/15(日) 16:00:56.19 .net

やっとマージで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

60 ：名無しさん＠そうだ選挙にいこう：2021/08/25(水) 06:54:28.35 .net

>>59 に追記
マージでの名寄せで完全一致以外のあいまい照合は、
閾値設定とか別リスト参照か。

番号範囲に対する近似値（うちわの最大）とかは出来ないので、
ここはヤッパリ、ソートしてMATCHとかXMATCHになりそう。

61 ：名無しさん＠そうだ選挙にいこう：2021/09/09(木) 16:30:59.59 .net

初心者です
みなさま教えて下さい

PowerQueryで吐き出したシートに対して、列ごとに名前の定義をして使うことを考えています
しかし、元データを直して更新すると、吐き出したシートの列の名前の定義が消えてしまうようです
どうやったら解決できるでしょうか？
VBA組むか、その都度定義してやるしかないでしょうか
よろしくおねがいします！

62 ：名無しさん＠そうだ選挙にいこう：2021/09/10(金) 02:54:51.48 .net

>>61
> 元データを直して更新すると、吐き出したシートの列の名前の定義が消えてしまうよう

状況がよく分からないのだけれど、
列名変更をPower Query エディターでやらずに、
ワークシート上で直接変更した
ということ？

クエリーとして最初に認識した列名が
エディター内のその後のステップで扱われるから、
エディターで列名変更しないとクエリーエラーになるのかと。

元のシートの列名と、変換したい列名が固定しているなら
エディター内で列名を変更するステップを追加すれば良いのでは？

元表をクエリーに変換するとき、先頭行をテーブルの見出しにしないか、
例えば、
・元表は別のbook
・ワークシート名は同じ
・対象となるテーブルや定義範囲のタイトル行は、ワークシート上の開始位置は同じ
・列の基本構成や順番は同じで、「8月合計」→「9月合計」に変わる程度
ということなら、

クエリーとして読込む対象を、テーブルではなくワークシートにすると
A列、B列・・・がColumn1、Column2・・・として固定して認識されるので、
エディター内で
・このColumn1、Column2・・・の列名変更する
・データ本体1行目までの間にある余計な行(旧見出し行を含む)を削除
するステップを入れれば良いのでは？

63 ：名無しさん＠そうだ選挙にいこう：2021/09/11(土) 09:36:43.83 .net

PowerQueryで、
「セルがすべてnull」の列を判定して、その列を削除する
みたいなことってできますかね？
M関数をどうやって組めばよいでしょうか。

64 ：名無しさん＠そうだ選挙にいこう：2021/09/11(土) 12:55:52.74 .net

それはできない

65 ：63：2021/09/11(土) 14:35:27.32 .net

>>64
やっぱりだめですかありがとうございます
VBAでやるしかないですかね・・・

66 ：名無しさん＠そうだ選挙にいこう：2021/09/11(土) 16:44:48.00 .net

全てnullのを消したい？
アホみたいな方法だが、できなくはない。

67 ：63：2021/09/11(土) 17:05:12.81 .net

>>66
PowerQueryでなにか方法ありますか？

68 ：名無しさん＠そうだ選挙にいこう：2021/09/11(土) 17:32:20.06 .net

条件によって列の数が変わるとか、なかなか…

69 ：名無しさん＠そうだ選挙にいこう：2021/09/11(土) 17:39:42.82 .net

列行入れ換えて行削除して戻せ

70 ：63：2021/09/11(土) 18:22:02.99 .net

>>69
おお、その手がありましたか
ちょっと試してみようかな
ありがとうございます

71 ：名無しさん＠そうだ選挙にいこう：2021/09/11(土) 18:53:11.67 .net

行列入れ替え→AddColumnで条件式追加（その行が全てnullかどうか）

72 ：名無しさん＠そうだ選挙にいこう：2021/10/17(日) 15:38:25.87 .net

日付を和暦（2021/10/17→R3/10/17みたいに）するにはどうすればよいですか？

73 ：名無しさん＠そうだ選挙にいこう：2021/10/17(日) 21:29:55.20 .net

>>72
テキストにして「2021/」を「R3」に置き換えるか、
西暦を「-2018」して頭に「R」付けるのはだめでつか。
てかExcelで読み込んで書式設定してやればいい気がします。

74 ：名無しさん＠そうだ選挙にいこう：2021/10/18(月) 08:05:27.92 .net

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

75 ：名無しさん＠そうだ選挙にいこう：2021/10/18(月) 12:58:13.25 .net

VBA挟む

76 ：名無しさん＠そうだ選挙にいこう：2021/10/18(月) 13:15:15.00 .net

私もわからないことが。

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

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

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

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

77 ：名無しさん＠そうだ選挙にいこう：2021/10/18(月) 18:10:22.81 .net

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

78 ：名無しさん＠そうだ選挙にいこう：2021/10/18(月) 20:12:02.71 .net

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

79 ：名無しさん＠そうだ選挙にいこう：2021/10/18(月) 20:14:45.51 .net

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

80 ：名無しさん＠そうだ選挙にいこう：2021/11/02(火) 20:12:42.49 .net

同じ行に、同じ文字列が出た場合、左から読んで一番左のものだけ生かして、ほかはnullにする方法とかありますでしょうか。
パワークエリでできたらありがたいです。


A　B　C　A　D　C　E　C　D　F

↓

A　B　C　　　D　　　E　　　　　F

みたいなイメージです
どうぞよろしくご指導お願いいたします！

81 ：名無しさん＠そうだ選挙にいこう：2021/11/03(水) 00:06:14.63 .net

>>80
一行だけなら縦横変換してダブリ排除
複数行なら比較列をひたすら挿入していく

82 ：80：2021/11/03(水) 06:58:14.95 .net

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

> 複数行なら比較列をひたすら挿入していく

具体的にはどんな比較列になるでしょうか　m関数を示していただければ幸いです

83 ：名無しさん＠そうだ選挙にいこう：2021/11/13(土) 15:57:00.01 .net

日本語の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 ：名無しさん＠そうだ選挙にいこう：2021/11/13(土) 22:53:09.16 .net

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

85 ：名無しさん＠そうだ選挙にいこう：2021/11/14(日) 06:29:24.14 .net

プライバシー

86 ：名無しさん＠そうだ選挙にいこう：2021/11/14(日) 07:57:21.15 .net

間違えた

87 ：名無しさん＠そうだ選挙にいこう：2021/11/14(日) 08:20:09.13 .net

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

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

88 ：83：2021/11/14(日) 10:33:08.91 .net

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

https://youtu.be/2DJI9V1kBqg

89 ：名無しさん＠そうだ選挙にいこう：2021/11/14(日) 13:05:21.12 .net

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

90 ：名無しさん＠そうだ選挙にいこう：2021/11/16(火) 21:26:07.71 .net

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

91 ：名無しさん＠そうだ選挙にいこう：2021/11/16(火) 21:41:41.48 .net

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

92 ：名無しさん＠そうだ選挙にいこう：2021/11/16(火) 21:46:16.28 .net

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

93 ：名無しさん＠そうだ選挙にいこう：2021/11/17(水) 00:22:27.51 .net

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

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

94 ：名無しさん＠そうだ選挙にいこう：2021/11/27(土) 13:27:52.35 .net

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

Power BIでの説明だけど。
https://youtu.be/pf5ImoMh6fU
https://youtu.be/cP-Cw613014

95 ：名無しさん＠そうだ選挙にいこう：2021/12/16(木) 09:27:31.56 .net

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

96 ：名無しさん＠そうだ選挙にいこう：2022/01/14(金) 11:16:35.78 .net

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

97 ：名無しさん＠そうだ選挙にいこう：2022/01/14(金) 13:05:32.23 .net

事故解決
使えないっぽい

98 ：名無しさん＠そうだ選挙にいこう：2022/02/14(月) 20:50:10.87 .net

これ四捨五入すると5が切り捨てられるんだけどどうにかならないですか？

Round関数 パワクエ
0.234 0.23 0.23
0.345 0.35 0.34 ←←←
0.456 0.46 0.46

99 ：名無しさん＠そうだ選挙にいこう：2022/02/14(月) 20:58:39.13 .net

どっかの記事
パワークエリの四捨五入は「銀行型丸め」が適用されており、ワークシート上のROUNDと結果が異なる場合があります。
この対策として、M関数の「Number.Round」の丸めモードを算術型丸めになるように設定する必要があります。

100 ：名無しさん＠そうだ選挙にいこう：2022/02/14(月) 22:30:00.61 .net

>>99
ありがとう御座います
色々検索しててAwayFromZeroを入れてみて一応期待する動作にはなってそうですがこれで果たして正解なのか。。

101 ：名無しさん＠そうだ選挙にいこう：2022/03/13(日) 10:44:22.49 .net

「後ろにステップを挿入」の使い方、少し分かってきた。

例えば、csvファイルが単純なマトリックスだけでなくて、
ヘッダーみたいに集計日とかデータ更新日とかが入っているような複合構成の場合、
上位行削除とか列削除/追加してなど加工して、本体部をひとまず整形し、
最後のステップ名が「変更された型」だったとする。<-ココ大事

ここで全行に共通する「集計日」列を追加したいとすると、
右クリックで「後ろにステップを挿入」して、処理をいったん分岐(？)出来る。

元ステップ冒頭の「ソース = ・・・」をコピペして「ソース2 =・・・」とすれば、
csvを読込んだ最初の状態をまた読み込める。
今度はヘッダー部分の「集計日」だけに削って行って、
　集計日
　2022/3/12
の2行×1列だけのテーブルにし、
ここでの最後のステップ名が「変更された型2」だったとすると、
　R_集計日 = Table.FirstValue(変更された型2),
とかにして、1行目の値を名前定義の変数にしておく。

で、元の本体部への戻り方は、「カスタム列の追加」の場合だと
　カスタム列の追加 = Table.AddColumn(変更された型, "追加列名", each R_集計日)
で、全行共通の集計日列が追加出来た。

・複数行/列にした場合はどうすればいいのか、
・他のクエリーの任意の行/列の値を取得するにはどうすればいいのか、
は今後の課題。

M式はList.なんちゃらを使う？
引数は{0から始まる行番号}[列名]を使った何かか？

102 ：名無しさん＠そうだ選挙にいこう：2022/03/13(日) 10:52:44.52 .net

自分のブログかチラシの裏でどうぞ

103 ：名無しさん＠そうだ選挙にいこう：2022/03/14(月) 00:22:12.62 .net

見事なチラシだった

104 ：名無しさん＠そうだ選挙にいこう：2022/03/23(水) 11:26:04.52 .net

僭越ながら教えてください。

パワークエリで見た目の整形の話です。

Excel2019でパワークエリで大量の
テキストファイルを読み込ませてるのですが、そこまではうまくいきました
見栄えのためにテキストファイルごとに列を分割して並べて表示も時々利用したいと考えています。
そのようなことはできるでしょうか？

イメージとしてはテキストコピペしてエクセルのA1から横に張って行くイメージです。

※背景として利用したいテキストファイルが合計15000ほどあり各システム別に分けられているので一つのくぎりとしては100程度になります。
利用目的としてpivot機能も含むチェック業務利用です。

105 ：名無しさん＠そうだ選挙にいこう：2022/03/23(水) 11:58:10.30 .net

僭越ながらの使い方に違和感

106 ：名無しさん＠そうだ選挙にいこう：2022/03/23(水) 12:51:22.55 .net

1行目だけで判断してやるなよと思ったら2行目以降もヤバかった

107 ：名無しさん＠そうだ選挙にいこう：2022/03/23(水) 14:24:28.16 .net

>>104　
powerquery内でTable.Pivot関数突っ込めば列にその情報ソースを出力できないこともない。リボンに列のピボット？があるはずだから、それ突っ込めば希望に近いものができるかもしれないので自分で調べて。その整形自体おすすめしないけど。15000列もエクセル側が受け皿を用意できたっけね？

108 ：名無しさん＠そうだ選挙にいこう：2022/03/25(金) 22:58:56.33 .net

誘導されてきました
エクセルのクエリで、一つのファイルにある複数のシートから、たとえば１つ目から４つ目のデータ（１０００行くらい）を１枚目を１から４、２枚目を５から８に転記していくにはどのような設定でやればいいでしょうか

109 ：名無しさん＠そうだ選挙にいこう：2022/03/26(土) 05:23:53.34 .net

>>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

あとは知らん。

110 ：名無しさん＠そうだ選挙にいこう：2022/03/26(土) 07:09:05.40 .net

>>108
VBAスレから誘導されたの見てて心配で見に来たけど
不特定多数のシートを扱うならクエリも多分動的制御が必要になるから結局VBAのお世話になると思うよ。

たらい回しにするつもりはないけど、
VBAで言ってることを実装出来るコードを書いといたので一応見といてくれ。
Power Queryは使っていないけどね。

111 ：名無しさん＠そうだ選挙にいこう：2022/03/26(土) 08:06:23.90 .net

>>109
質問者自身が自分が何やりたいのか分かってないと思うから丁寧に回答したところで思っていたのと違うとかそんな事言われるだけになるぞ
お互い損するだけ

112 ：名無しさん＠そうだ選挙にいこう：2022/03/26(土) 12:52:40.95 .net

>>110
普段はPower Query使ってるけど、
繰り返し大量処理だとメモリー爆食いするので
VBAも参考にさせてもらってるよ。

ヘルパークエリーでパラメータ使って
さらにその都度VBAでファイル保存の
合わせ技で効率良くなるかも知れない。

113 ：名無しさん＠そうだ選挙にいこう：2022/03/26(土) 22:07:01.78 .net

この処理は難しそうということですね
ありがとうございました

114 ：名無しさん＠そうだ選挙にいこう：2022/03/26(土) 23:08:49.05 .net

優しい世界

115 ：名無しさん＠そうだ選挙にいこう：2022/03/28(月) 18:47:33.32 .net

>>113
生半可Power Cueryでやろうとするから難しいだけ。
やる方法には適材適所というものがある。

上記の要件であればVBAならバカでチンケな俺でも数分で作ることが出来た。というかVBAスレに貼っておいた。

Power Queryの今までやってきたことは無駄にはならないだろうけど、
自分でVBAのコード書いたりSQL書いたり出来ると更に出来る仕事の幅が増えるよ。

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

116 ：名無しさん＠そうだ選挙にいこう：2022/03/28(月) 21:08:23.17 .net

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

117 ：名無しさん＠そうだ選挙にいこう：2022/03/28(月) 21:14:03.41 .net

それが5chクォリティ

118 ：名無しさん＠そうだ選挙にいこう：2022/03/29(火) 07:37:54.81 .net

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

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

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

119 ：名無しさん＠そうだ選挙にいこう：2022/03/29(火) 10:53:52.14 .net

>>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つマージかしら？　それだったら元表をソートすれば済んじゃいそう・・・

120 ：名無しさん＠そうだ選挙にいこう：2022/03/30(水) 00:29:25.11 .net

>>119
レスありがとうございます。

pngの通り類似の処理はしたのですが、残念ながらうまくいきませんでした。
緑文字でListととなり、一括に束ねられてしまいます。
※束ねられたものを成形する方法ありそうですが、わかっていません・・・

List.Firstやとかではテキストが表示するのですが・・・
List.SourceやList.ReverseだとListになってしまいます・・・
違いは適用したステップが自動生成されているくらいだとは考えています。

121 ：名無しさん＠そうだ選挙にいこう：2022/03/30(水) 06:03:24.41 .net

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
変更された型
ーーーーーーーーーーーーーーーーーーーー

122 ：名無しさん＠そうだ選挙にいこう：2022/03/30(水) 08:26:26.80 .net

>>120
元のデータがどういうものか、
もうちょっと説明がないと
対応しようがない。

123 ：120：2022/03/30(水) 09:53:33.54 .net

>>122

おはようございます。

知識がなさすぎてどういう情報を提供すればいいのかさえわかっていませんが、
元のデータはﾌｫﾙﾀﾞ上にある「ただのテキストデータ」で、
GUIでﾌｫﾙﾀﾞごと選択して複数ファイルを引っ張ったものとなります。

丁度121のソースとほぼ同じだとおもいます。
数値とかは入力値としてはありますが、PowerQueryでは計算とかは求めないものと考えています。

諦めの選択にはなっていますが、powerqueryにここまでてこずると思っていなかったこともあり、
直近ではテーブルデータに起こしてから細かい計算とかはExcelの関数で手入力しようと考えています。

Excelの操作手順を以下に記載します。

�@Excel新規作成　�Aデータの取得-ﾌｫﾙﾀから　�B結合　�Cクエリエディタその他のクエリから「変換」列のピボット　�D数式バーでList.Reverseに変換とかを

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

124 ：名無しさん＠そうだ選挙にいこう：2022/03/30(水) 11:21:58.27 .net

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

125 ：名無しさん＠そうだ選挙にいこう：2022/03/30(水) 12:14:39.49 .net

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

126 ：名無しさん＠そうだ選挙にいこう：2022/03/30(水) 17:12:46.25 .net

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

127 ：名無しさん＠そうだ選挙にいこう：2022/03/30(水) 21:10:35.26 .net

話題変わるけど、GUIまんまだと読みにくいからSlcClmとかに俺は名前変えてる。

128 ：名無しさん＠そうだ選挙にいこう：2022/04/01(金) 08:12:57.87 .net

クエリの関数は使えるものと使えないものが分かりづらいよね
所詮数々の企業をだめにしてきたマイクロソフトか

129 ：名無しさん＠そうだ選挙にいこう：2022/04/02(土) 11:50:11.30 .net

そう言えば、ADOだとワイルドカードが%だから、
Accessも%だろうと思ったら実は*で、
フザケンナちゃんと統一しろムキー!ってなったことあるわ。

130 ：名無しさん＠そうだ選挙にいこう：2022/04/11(月) 19:20:30.64 .net

クエリ勉強用に書籍を購入検討していますが何かしら比較したことのある書籍あれば教えてください。


レスつくのが時間いると思いますので、
今時点は適当に購入してみて後日感想記載します。

131 ：名無しさん＠そうだ選挙にいこう：2022/04/11(月) 19:31:11.43 .net

>>130
クエリってpowerqueryを略して言ってるの？

132 ：名無しさん＠そうだ選挙にいこう：2022/04/11(月) 22:28:02.59 .net

パワクエ

133 ：名無しさん＠そうだ選挙にいこう：2022/04/11(月) 22:53:07 .net

ﾃﾃﾃﾝﾃﾝﾃﾝﾃﾝﾃﾝﾃｰﾝ ﾃｰﾝﾃﾃｰﾝﾃﾝﾃﾝﾃﾝﾃﾝﾃﾝﾃｰﾝﾃｰﾝ

134 ：名無しさん＠そうだ選挙にいこう：2022/04/11(月) 23:31:11 .net

ツマンネ
幼稚園児か？

135 ：名無しさん＠そうだ選挙にいこう：2022/04/12(火) 00:24:11.65 .net

いつもの機械翻訳か

136 ：名無しさん＠そうだ選挙にいこう：2022/04/12(火) 19:59:29.96 .net

月次更新を半年くらい繰り返してきたクエリーを今月更新したら、
こんなエラーメッセージが出てきて更新不能になったでござる。

> 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

137 ：名無しさん＠そうだ選挙にいこう：2022/04/12(火) 21:22:33.11 .net

今まで動いてたんならソース元を先ずは疑ってみるべす

138 ：名無しさん＠そうだ選挙にいこう：2022/04/27(水) 21:26:09.00 .net

質問です

グループ毎に小計及び総合計を出したいですが、簡単に出来る方法はありますでしょうか？
ネットに乗ってるLetは使い方が良く分かりませんでした

139 ：名無しさん＠そうだ選挙にいこう：2022/04/27(水) 22:10:34.45 .net

前提がよくわからん。ピボットテーブルでも使えば？

140 ：名無しさん＠そうだ選挙にいこう：2022/04/27(水) 22:34:05.38 .net

列数が20ほどありピボットテーブルで横に伸ばすと操作性が著しく劣るためパワークエリで直接編アレンジしてテーブル形式で出力したいです
機能の小計が使えれば良いのですがテーブル形式になってると使えないようです

141 ：名無しさん＠そうだ選挙にいこう：2022/04/28(木) 11:01:21.67 .net

いつもの機械翻訳の人？

142 ：名無しさん＠そうだ選挙にいこう：2022/04/28(木) 18:25:12.49 .net

Powerqueryを数字ないデータで使ってる人っていますか？

143 ：名無しさん＠そうだ選挙にいこう：2022/04/29(金) 11:55:35.39 .net

数字をテキストとしてならあるよ。

144 ：名無しさん＠そうだ選挙にいこう：2022/05/17(火) 20:43:53.61 .net

フィルターされた行 = Table.SelectRows(テーブル, each Text.StartsWith([#"RowName"], FilterValue))

変数RowNameがうまく認識されず、
「Expression.Error: レコードのフィールド 'RowName' が見つかりませんでした。」
となります。
どうするのがよいでしょうか？

145 ：名無しさん＠そうだ選挙にいこう：2022/05/17(火) 21:56:12.53 .net

Start.Withは「その文字列で始まる」という意味だから、RowNameで始まる文字列が無いんでしょ。そこ変えれば？

146 ：名無しさん＠そうだ選挙にいこう：2022/05/17(火) 23:21:37.74 .net

回答ありがとう。

RowNameは動的に変わる変数で、フィルタ用の文字列ではなく、列名を指してます。
たとえばRowNameが「商品名」だったとき、
・[#"RowName"]→エラー
・[RowName]→エラー
・[商品名]→正常
という感じでした。
RowNameに「商品名」が正しく入っていることは確認済みで、
もちろん列名が「商品名」という列も存在します。

角括弧 [] に変数を入れるときに、とくに気にすることはないと
思っているんですが、どうなんだろう？

147 ：名無しさん＠そうだ選挙にいこう：2022/05/18(水) 13:50:53 .net

おおよそ認識の通り、角括弧内の文字列に演算子を含む場合には#"〇〇"としてあげるくらい。このエラーには関係ない。
StartsWithはRowNameにFilterValueが含まれるか否かをTrueかFalseで返す関数らしいからそこが問題かと。その意味で「その文字列で始まる文字列が無い」んでしょうね。これ以上はわからん

148 ：名無しさん＠そうだ選挙にいこう：2022/05/19(木) 08:23:07.10 .net

>>147
ありがとう
もうちょっと考えてみますわ

149 ：名無しさん＠そうだ選挙にいこう：2022/05/22(日) 22:13:07.82 .net

最近知ってめっちゃ便利だな
こねくり回すならpythonでするけど

150 ：名無しさん＠そうだ選挙にいこう：2022/05/25(水) 03:56:33 .net

クエパワー

151 ：名無しさん＠そうだ選挙にいこう：2022/05/31(火) 01:46:15.43 .net

パワークエリで利用するデータはある程度整えてから利用した方が良いのでしょうか

読み込むExcelファイル内に社名や判子を押すセルなど不要なデータがあったりしてちょっと扱いにくいです
特にマージするときに行などがズレてしまいます｡

仕方が無いので､パワークエリの扱いがまだよくわからないのもあって素データを編集しています｡
素のExcelで不要なセルを削除したりしてからマージします｡でも編集するのは面倒だし
素のデータを編集するのは好きではないけど､これは仕方ないことでしょうか｡

152 ：名無しさん＠そうだ選挙にいこう：2022/05/31(火) 12:15:52.30 .net

PowerQuery自体にデータ整形の機能はあるけど
それにも限界があるかと。

ワークシートの読込みで
行列の規則性ではダメで、罫線だけが頼りの場合だと、
VBAも使わないと整形出来ないかも。

Webページの自動認識である程度テーブルを分けてくれるので、
M言語に罫線も認識する関数があるのかしら？
htmlで配列を検出しているのかどうか、
まだ分からないけど。

153 ：名無しさん＠そうだ選挙にいこう：2022/05/31(火) 19:53:17.67 .net

もともとクエリだからね。
データベース並みに構造化されたデータを加工するのが前提。
帳票的なデータをいじるのにはあまり向いてないわな。

154 ：名無しさん＠そうだ選挙にいこう：2022/05/31(火) 21:28:54.04 .net

>>152
>>153

ありがとうございます
今日もう一度見直したらエラーやnullが原因で崩れてしまうことがわかり無事統合できました｡
今までは不要な行は手動で消していましたがフィルタ機能を使えばできましたね……

155 ：名無しさん＠そうだ選挙にいこう：2022/06/01(水) 00:46:56.35 .net

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

156 ：名無しさん＠そうだ選挙にいこう：2022/06/01(水) 16:52:33.88 .net

質問が二つありますので宜しくお願い致します

1. 　CurrentWorkBook内に作ったピボットテーブルは
　　範囲参照では取り込めないというメッセージが出たので、
　　Excel　WorkBooｋからの取り込みで作業中のファイルを指定して
　　ピボットテーブルのシートをデータモデルに取り込んで作業していました。
　　ある程度作業は勧められましたが　　突然読み込みエラーでクエリごと
　　消えてしまいました。
　　作業中のBook内のピボットテーブルからデータを読み込む
　　のには制限があるのでしょうか？
　　

2. クエリを削除しても、ピボットテーブルをデータモデルから
　　作成するときに、削除したはずのクエリがテーブルとして出てきます。
　　データモデルを完全に削除する方法を教えてください

157 ：名無しさん＠そうだ選挙にいこう：2022/06/01(水) 20:02:45.92 .net

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

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

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

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

158 ：名無しさん＠そうだ選挙にいこう：2022/06/02(木) 13:49:56.88 .net

>>157
ありがとうございます。ピボットテーブルはやはり参照元にしない方が良いのですね。
確かに集計項目を変えた時にエラーが出ました。

今やりたいのは例えば、下記のようなテーブルがあったとして
売上１００以下の商品はその他というくくりにして集計したいのです


商品　　　　カラー　　　　売上
A　　　　　　赤　　　　　　１００
A　　　　　　青　　　　　　　５０
A　　　　　　黄　　　　　　　１０
B　　　　　　赤　　　　　　　７０
B　　　　　　青　　　　　　　２０
C 　赤　　　　　　　９０
C　　　　　　青　　　　　　　２０
D　　　　　　赤　　　　　　　２０
D　　　　　　青　　　　　　　５０

上記の表を

商品　　　売上
A　　　　　１６０
C　　　　　１１０
その他　　１８０

このように加工して、その表を再びテーブルとして使用したいのです。
私はピボットテーブルを使ってしまったのですが、このような場合に使える方法がありましたら、教えてください。

159 ：名無しさん＠そうだ選挙にいこう：2022/06/02(木) 14:19:05.73 .net

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

160 ：名無しさん＠そうだ選挙にいこう：2022/06/02(木) 19:53:09.27 .net

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

161 ：157：2022/06/02(木) 22:03:48.12 .net

>>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の模様。

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

162 ：157：2022/06/02(木) 22:08:25.55 .net

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

163 ：157：2022/06/02(木) 22:11:18.45 .net

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

164 ：名無しさん＠そうだ選挙にいこう：2022/06/02(木) 23:14:56.46 .net

質問させてください

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

165 ：名無しさん＠そうだ選挙にいこう：2022/06/02(木) 23:33:25.04 .net

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

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

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

166 ：名無しさん＠そうだ選挙にいこう：2022/06/03(金) 01:12:53.31 .net

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

167 ：名無しさん＠そうだ選挙にいこう：2022/06/03(金) 03:27:08.83 .net

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

168 ：名無しさん＠そうだ選挙にいこう：2022/06/08(水) 00:06:55.79 .net

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

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

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

↓こういう状況です。
https://i.imgur.com/Bi79f98.png

169 ：名無しさん＠そうだ選挙にいこう：2022/06/08(水) 00:13:12.49 .net

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

170 ：名無しさん＠そうだ選挙にいこう：2022/06/08(水) 00:23:02.05 .net

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

171 ：名無しさん＠そうだ選挙にいこう：2022/06/08(水) 01:29:41.21 .net

>>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.の各週ファイル内に関数計算を仕込んでおいても良いけど、
追加や変更が出たとき、全部直さなきゃならなくなるから。

172 ：171：2022/06/08(水) 01:43:56.70 .net

>>171 で
> 3. 関数計算の展開は、2.のファイル内で各週csv等を
> 結合してから展開する方が吉。

とは書いたものの、
・1週分のデータが数千行以上
・結合した後の合計数万行以上
・横列に展開する関数列がやたら多い
なんてことで、2. のブックの処理速度が落ちるというのであれば、
計算負荷分散のため、
1. のファイル内で関数計算を展開しておくことはあり得る。

173 ：171：2022/06/08(水) 01:51:10.39 .net

でなきゃ、各週分を取り込んだとき、2.のブックのワークシートには読込ませず、
データモデルにしておくのも一つの手。

データモデル内で右に列を追加しての関数計算は可能。
データモデルだとワークシート104万行の限界を超えられるし、
これをピボットテーブルのデータ元にすることも可能。

とかやってるうちに、欲が出てきてPower Pivot、DAX関数の沼にはまったが。

174 ：168：2022/06/08(水) 02:18:46.15 .net

>>171
むちゃくちゃ親切にありがとうございます！
1週分のデータはせいぜい100〜200行ぐらいで、結合後もまだ3000行弱なので最初のやつで大丈夫だと思います。
とりあえず動画見てみます！

175 ：名無しさん＠そうだ選挙にいこう：2022/06/28(火) 11:20:25 .net

PowerBI上で重回帰分析は実施できますでしょうか？

176 ：名無しさん＠そうだ選挙にいこう：2022/06/28(火) 11:48:43.99 .net

>>175
やったことないけど出来そうに見える

Power BIの高度な分析機能の紹介【主要なインフルエンサー】
https://youtu.be/yqJtC4_-4lE

177 ：名無しさん＠そうだ選挙にいこう：2022/06/28(火) 13:40:02.58 .net

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

178 ：名無しさん＠そうだ選挙にいこう：2022/07/01(金) 17:58:50.96 .net

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

179 ：名無しさん＠そうだ選挙にいこう：2022/07/26(火) 11:01:08.73 .net

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

180 ：名無しさん＠そうだ選挙にいこう：2022/07/26(火) 11:27:40.86 .net

それが基本機能です。

181 ：名無しさん＠そうだ選挙にいこう：2022/07/26(火) 11:48:18.04 .net

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

182 ：名無しさん＠そうだ選挙にいこう：2022/07/27(水) 04:26:47.47 .net

>>179
マージで

183 ：名無しさん＠そうだ選挙にいこう：2022/07/27(水) 20:06:48.57 .net

>>182
無粋だな

>>180-181の流れで完璧だったのに

184 ：名無しさん＠そうだ選挙にいこう：2022/07/28(木) 05:04:05.70 .net

悪いな、せっかちなもので。

185 ：名無しさん＠そうだ選挙にいこう：2022/08/01(月) 12:38:51.85 .net

webからの取得で認証フォーム突破出来なかったから調べたけどBASIC認証のみなんやね
カスタムから出来るかもしれないて回答してる人いるだけでやり方はどこにも書いてなかった
認証フォームあるようなやつスクレイピングしたいならPythonとかプログラミング学んだ方が早いんかな

186 ：名無しさん＠そうだ選挙にいこう：2022/08/16(火) 10:17:32.13 .net

Power Queryで捨て仮名をナミ字に変換するにはどうすればいいんでしょう
ぁぃぅぇぉゃゅょゎっ　それぞれを、
あいうえおやゆよわつ　にしたいのですが、上手いやり方を思い付けません

187 ：名無しさん＠そうだ選挙にいこう：2022/08/16(火) 13:03:37.37 .net

変換テーブルを使う考え方でなんとかできました
もっと上手いやり方があるようでしたら教えてください
以下が今回やった方法です
= List.Accumulate(Table.ToRows(#table({"捨て仮名","ナミ字"}, {{"ｧ","ｱ"},{"ｨ","ｲ"},{"ｩ","ｳ"},{"ｪ","ｴ"},{"ｫ","ｵ"},{"ｬ","ﾔ"},{"ｭ","ﾕ"},{"ｮ","ﾖ"},{"ｯ","ﾂ"}})),[ｶﾅ],(x, y)=>Text.Replace(x,y{0},y{1})))

188 ：名無しさん＠そうだ選挙にいこう：2022/08/16(火) 15:35:40.81 .net

解説記事としては、これかな？

「複数の語句をまとめて置換する」を実務で使おう｜Power Query
https://qiita.com/olt_yt/items/07a28fb8173a324b9c1c

189 ：名無しさん＠そうだ選挙にいこう：2022/08/17(水) 10:31:47.55 .net

>>188
全然解説記事になってないな
読んで損した
その記事が参照した記事の方が、解説もしっかりされてる

複数の語句をまとめて置換する - Qiita
https://qiita.com/tanuki_phoenix/items/94fb489726a42ad764b5

190 ：名無しさん＠そうだ選挙にいこう：2022/08/19(金) 07:17:31.38 .net

PowerBI Proより無料のDesktopのほうが機能多いのね

191 ：名無しさん＠そうだ選挙にいこう：2022/08/31(水) 15:07:50.25 .net

Power Queryでクエリの並び替えってできますか？
今は新しいExcelブックをつくってクエリをコピペするという不毛な作業をしています

192 ：名無しさん＠そうだ選挙にいこう：2022/08/31(水) 15:29:32.86 .net

>>191
手動のドラッグによる並べ替えの話でいいの？

Excelの「クエリと接続」ペインだと右クリックメニューで一段ずつ上か下だけど、
いずれかのクエリーを編集で開いて
Power Query エディターの左ペインでやれば、
ドラッグで飛ばして並び替えは可能だけど。

193 ：名無しさん＠そうだ選挙にいこう：2022/08/31(水) 17:33:54.29 .net

>>192
ドラッグでできました…
まさかこんな単純な方法だったとは盲点でした
ありがとうございました！

194 ：名無しさん＠そうだ選挙にいこう：2022/09/01(木) 08:59:15.50 .net

他のブックへのクエリーのコピーは
PowerQueryのスクリプトをまるまるコピペで。

ワークシートの定義名範囲や、
クエリーでないテーブルの参照をクエリー内でしているときは、
それらも再構築が必要だけど、
名前定義の範囲のコピペは、
「(当該)ブック」とか「(当該)ワークシート」とか、
その有効「範囲」の作り直しが必要だったかと。
でないと「他ブックのリンク」になってしまうので要注意。

195 ：名無しさん＠そうだ選挙にいこう：[ここ壊れてます] .net

Table.AddColumn(ソース, "新列名", each [旧列名]*1000)の 旧列名 の部分を変数にするにはどうしたら良い？

196 ：名無しさん＠そうだ選挙にいこう：2022/09/11(日) 22:27:12.62 .net

指定した日数分の営業日ってDAX関数でできますかね？
指定した日までの営業日ならあるのですが。

197 ：名無しさん＠そうだ選挙にいこう：2022/09/12(月) 06:51:38.13 .net

>>195
それは俺も知りたい。
任意の列名変更は、一度ピボット解除して列名を縦にして、
if Text.Contains() then else
とかで検索と置換が出来るようにすれば良いのか？
エロい人、教えて。

198 ：名無しさん＠そうだ選挙にいこう：2022/09/12(月) 18:47:41.90 .net

>>195
自己解決した
Table.AddColumn(ソース, "新列名", each Record.Field(_, "旧列名")*1000)

>>197
列名の変更は {"旧列名", "新列名"} で両方とも文字列で指定だから問題の質が異なる
テーブルから列名検索したいなら
探した列名=List.Select(Table.ColumnNames(前の処理), each 検索条件)){0} //見つかった最初のもの
で得た列目使って {探した列名, "新列名"} とかにすればいい

199 ：名無しさん＠そうだ選挙にいこう：[ここ壊れてます] .net

>>198
ん？

よく分からんけど、それで「旧列名」の部分が変数扱いになったの？

Record.Field関数の2番目の引数はレコードの中の特定の列名だから、
思い切り定数で"旧列名"と指定していることになってるのかと。

1番目の引数のアンダースコアはeachとセットみたいだけど、
レコードの代名詞みたいになっているのかしら？

列名を変数にするって例えば、
column1、Column2、column3・・・
と元データを読込むたびに列が増えるような場合で、
最後の列が合計かなんかで、
それを1000倍にする列をさらに追加したい時、
元データの、変化する最後の列名を変数で扱いたいのかと思ってた。

200 ：sage：2022/09/28(水) 10:15:26.54 .net

Power BIのテーブルで、列名を縦書きにできませんか？

201 ：名無しさん＠そうだ選挙にいこう：2022/09/28(水) 14:42:54.65 .net

ピボット解除じゃなくて、まさか1セルの中での縦書き？

202 ：名無しさん＠そうだ選挙にいこう：2022/09/28(水) 19:45:35.61 .net

列名を文字のリストに分割してから改行文字を間に挟んで結合する
M言語の関数でできる
得られた文字列で列名変更

203 ：名無しさん＠そうだ選挙にいこう：2022/10/08(土) 20:41:20.29 .net

Power Queryは書式設定には使えませんか？

204 ：名無しさん＠そうだ選挙にいこう：2022/10/09(日) 08:15:09.14 .net

使えません

205 ：名無しさん＠そうだ選挙にいこう：2022/10/09(日) 09:18:42.05 .net

ありがとうございます
別の方法を考えてみます

206 ：名無しさん＠そうだ選挙にいこう：2022/10/27(木) 09:56:09.11 .net

連続した1時間ごとのデータがあって、
ある閾値を下回った連続時間帯が年間で最大何コマだったのかを調べたい。
（例えば、風力発電の年間出力データとか、気温の年間データとか）

下回った時間帯のフラグ列作って、
グループ化してその中でインデックス振って、
List.Sum(List.FirstN())で累計を取ろうかと思ったが、なんか上手く行かない。

List.Accumulateで累計するにも、
グループ化とインデックス、List.FirstN()は要るよね？
List.Accumulateの公式説明がよく分からない。

助けて。

207 ：名無しさん＠そうだ選挙にいこう：2022/10/31(月) 16:21:26.70 .net

PowerBI初心者です。表示したいグラフを表示する方法にたどり着けないので教えて下さい。

208 ：名無しさん＠そうだ選挙にいこう：2022/10/31(月) 16:24:38.27 .net

表示したいグラフは意味的にはソフトウェア開発における不具合検出数のグラフです。X軸は日付、Y軸は検出した不具合の数の累計と解決した不具合の数の累計です。

209 ：名無しさん＠そうだ選挙にいこう：2022/10/31(月) 16:31:15.24 .net

不具合は課題管理システムで管理していますが、PowerBIへの入力は課題管理システムからエクスポートした不具合一覧です。1行が1件の不具合を表します。1行を構成する列のうち、不具合の連番、不具合の状態(解決済か未解決か)を元データに不具合の累計件数と解決済み不具合の累計件数を時系列に表示させたいです。

210 ：名無しさん＠そうだ選挙にいこう：2022/10/31(月) 16:40:01.25 .net

X軸に不具合報告の作成日を指定し、Y軸にクイックメジャーのタイムインテリジェンスから何か選ぼうとしましたが、一番期間が長い選択肢でも年度累計までです。年度累計を選ぶと年度の変わり目で累計件数が0に戻ってしまいます。表示させたい事は年度関係なしの累計です。

211 ：名無しさん＠そうだ選挙にいこう：2022/10/31(月) 18:36:37.47 .net

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

212 ：名無しさん＠そうだ選挙にいこう：2022/10/31(月) 20:30:00.67 .net

アホすぎ

213 ：名無しさん＠そうだ選挙にいこう：2022/10/31(月) 20:30:29.69 .net

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

214 ：名無しさん＠そうだ選挙にいこう：2022/10/31(月) 20:32:31.97 .net

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

215 ：名無しさん＠そうだ選挙にいこう：2022/10/31(月) 23:28:19.14 .net

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

216 ：名無しさん＠そうだ選挙にいこう：2022/11/01(火) 01:53:03.10 .net

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

217 ：名無しさん＠そうだ選挙にいこう：2022/11/01(火) 06:19:16.86 .net

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

218 ：名無しさん＠そうだ選挙にいこう：2022/11/01(火) 06:49:20.30 .net

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

219 ：名無しさん＠そうだ選挙にいこう：2022/11/01(火) 07:21:07.74 .net

相手にしないことにした

220 ：名無しさん＠そうだ選挙にいこう：2022/11/01(火) 18:24:43.06 .net

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

221 ：名無しさん＠そうだ選挙にいこう：2022/11/01(火) 20:20:31.06 .net

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

222 ：名無しさん＠そうだ選挙にいこう：2022/11/05(土) 07:46:09.06 .net

別に104万行超えるわけでねぇーべ？
全期間のファイル、クエリーで読込んで結合したらよろし。
1つのフォルダにファイル突っ込んどいて
フォルダ指定で全部結合する方法は
あっちこっちで解説されとるがな。

104万行超えでも
データモデルにしてから集計クエリーで期間絞ればよろし。
遅いけど。

223 ：名無しさん＠そうだ選挙にいこう：2022/11/05(土) 07:59:30.59 .net

月の売上の横に前月比を出したいですがうまくいきません。

224 ：名無しさん＠そうだ選挙にいこう：2022/11/06(日) 06:55:46.45 .net

0から始まるインデックスを各行にふって
直前のステップ名が「A」とした場合、

前月比 = [売上] / A[売上]{[インデックス]-1}

最初の行がErrorになるのがイヤなら、後からエラーをnullに置換するか、

前月比 = if [インデックス] = 0 then null else [売上] / A[売上]{[インデックス]-1}

とか。

https://analytic-vba.com/power-query/m-code/begin-previous-ref/

225 ：名無しさん＠そうだ選挙にいこう：2022/12/19(月) 01:36:52.50 .net

属性の列にA B C D E…と項目があり、値列に対応する数値が入っています。散布図の縦軸横軸どちらも、値を選択して、X軸はAの値、Ｙ軸はBの値、の様なことをしたいです。スライサーを軸別に設定するようなこと可能でしょうか？もしくは、データテーブルの作り直しから必要でしょうか。

226 ：名無しさん＠そうだ選挙にいこう：2022/12/19(月) 06:30:25.65 .net

PowerPivotでメジャー作成かな

227 ：名無しさん＠そうだ選挙にいこう：2022/12/19(月) 10:38:59.67 .net

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

228 ：名無しさん＠そうだ選挙にいこう：2022/12/19(月) 13:26:23.57 .net

>>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軸を対象年月日時間帯にすることも可能だが、
それじゃ散布図でなくてただの折れ線グラフで十分で、
ピボットテーブルでも可能。

229 ：228：2022/12/22(木) 22:38:20.74 .net

たとえば風速の観測地点・年別の月展開箱ひげ図を
このやり方 >>228 でやるより、
PowerBIでやった方が遥に軽かった。

230 ：名無しさん＠そうだ選挙にいこう：2022/12/23(金) 00:27:17.32 .net

月別の集合グラフで半期だけ色変えたりフィルタ変えることはできますか？
４～９月は実績で１０～３月は計画値を表示したいです。

231 ：名無しさん＠そうだ選挙にいこう：2022/12/23(金) 07:04:20.22 .net

「集合グラフ」って、積み上げ棒グラフでない、通常の棒グラフ？
実績値と計画値の系列を分けたら色は変えられるけど、
ひと月の幅がやたらと広がってしまうので、
実績月/計画月の判断をシート上のセルで計算して、
それを頼りにVBAで色を変えることくらいしか
思いつかない。

232 ：名無しさん＠そうだ選挙にいこう：2022/12/23(金) 17:31:00.24 .net

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

233 ：名無しさん＠そうだ選挙にいこう：2022/12/26(月) 08:13:37.47 .net

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

234 ：名無しさん＠そうだ選挙にいこう：2022/12/26(月) 08:32:29.91 .net

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

235 ：名無しさん＠そうだ選挙にいこう：2022/12/26(月) 08:40:16.37 .net

DAXのisblankを使う

236 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 02:47:33.99 .net

詳しい人おるん？ここ

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

237 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 06:41:08.90 .net

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

238 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 08:23:09.98 .net

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

239 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 19:16:46.33 .net

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

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

240 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 20:15:45.85 .net

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

241 ：名無しさん＠そうだ選挙にいこう：2023/02/23(木) 19:32:25.67 .net

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

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

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

242 ：名無しさん＠そうだ選挙にいこう：2023/04/20(木) 21:50:38.07 .net

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

243 ：名無しさん＠そうだ選挙にいこう：2023/04/24(月) 00:36:41.46 .net

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

244 ：名無しさん＠そうだ選挙にいこう：2023/04/24(月) 08:02:05.84 .net

そもそもそれバグか？

245 ：名無しさん＠そうだ選挙にいこう：2023/04/24(月) 08:12:12.97 .net

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

246 ：名無しさん＠そうだ選挙にいこう：2023/04/24(月) 19:47:36.75 .net

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

247 ：名無しさん＠そうだ選挙にいこう：2023/04/24(月) 23:34:43.18 .net

すげえの来ちゃった

248 ：名無しさん＠そうだ選挙にいこう：2023/04/25(火) 06:53:45.87 .net

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

VLOOKUPでもできないかと。

249 ：名無しさん＠そうだ選挙にいこう：2023/04/25(火) 20:44:18.56 .net

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

250 ：名無しさん＠そうだ選挙にいこう：2023/04/27(木) 22:14:38.20 .net

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

251 ：名無しさん＠そうだ選挙にいこう：2023/04/28(金) 05:55:30.09 .net

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

252 ：名無しさん＠そうだ選挙にいこう：2023/04/28(金) 08:12:46.53 .net

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

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

253 ：名無しさん＠そうだ選挙にいこう：2023/04/28(金) 10:20:50.05 .net

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

254 ：名無しさん＠そうだ選挙にいこう：2023/04/28(金) 13:09:34.60 .net

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

255 ：名無しさん＠そうだ選挙にいこう：2023/04/29(土) 09:30:55.87 .net

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

256 ：名無しさん＠そうだ選挙にいこう：2023/04/29(土) 19:29:05.45 .net

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

257 ：名無しさん＠そうだ選挙にいこう：2023/04/30(日) 21:17:43.50 .net

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

258 ：名無しさん＠そうだ選挙にいこう：2023/04/30(日) 22:21:52.64 .net

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

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

259 ：名無しさん＠そうだ選挙にいこう：2023/05/04(木) 15:37:23.40 .net

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


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

260 ：名無しさん＠そうだ選挙にいこう：2023/05/04(木) 15:59:34.63 .net

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

261 ：名無しさん＠そうだ選挙にいこう：2023/05/04(木) 16:02:50.50 .net

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

262 ：名無しさん＠そうだ選挙にいこう：2023/05/04(木) 19:20:03.72 .net

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

263 ：名無しさん＠そうだ選挙にいこう：2023/05/04(木) 20:14:18.31 .net

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

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

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

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

264 ：名無しさん＠そうだ選挙にいこう：2023/05/04(木) 21:26:21.36 .net

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

265 ：名無しさん＠そうだ選挙にいこう：2023/05/04(木) 21:41:44.90 .net

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

266 ：名無しさん＠そうだ選挙にいこう：2023/05/04(木) 22:00:16.82 .net

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

267 ：名無しさん＠そうだ選挙にいこう：2023/05/04(木) 23:04:13.08 .net

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

268 ：名無しさん＠そうだ選挙にいこう：2023/05/05(金) 21:35:12.90 .net

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

269 ：名無しさん＠そうだ選挙にいこう：2023/05/06(土) 08:30:19.91 .net

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

270 ：名無しさん＠そうだ選挙にいこう：2023/05/12(金) 07:31:39.40 .net

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

271 ：名無しさん＠そうだ選挙にいこう：2023/05/12(金) 11:11:08.97 .net

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

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

272 ：名無しさん＠そうだ選挙にいこう：2023/05/12(金) 12:45:44.93 .net

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

273 ：名無しさん＠そうだ選挙にいこう：2023/05/17(水) 18:14:43.80 .net

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

Microsoft Jpan destroyer

274 ：名無しさん＠そうだ選挙にいこう：2023/05/18(木) 23:07:29.26 .net

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

275 ：名無しさん＠そうだ選挙にいこう：2023/05/19(金) 21:31:18.51 .net

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

276 ：名無しさん＠そうだ選挙にいこう：2023/05/20(土) 10:04:59.78 .net

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

277 ：名無しさん＠そうだ選挙にいこう：2023/05/20(土) 12:07:29.56 .net

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

278 ：名無しさん＠そうだ選挙にいこう：2023/05/20(土) 12:42:19.18 .net

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

279 ：名無しさん＠そうだ選挙にいこう：2023/05/20(土) 14:40:14.51 .net

複製？参照じゃなくて？

280 ：名無しさん＠そうだ選挙にいこう：2023/05/20(土) 15:47:46.17 .net

>>279
はい
複製です

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

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

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

281 ：名無しさん＠そうだ選挙にいこう：2023/05/20(土) 21:02:05.11 .net

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

282 ：名無しさん＠そうだ選挙にいこう：2023/05/24(水) 06:51:24.40 .net

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

283 ：名無しさん＠そうだ選挙にいこう：2023/05/25(木) 18:43:27.97 .net

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

284 ：名無しさん＠そうだ選挙にいこう：2023/05/25(木) 20:55:40.89 .net

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

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

285 ：名無しさん＠そうだ選挙にいこう：2023/05/25(木) 21:30:41.91 .net

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

286 ：名無しさん＠そうだ選挙にいこう：2023/05/25(木) 21:49:31.03 .net

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

287 ：名無しさん＠そうだ選挙にいこう：2023/05/25(木) 22:24:12.84 .net

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

288 ：名無しさん＠そうだ選挙にいこう：2023/05/25(木) 22:28:06.99 .net

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

289 ：名無しさん＠そうだ選挙にいこう：2023/05/26(金) 16:42:48.05 .net

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

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

290 ：名無しさん＠そうだ選挙にいこう：2023/05/26(金) 21:32:07.23 .net

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

291 ：名無しさん＠そうだ選挙にいこう：2023/05/29(月) 19:49:38.16 .net

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

292 ：名無しさん＠そうだ選挙にいこう：2023/05/29(月) 20:19:53.36 .net

できる

293 ：名無しさん＠そうだ選挙にいこう：2023/05/29(月) 22:43:04.76 .net

パス変えるのと同じ

294 ：名無しさん＠そうだ選挙にいこう：2023/05/30(火) 01:30:36.14 .net

サンキュー
調べてみる

295 ：名無しさん＠そうだ選挙にいこう：2023/05/30(火) 22:37:21.20 .net

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

296 ：名無しさん＠そうだ選挙にいこう：2023/05/30(火) 23:56:27.22 .net

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

297 ：名無しさん＠そうだ選挙にいこう：2023/06/01(木) 20:08:58.26 .net

欧州の電力需給実績リアルタイム15分値のチャートがWeb公開されているんだけど、
https://energy-charts.info/charts/power/chart.htm?l=en&c=DE&stacking=stacked_absolute_area
これってPower BIベースなのだろうか？

日本の電力需給1時間値でマネして、
「折れ線グラフおよび積上げ棒グラフ」で再現できないかやってみた。

が、
1時間値の積上げ棒グラフの幅が、
最初はこのくらい狭く表示できたんだけど、
何かの拍子に広がってしもうた。

視覚化の「列」が積み上げ棒グラフのプロパティっぽいんだが、
「カテゴリの最小幅(px)」の最小値が「20」で、
これより細かくできない。

何かやり方あるのだろうか？

298 ：名無しさん＠そうだ選挙にいこう：2023/06/01(木) 20:10:35.43 .net

パワークエリでフォルダからブックを読み込む時にシート2のみ取り込む方法はありますか？

299 ：297：2023/06/01(木) 20:25:08.18 .net

ベースはPower BIではなく
これとか
https://www.highcharts.com/
これら
https://d3js.org/
みたいだった。
失礼。

にしても、Power BIで近づけることはできないかしら？

300 ：名無しさん＠そうだ選挙にいこう：2023/06/01(木) 20:36:36.70 .net

>>298
目的のフォルダ指定してPower Queryエディター開いたら、
ステップを最初の「ソース」だけ残す。

kind列でsheetだけ絞るフィルターかけて
Item列にsheet名が出てるはずだから
またフィルターでそれだけ絞る。

1行だけになったらData列だけ残して他の列を削除。
中の緑色の文字"Table"をクリックすれば、
目的のsheetが開く。
そのステップも自動で追加される。

あとは型の変更なり計算なり、
ステップを増やしていくのはお好きなように。

301 ：名無しさん＠そうだ選挙にいこう：2023/06/01(木) 20:41:47.20 .net

>>300
ありがとうございます😊
Bing君に聞いても上手く答えを出してくれなくて

302 ：名無しさん＠そうだ選挙にいこう：2023/06/01(木) 22:52:16.18 .net

テンプレみたいなのがあるサイトない？

303 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 06:09:29.87 .net

完全外部結合させた時にキー列のnullに右部のキー列を入れるにはどうしたらいいでしょうか？

完全外部結合と論理和は何が違うのでしょうか？
Bing AIくんお尋ねしたら完全外部結合は表を結合させるもので、論理和はAUBだから概念は違うけどどちらも全てという意味では似てると言われました

304 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 08:17:38.29 .net

マージする前の右と左のクエリーの、
それぞれの列はどうなっている？

305 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 10:00:39.71 .net

>>303
条件列作って、[キー列]がnullなら[右のキー列]、それ以外は[キー列]
キー列を削除
作った条件列をキー列の名前に変更

M言語を直接編集する気があるならキー列を直接変更する方法はあるよ

306 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 17:51:17.37 .net

>>305
M言語？はちょくちょく触らせてもらっています
それ自体は簡単そうなので気になりますね

話が変わりますがテーブルに読み込んでいるクエリだけ（？）データモデルに追加するを止めるようにしたらあれだけ出ていたメモリ不足エラーやその他のエラーがぱったり消えました
やはりバグなんですね

307 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 18:25:50.26 .net

>>306
= Table.ReplaceValue(前のステップ, each [キー列],each if [キー列] = null then [右のキー列] else [キー列],Replacer.ReplaceValue,{"キー列"})

308 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 18:32:00.29 .net

あれ？これで十分なのかな？
= Table.ReplaceValue(前のステップ, null, [右のキー列],Replacer.ReplaceValue,{"キー列"})
試してみて

309 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 18:32:02.67 .net

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


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

310 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 18:41:24.85 .net

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

311 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 19:36:50.80 .net

ちなみになのですが昨日サイトで完全外部結合した後にキー列をカスタム関数のifで結合すればいいという事のを見て自分で実践してみましたが＝が二つになってしまい、なんとかトークンが必要ですというエラーが出てきました
あれは何が問題だったのでしょうか？
BingAIに聞けばよかったですね

312 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 22:24:43.95 .net

>>307
eachが必要
= Table.ReplaceValue(前のステップ, null, each [右のキー列],Replacer.ReplaceValue,{"キー列"})

313 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 22:48:35.75 .net

>>311
「トークンが必要」というエラーは
詳細エディターで手入力でM言語式を書いたとき、
うっかり間違える「前のステップ」名。

でなきゃ、途中ステップの最後に "," を忘れたか、
最終ステップ（in前）の最後の記述に余計な "," を入れた
だったか。

314 ：名無しさん＠そうだ選挙にいこう：2023/06/03(土) 23:22:22.76 .net

>>313
そうだったのですね
ありがとうございます
まだまだパワークエリのM言語は触り始めたばかりでルールを知りませんでした

315 ：名無しさん＠そうだ選挙にいこう：2023/06/04(日) 13:13:19.77 .net

パワークエリのグループ化の集計方法は何が違うのでしょうか？

316 ：名無しさん＠そうだ選挙にいこう：2023/06/05(月) 12:30:31.25 .net

B列からH列まであって
それぞれの列の値は被らないとします
この場合B列からH列までを一つの列にまとめる事は可能でしょうか？

317 ：名無しさん＠そうだ選挙にいこう：2023/06/05(月) 18:50:38.07 .net

データモデルに追加はしない方がよさそうだね
なんでクライアントのメモリ食ってんの？

318 ：名無しさん＠そうだ選挙にいこう：2023/06/05(月) 19:09:10.88 .net

データモデル(PowerPivot)はデータをブックに読み込んでしまう
ファイルサイズ見ればわかるよ
PowerPivotの編集画面で表示されるデータがそのままブックに保存されてる
PowerQueryだけならそうならない

319 ：名無しさん＠そうだ選挙にいこう：2023/06/05(月) 21:44:11.31 .net

>>318
データモデルに追加はよく省メモリとか軽量化のための手段として紹介されていますが
全くの出鱈目だったんですね

320 ：名無しさん＠そうだ選挙にいこう：2023/06/06(火) 06:57:57.49 .net

データモデルは、
ワークシート限界の104万行以上を扱える。
Power Pivotと併用すると、計算が速い。
その代わり、データが大きいほどメモリーは食う。

321 ：名無しさん＠そうだ選挙にいこう：2023/06/06(火) 07:34:36.14 .net

>>320
リレーションシップだから処理が軽く早いんだと聞いていましたが違ったのですね

322 ：320：2023/06/06(火) 10:15:46.29 .net

たとえば、>>297 の
日本版電力エリア需給実績（1時間値）もどきをやると、
1時間平均値なので、1供給エリアごとに年間8,760行。

個別の供給10エリアはあって、
さらに、50Hz連系時間帯串刺し、60Hz連系串刺し、10エリア計時間帯串刺しで＋3エリア
都合、13エリア分になる。
年間：8,760行×13エリア＝113,880行/年。

ここまでの、Web公開csvファイルDLからクレンジング、加工、集計とかの下処理は、
Excelで個別にクエリーやVBAを使って月次処理。

公開開始の2016年度から2022年度までの7年間だと、797,160行。
3ヶ年だけ、蓄電池シミュレーション版も入れたら、104万行を超えてもうた。

で、まだ勉強しながら作ってる途中だけど、
Power BI Desktopから年度別Excelファイルを読みに行って、
統合してデータモデルに格納（そもそも、BIにはワークシートがない）、
これの月別1時間値推移の、積み上げ棒＆折れ線の複合グラフを作ると・・・

・スライサー切替えによるグラフ再描写が1秒弱。(第8世代Core i7のKなし)
・月次集計、年次集計、エリア間比較、電源構成比も楽ですよ
・BIの.pbixファイルだけなら130MBくらい
・ただし、BIだけでメモリーは1.4GB食ってます、
　制作途中でBIの中でクエリー更新すると、CPU負荷率とメモリー消費量がもっと跳ね上がって、ちょっと時間が掛かります、
　出来てしまえば、スライサー切替えによる再計算は速いです・・・

って話。

https://i.imgur.com/C74ul4i.png
https://i.imgur.com/omO9dQm.png
https://i.imgur.com/1QPmbIO.png

323 ：名無しさん＠そうだ選挙にいこう：2023/06/06(火) 16:36:36.54 .net

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

324 ：名無しさん＠そうだ選挙にいこう：2023/06/06(火) 17:13:19.88 .net

外せるよ。

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

325 ：名無しさん＠そうだ選挙にいこう：2023/06/06(火) 17:42:40.53 .net

>>324
ありがと！

326 ：名無しさん＠そうだ選挙にいこう：2023/06/06(火) 20:01:51.66 .net

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

327 ：名無しさん＠そうだ選挙にいこう：2023/06/07(水) 01:20:58.20 .net

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

328 ：名無しさん＠そうだ選挙にいこう：2023/06/07(水) 07:18:28.24 .net

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

https://i.imgur.com/bTwA4tg.jpg


https://i.imgur.com/z7yJTK3.jpg

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

329 ：名無しさん＠そうだ選挙にいこう：2023/06/07(水) 08:26:02.56 .net

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

よーく、覚えとけ。

330 ：名無しさん＠そうだ選挙にいこう：2023/06/07(水) 08:45:36.27 .net

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

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

331 ：名無しさん＠そうだ選挙にいこう：2023/06/08(木) 21:58:54.72 .net

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

332 ：名無しさん＠そうだ選挙にいこう：2023/06/08(木) 22:08:42.54 .net

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

333 ：名無しさん＠そうだ選挙にいこう：2023/06/08(木) 22:09:20.85 .net

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

334 ：名無しさん＠そうだ選挙にいこう：2023/06/09(金) 00:00:23.22 .net

かまってちゃんはお断り

335 ：名無しさん＠そうだ選挙にいこう：2023/06/09(金) 20:55:11.42 .net

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

336 ：名無しさん＠そうだ選挙にいこう：2023/06/11(日) 10:42:42.93 .net

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

337 ：名無しさん＠そうだ選挙にいこう：2023/06/14(水) 22:38:53.61 .net

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

338 ：名無しさん＠そうだ選挙にいこう：2023/06/14(水) 22:40:42.75 .net

どゆこと

339 ：名無しさん＠そうだ選挙にいこう：2023/06/15(木) 01:16:22.70 .net

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

340 ：名無しさん＠そうだ選挙にいこう：2023/06/15(木) 21:07:17.33 .net

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

341 ：名無しさん＠そうだ選挙にいこう：2023/06/16(金) 04:39:41.55 .net

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

342 ：名無しさん＠そうだ選挙にいこう：2023/06/16(金) 09:03:52.63 .net

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

343 ：名無しさん＠そうだ選挙にいこう：2023/06/16(金) 10:05:08.67 .net

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

344 ：名無しさん＠そうだ選挙にいこう：2023/06/16(金) 12:14:32.96.net

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

345 ：名無しさん＠そうだ選挙にいこう：2023/06/18(日) 10:08:57.13.net

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

346 ：名無しさん＠そうだ選挙にいこう：2023/06/19(月) 22:48:24.89.net

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

347 ：名無しさん＠そうだ選挙にいこう：2023/08/04(金) 09:28:17.84.net

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

348 ：名無しさん＠そうだ選挙にいこう：2023/08/04(金) 12:22:46.80.net

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

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

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

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

とか。

349 ：名無しさん＠そうだ選挙にいこう：2023/08/23(水) 09:28:23.14 .net

やっぱり、出たよ

Introducing Python in Excel: The Best of Both Worlds for Data Analysis and Visualization
https://techcommunity.microsoft.com/t5/microsoft-365-blog/introducing-python-in-excel-the-best-of-both-worlds-for-data/ba-p/3905482?ocid=usoc_TWITTER_M365_spl100004503643083

Microsoft、「Python in Excel」を発表 〜Windows向けベータ版でテスト開始
https://forest.watch.impress.co.jp/docs/news/1525532.html

350 ：名無しさん＠そうだ選挙にいこう：2023/09/26(火) 06:21:44.76 .net

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

351 ：名無しさん＠そうだ選挙にいこう：2023/10/02(月) 03:26:16.21 .net

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

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

352 ：名無しさん＠そうだ選挙にいこう：2024/02/06(火) 00:00:20.41 .net

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

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

353 ：名無しさん＠そうだ選挙にいこう：2024/02/29(木) 16:59:07.69 .net

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

354 ：名無しさん＠そうだ選挙にいこう：2024/02/29(木) 17:13:55.05 .net

あ、出来そうだわ

355 ：名無しさん＠そうだ選挙にいこう：2024/03/08(金) 15:47:22.05 .net

初歩的な質問かもしれんのですが、この動画https://youtu.be/tFrjr3IiIlM?si=nYS3GRwPgYwMprmO　見ながら勉強しててドリルダウンまで行ったんだけど何故か綺麗に左からGDP順でドリルダウにならず…国名の五十音順になってしまうのって何故だと思う？

356 ：名無しさん＠そうだ選挙にいこう：2024/03/08(金) 16:05:36.54 .net

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