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

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

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

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

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

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

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

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

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

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

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

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

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

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

見事なチラシだった

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

あとは知らん。

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

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

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

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

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

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

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

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

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

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

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

優しい世界

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

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

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

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

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

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

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

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

それが5chクォリティ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

**120** · 2022/03/30(水) 09:53:33.54

>>122

おはようございます。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

パワクエ

**名無しさん＠そうだ選挙にいこう** · 2022/04/11(月) 22:53:07.06

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

**名無しさん＠そうだ選挙にいこう** · 2022/04/11(月) 23:31:11.66

ツマンネ
幼稚園児か？

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

いつもの機械翻訳か

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

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

> 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

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

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

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

質問です

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

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

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

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

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

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

いつもの機械翻訳の人？

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

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

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

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

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

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

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

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

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

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

回答ありがとう。

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

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

**名無しさん＠そうだ選挙にいこう** · 2022/05/18(水) 13:50:53.35

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

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

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

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

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

**名無しさん＠そうだ選挙にいこう** · 2022/05/25(水) 03:56:33.47

クエパワー

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

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

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

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

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

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

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

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

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

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

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

>>152
>>153

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

上記の表を

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

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

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

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

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

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

**157** · 2022/06/02(木) 22:03:48.12

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

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

**157** · 2022/06/02(木) 22:08:25.55

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

**157** · 2022/06/02(木) 22:11:18.45

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

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

質問させてください

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2. これらを通しで集計するには、別ブックから「データの取得 - フォルダから」で、
全てのファイルを結合出来る。
・「パラメータ」を使う「ヘルパークエリ」が自動作成されるが、
　カスタマイズはそんなに難しくない。
・参考動画
　https://youtu.be/RCmoho3jU8Q
　https://youtu.be/VxKoqBJWgRo
　https://youtu.be/TvIsvzGiQTU
　https://youtu.be/uLV85sauJRw
　等。
・このクエリーの中で「新しい順」にソートするステップを入れておけば良い。
・1. を毎週やっていれば、最新の通し集計は、このクエリーを更新するだけ。

3. 関数計算の展開は、2.のファイル内で各週csv等を
結合してから展開する方が吉。
1.の各週ファイル内に関数計算を仕込んでおいても良いけど、
追加や変更が出たとき、全部直さなきゃならなくなるから。

**171** · 2022/06/08(水) 01:43:56.70

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

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

**171** · 2022/06/08(水) 01:51:10.39

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

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

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

**168** · 2022/06/08(水) 02:18:46.15

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

**名無しさん＠そうだ選挙にいこう** · 2022/06/28(火) 11:20:25.93

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

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

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

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

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

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

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

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

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

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

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

それが基本機能です。

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

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

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

>>179
マージで

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

>>182
無粋だな

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

**名無しさん＠そうだ選挙にいこう** · 2022/09/11(日) 17:18:15.63

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

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

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

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

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

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

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

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

**名無しさん＠そうだ選挙にいこう** · 2022/09/13(火) 19:06:53.79

>>198
ん？

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

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

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

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

**sage** · 2022/09/28(水) 10:15:26.54

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

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

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

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

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

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

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

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

使えません

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

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

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

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

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

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

助けて。

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

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

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

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

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

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

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

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

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

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

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

アホすぎ

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

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

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

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

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

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

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

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

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

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

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

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

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

相手にしないことにした

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

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

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

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

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

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

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

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

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

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

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

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

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

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

とか。

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

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

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

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

PowerPivotでメジャー作成かな

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

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

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

>>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** · 2022/12/22(木) 22:38:20.74

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

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

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

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

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

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

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

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

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

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

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

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

DAXのisblankを使う

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

詳しい人おるん？ここ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

そもそもそれバグか？

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

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

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

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

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

すげえの来ちゃった

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

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

VLOOKUPでもできないかと。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Microsoft Jpan destroyer

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

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

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

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

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

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

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

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

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

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

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

複製？参照じゃなくて？

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

>>279
はい
複製です

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

できる

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

パス変えるのと同じ

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

サンキュー
調べてみる

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

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

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

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

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

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

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

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

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

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

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

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

**297** · 2023/06/01(木) 20:25:08.18

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

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