X

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

0001名無しさん＠そうだ選挙にいこう
垢版 |
2019/10/20(日) 11:59:28.17
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/
006763
垢版 |
2021/09/11(土) 17:05:12.81
>>66
PowerQueryでなにか方法ありますか？
007063
垢版 |
2021/09/11(土) 18:22:02.99
>>69
おお、その手がありましたか
ちょっと試してみようかな
ありがとうございます
0073名無しさん＠そうだ選挙にいこう
垢版 |
2021/10/17(日) 21:29:55.20
>>72
テキストにして「2021/」を「R3」に置き換えるか、
西暦を「-2018」して頭に「R」付けるのはだめでつか。
てかExcelで読み込んで書式設定してやればいい気がします。
0074名無しさん＠そうだ選挙にいこう
垢版 |
2021/10/18(月) 08:05:27.92
初めての書き込みです
クエリとピボットどちらにもブックを開く時に自動更新がかかるようにしているファイルがあるのですが
ブックを開く→ピボット更新→クエリ読込 の順で処理されてしまうようでピボットが最新の状態になりません
クエリの読み込み結果を待ってからピボット更新とする方法はないでしょうか？
手動で更新すればいいだけの話なのですがそれでは自動化とはいえず困っています
0076名無しさん＠そうだ選挙にいこう
垢版 |
2021/10/18(月) 13:15:15.00
私もわからないことが。

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

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

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

仕方ないから、Q1を参照する式を埋め込んだセルを用意してそれをテーブル化し、Q2はそれを参照するようにしたけど、良い方法では無いしね。
0077名無しさん＠そうだ選挙にいこう
垢版 |
2021/10/18(月) 18:10:22.81
>>76
Query1の「クエリ プロパティ」は、
>>57 の画像にあるように
「バックグランドで更新する」
「すべて更新でこの接続を更新する」
の2つのチェックを外してる？
0080名無しさん＠そうだ選挙にいこう
垢版 |
2021/11/02(火) 20:12:42.49
同じ行に、同じ文字列が出た場合、左から読んで一番左のものだけ生かして、ほかはnullにする方法とかありますでしょうか。
パワークエリでできたらありがたいです。


A　B　C　A　D　C　E　C　D　F

↓

A　B　C　　　D　　　E　　　　　F

みたいなイメージです
どうぞよろしくご指導お願いいたします！
008280
垢版 |
2021/11/03(水) 06:58:14.95
>>81
ありがとうございます

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

具体的にはどんな比較列になるでしょうか　m関数を示していただければ幸いです
0083名無しさん＠そうだ選挙にいこう
垢版 |
2021/11/13(土) 15:57:00.01
日本語の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言語にこんな機能はあるのだろうか？
0087名無しさん＠そうだ選挙にいこう
垢版 |
2021/11/14(日) 08:20:09.13
>>84
グループ化では重複数そのものは出せるんだけど
たとえば重複数が3だったとして
ソートしてCOUNTIF(A$1:A3,A3)のように
1
2
3
という芸当は出来なかったような。

DAX関数だと出来るらしいが、今度はその後のクエリー処理が続かなさそう。
008883
垢版 |
2021/11/14(日) 10:33:08.91
>>84
「最新版を抽出する」という意味で、それらしき事例があったわ。
PowerQueryでやる方法は、ちょっとトリッキーだけど。

https://youtu.be/2DJI9V1kBqg
0090名無しさん＠そうだ選挙にいこう
垢版 |
2021/11/16(火) 21:26:07.71
誰か教えて下さい
フォルダから同じ体裁のデータを複数取得し、
クエリで一行目を削除して二行目をヘッダーに昇格させてるのですが、
どうしても2つ目以降のデータの先頭2行がレコードとして残ってしまいます
フィルタリングすれば消せるっちゃ消せるのですが、、、
これは仕様なのでしょうか？
内部的に先にデータを合体させてるからこんなことに？
0092名無しさん＠そうだ選挙にいこう
垢版 |
2021/11/16(火) 21:46:16.28
fxのクエリの詳細画面で、2行目と、接尾に2個あるin～の2個めの方と、一番最後の行、この3つを//でコメント化してあげると、普通のクエリとして認識されるから、その上で編集して、最後にその//を削除すれば編集完了。あとは本来のクエリ側で、問題なく順番にクエリが評価されていくかを確認して。
0093名無しさん＠そうだ選挙にいこう
垢版 |
2021/11/17(水) 00:22:27.51
勤務実績(従業員名、日付、残業時間)から残業時間を５日毎かつ従業員別に集計したい

Sumlfsを再現するにはどの関数を使えばよいですか？
0095名無しさん＠そうだ選挙にいこう
垢版 |
2021/12/16(木) 09:27:31.56
年月、商品、数量のリストが数年分あります
数量の多い商品順にランク付けしたいのですが年月別に集計するやり方がわかりません
どのように範囲指定すればよいでしょうか？
0098名無しさん＠そうだ選挙にいこう
垢版 |
2022/02/14(月) 20:50:10.87
これ四捨五入すると5が切り捨てられるんだけどどうにかならないですか？

Round関数 パワクエ
0.234 0.23 0.23
0.345 0.35 0.34 ←←←
0.456 0.46 0.46
0099名無しさん＠そうだ選挙にいこう
垢版 |
2022/02/14(月) 20:58:39.13
どっかの記事
パワークエリの四捨五入は「銀行型丸め」が適用されており、ワークシート上のROUNDと結果が異なる場合があります。
この対策として、M関数の「Number.Round」の丸めモードを算術型丸めになるように設定する必要があります。
0100名無しさん＠そうだ選挙にいこう
垢版 |
2022/02/14(月) 22:30:00.61
>>99
ありがとう御座います
色々検索しててAwayFromZeroを入れてみて一応期待する動作にはなってそうですがこれで果たして正解なのか。。
0101名無しさん＠そうだ選挙にいこう
垢版 |
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から始まる行番号}[列名]を使った何かか？
0104名無しさん＠そうだ選挙にいこう
垢版 |
2022/03/23(水) 11:26:04.52
僭越ながら教えてください。

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

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

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

※背景として利用したいテキストファイルが合計15000ほどあり各システム別に分けられているので一つのくぎりとしては100程度になります。
利用目的としてpivot機能も含むチェック業務利用です。
0107名無しさん＠そうだ選挙にいこう
垢版 |
2022/03/23(水) 14:24:28.16
>>104　
powerquery内でTable.Pivot関数突っ込めば列にその情報ソースを出力できないこともない。リボンに列のピボット？があるはずだから、それ突っ込めば希望に近いものができるかもしれないので自分で調べて。その整形自体おすすめしないけど。15000列もエクセル側が受け皿を用意できたっけね？
0108名無しさん＠そうだ選挙にいこう
垢版 |
2022/03/25(金) 22:58:56.33
誘導されてきました
エクセルのクエリで、一つのファイルにある複数のシートから、たとえば１つ目から４つ目のデータ（１０００行くらい）を１枚目を１から４、２枚目を５から８に転記していくにはどのような設定でやればいいでしょうか
0109名無しさん＠そうだ選挙にいこう
垢版 |
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

あとは知らん。
0110名無しさん＠そうだ選挙にいこう
垢版 |
2022/03/26(土) 07:09:05.40
>>108
VBAスレから誘導されたの見てて心配で見に来たけど
不特定多数のシートを扱うならクエリも多分動的制御が必要になるから結局VBAのお世話になると思うよ。

たらい回しにするつもりはないけど、
VBAで言ってることを実装出来るコードを書いといたので一応見といてくれ。
Power Queryは使っていないけどね。
0111名無しさん＠そうだ選挙にいこう
垢版 |
2022/03/26(土) 08:06:23.90
>>109
質問者自身が自分が何やりたいのか分かってないと思うから丁寧に回答したところで思っていたのと違うとかそんな事言われるだけになるぞ
お互い損するだけ
0112名無しさん＠そうだ選挙にいこう
垢版 |
2022/03/26(土) 12:52:40.95
>>110
普段はPower Query使ってるけど、
繰り返し大量処理だとメモリー爆食いするので
VBAも参考にさせてもらってるよ。

ヘルパークエリーでパラメータ使って
さらにその都度VBAでファイル保存の
合わせ技で効率良くなるかも知れない。
0113名無しさん＠そうだ選挙にいこう
垢版 |
2022/03/26(土) 22:07:01.78
この処理は難しそうということですね
ありがとうございました
0115名無しさん＠そうだ選挙にいこう
垢版 |
2022/03/28(月) 18:47:33.32
>>113
生半可Power Cueryでやろうとするから難しいだけ。
やる方法には適材適所というものがある。

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

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

まぁスレ違いだからこの話はここではこれ以上しないけどね。
0118名無しさん＠そうだ選挙にいこう
垢版 |
2022/03/29(火) 07:37:54.81
すみませんが、どうしてもわからないので教えてください。

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

「 ピボットされた列1 = Table.Pivot(削除された列1, List.Distinct(削除された列1[Source.Name]), "Source.Name", "Column1", List.Count)」
※上の式は「列のピボット」のGUIボタンで自動作成されました。
0119名無しさん＠そうだ選挙にいこう
垢版 |
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つマージかしら？　それだったら元表をソートすれば済んじゃいそう・・・
0120名無しさん＠そうだ選挙にいこう
垢版 |
2022/03/30(水) 00:29:25.11
>>119
レスありがとうございます。

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

List.Firstやとかではテキストが表示するのですが・・・
List.SourceやList.ReverseだとListになってしまいます・・・
違いは適用したステップが自動生成されているくらいだとは考えています。
0121名無しさん＠そうだ選挙にいこう
垢版 |
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
変更された型
ーーーーーーーーーーーーーーーーーーーー
0123120
垢版 |
2022/03/30(水) 09:53:33.54
>>122

おはようございます。

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

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

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

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

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

情報小出しすみません。
データ取得からのデータ成形したいだけなので、クエリが得意とすることだとは考えています。
0129名無しさん＠そうだ選挙にいこう
垢版 |
2022/04/02(土) 11:50:11.30
そう言えば、ADOだとワイルドカードが%だから、
Accessも%だろうと思ったら実は*で、
フザケンナちゃんと統一しろムキー!ってなったことあるわ。
0130名無しさん＠そうだ選挙にいこう
垢版 |
2022/04/11(月) 19:20:30.64
クエリ勉強用に書籍を購入検討していますが何かしら比較したことのある書籍あれば教えてください。


レスつくのが時間いると思いますので、
今時点は適当に購入してみて後日感想記載します。
0131名無しさん＠そうだ選挙にいこう
垢版 |
2022/04/11(月) 19:31:11.43
>>130
クエリってpowerqueryを略して言ってるの？
0136名無しさん＠そうだ選挙にいこう
垢版 |
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
0138名無しさん＠そうだ選挙にいこう
垢版 |
2022/04/27(水) 21:26:09.00
質問です

グループ毎に小計及び総合計を出したいですが、簡単に出来る方法はありますでしょうか？
ネットに乗ってるLetは使い方が良く分かりませんでした
0140名無しさん＠そうだ選挙にいこう
垢版 |
2022/04/27(水) 22:34:05.38
列数が20ほどありピボットテーブルで横に伸ばすと操作性が著しく劣るためパワークエリで直接編アレンジしてテーブル形式で出力したいです
機能の小計が使えれば良いのですがテーブル形式になってると使えないようです
0144名無しさん＠そうだ選挙にいこう
垢版 |
2022/05/17(火) 20:43:53.61
フィルターされた行 = Table.SelectRows(テーブル, each Text.StartsWith([#"RowName"], FilterValue))

変数RowNameがうまく認識されず、
「Expression.Error: レコードのフィールド 'RowName' が見つかりませんでした。」
となります。
どうするのがよいでしょうか？
0146名無しさん＠そうだ選挙にいこう
垢版 |
2022/05/17(火) 23:21:37.74
回答ありがとう。

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

角括弧 [] に変数を入れるときに、とくに気にすることはないと
思っているんですが、どうなんだろう？
0147名無しさん＠そうだ選挙にいこう
垢版 |
2022/05/18(水) 13:50:53.35
おおよそ認識の通り、角括弧内の文字列に演算子を含む場合には#"〇〇"としてあげるくらい。このエラーには関係ない。
StartsWithはRowNameにFilterValueが含まれるか否かをTrueかFalseで返す関数らしいからそこが問題かと。その意味で「その文字列で始まる文字列が無い」んでしょうね。これ以上はわからん
0148名無しさん＠そうだ選挙にいこう
垢版 |
2022/05/19(木) 08:23:07.10
>>147
ありがとう
もうちょっと考えてみますわ
0151名無しさん＠そうだ選挙にいこう
垢版 |
2022/05/31(火) 01:46:15.43
パワークエリで利用するデータはある程度整えてから利用した方が良いのでしょうか

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

仕方が無いので､パワークエリの扱いがまだよくわからないのもあって素データを編集しています｡
素のExcelで不要なセルを削除したりしてからマージします｡でも編集するのは面倒だし
素のデータを編集するのは好きではないけど､これは仕方ないことでしょうか｡
0152名無しさん＠そうだ選挙にいこう
垢版 |
2022/05/31(火) 12:15:52.30
PowerQuery自体にデータ整形の機能はあるけど
それにも限界があるかと。

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

Webページの自動認識である程度テーブルを分けてくれるので、
M言語に罫線も認識する関数があるのかしら？
htmlで配列を検出しているのかどうか、
まだ分からないけど。
0153名無しさん＠そうだ選挙にいこう
垢版 |
2022/05/31(火) 19:53:17.67
もともとクエリだからね。
データベース並みに構造化されたデータを加工するのが前提。
帳票的なデータをいじるのにはあまり向いてないわな。
0154名無しさん＠そうだ選挙にいこう
垢版 |
2022/05/31(火) 21:28:54.04
>>152
>>153

ありがとうございます
今日もう一度見直したらエラーやnullが原因で崩れてしまうことがわかり無事統合できました｡
今までは不要な行は手動で消していましたがフィルタ機能を使えばできましたね……
0156名無しさん＠そうだ選挙にいこう
垢版 |
2022/06/01(水) 16:52:33.88
質問が二つありますので宜しくお願い致します

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

2. クエリを削除しても、ピボットテーブルをデータモデルから
　　作成するときに、削除したはずのクエリがテーブルとして出てきます。
　　データモデルを完全に削除する方法を教えてください
0157名無しさん＠そうだ選挙にいこう
垢版 |
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上の当該シートタブを右クリックで削除。
0158名無しさん＠そうだ選挙にいこう
垢版 |
2022/06/02(木) 13:49:56.88
>>157
ありがとうございます。ピボットテーブルはやはり参照元にしない方が良いのですね。
確かに集計項目を変えた時にエラーが出ました。

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


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

上記の表を

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

このように加工して、その表を再びテーブルとして使用したいのです。
私はピボットテーブルを使ってしまったのですが、このような場合に使える方法がありましたら、教えてください。
0159名無しさん＠そうだ選挙にいこう
垢版 |
2022/06/02(木) 14:19:05.73
ピボットテーブルの参照元データはpowerqueryから見られないの？
powerqueryで参照元データを直接いじれば良いのでは？
0161157
垢版 |
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の模様。

これなら二次利用の参照元にも出来るべ。
0162157
垢版 |
2022/06/02(木) 22:08:25.55
>>161 の追記
2.と3.のクエリーは1.を参照
0163157
垢版 |
2022/06/02(木) 22:11:18.45
>>161 の訂正
100超と100未満じゃ、「100」が抜けてまう...orz
そのあたりはよしなに。
0164名無しさん＠そうだ選挙にいこう
垢版 |
2022/06/02(木) 23:14:56.46
質問させてください

折れ線グラフで毎日の温度データを表示しています
・基準値を超えた日に任意の文字を表示させる
・位置はデータに追従させる
ということはできますか
ヒントに入れてカーソルを合わせたら表示されるのではなく、常に表示した状態にしたいです
0165名無しさん＠そうだ選挙にいこう
垢版 |
2022/06/02(木) 23:33:25.04
>>164
「任意の文字」とは何？

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

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

ニューススポーツなんでも実況