【Excel】Power Queryを語るスレ【Power BI】 (356ﾚｽ)
上下前次1-新
1(1): 2019/10/20(日)11:59 AAS
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
省2
2(1): 2019/10/25(金)01:10 AAS
ワークシート上の名前定義した特定セルを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 AAS
テーブル内の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 AAS
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)
省13
5: 2019/10/25(金)17:57 AAS
# にはサンプル云々の意味がある模様・・・
6(2): 2019/11/03(日)12:27 AAS
?同じフォルダ内に複数のEXCELファイルがある。
?そのすべてのEXCELファイルに全く同じ構成のテーブルがある（テーブル名、カラムの定義も一緒）。
そのすべてのテーブルをマージしたいんですが、ソース取得のコードってどう書けばいいでしょう？
7(1): 2019/11/03(日)17:16 AAS
>>6
行数を除いて、同じフォーマットの表であれば、
読み込むときに先に結合させては？
入り口は、このあたりも参考になるかと。
ExcelでPowerQueryを使ってデータ収集分析
https://qiita.com/mosugi/items/71df310b35db81722d1e
「データの取得−ファイルから−フォルダ−から」入って
フォルダーパスを指定したら、「結合」ボタンも出る。
その中のメニュー「データの結合と変換」を選ぶと
読み方(xlsxファイルだと、どのsheet/範囲/テーブルか、余計な行は削除するか、ヘッダーはどうするか、カスタム列の追加、列の型等)
省11
8: 7 2019/11/03(日)17:18 AAS
>>6
で、コードは >>7 で出来た詳細エディターを参考に。
9(2): 2019/11/27(水)15:26 AAS
Power Pivot使ってる人ってどの位いるんだろう?
自分は何とか使えるようになったけど、セミナーに出席しても使えてそうな人殆どいなさそう...
10(1): 2019/11/27(水)16:04 AAS
>>9
日本語の解説書籍やWeb記事がまだ少ないからねぇ・・・
セル結合とかで、紙の帳票っぽく仕上げるとか、
IFの入れ子が多数な、複雑な関数を組んで作業列をなくすとかで、
見た目をよくすることばかりに血道を上げるより、
まず、テーブル形式(≒クエリー)がどれだけ便利かに気付いて貰えばと。
これ使うと、出来ることがだいぶ増える。
他の人がステップ(マクロ)の仕組みを理解するのは、
VBAよりも比較的わかりやすいし。
WebスクレイピングではPhythonより出来ることは限られるけど、
省6
11: 2019/11/28(木)00:59 AAS
パワーピボット使ってる。カラムの異なるデータがみるみる集計できるから多用してる。
でもあれも変なところで物足りなくて、時間表記でセル書式の[h]:mmなんかがサポートされてなかったり。
12(1): 9 2019/11/28(木)10:12 AAS
>>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 AAS
>>12
そう。
「メニュー−データ−データの取得と変換−Webから」
でURLを指定するヤツ。
Power QueryではPythonみたいに、
Web画面がテキスト入力させたりとかボタンを押させたりのものだとタイヤ雨出来ないけど、
複数ページあって、URLがページ数を反映しているものだったら、
対応できる模様。
こんなのとか。
[Power BI / Excel] 複数にまたがる Web ページからデータを取得する
省1
14(1): 2019/12/12(木)18:38 AAS
2016vbaでcsvから作ったクエリのrefreshallしても更新されないことがある？
うちの環境で一旦endしても更新されない時があるのは謎。
ボタン分けて継続の作業をしていますが、その間に手動ですべて更新しています
15(1): 2019/12/12(木)20:14 AAS
>>14
vbaを噛ましてないけど、Office soloのExcelで、
1つのcsvから多段にクエリーを展開していくとき、
「すべて更新」で上手くいかないことはある。
正直、謎。
自分なりの回避方法は、
1. シーケンシャルに再計算させたいクエリーはそれぞれのプロパティで
「バックグラウンドで更新する」：OFF
「すべての更新でこの接続を更新する」：ON
https://i.imgur.com/ZBFAHRz.png
省8
16: 2019/12/12(木)20:21 AAS
>>15
ありがとうございます
同様の事象があることがわかり安心しました。
仕事で使えると思って頑張って学習しています。
確認しながら進めて聞きます。
17: 2019/12/18(水)10:10 AAS
外部データ取り込みで他EXCELファイルのテーブルを読みに行く時、
そのテーブルのカラムのデータ型はanyで、nullも文字列も小数点以下の数値（正数・負数）も含むんだけど、
Table.ExpandTableColumnのタイミングで小数点以下が丸められてしまう。
追っていくと、SampleFileでファイルを呼び出した時点で丸められてしまっているようだ。
なった事あるって人居ませんか？
18(1): 2019/12/18(水)11:33 AAS
自己解決したので勝手に語る。
読み込み元ファイルの問題だった。
でも謎が残る。
元データのテーブルの該当データは通貨型。単純な掛け算式が入っていて、丸め処理はしていないから、セル表示では丸められているが、内部的には小数点以下を持っている。
これを、表示設定で小数点をセル表示上で見えるようにしてあげたら、クエリの結果に小数点以下が表示されるようになった。
因果はわかったけど、仕様が謎。
データ型変更、ステップのどこにも入ってないのにね。
省1
19: 2019/12/18(水)17:20 AAS
>>18
thx.
取り込む時、手動で全列の書式は指定する。
M関数で日時を扱う時も、元の書式が違うと、
違う関数になるし。
20(1): 2019/12/21(土)00:08 AAS
読み捨てで読み込んだデータでは直接ピボット作れないのね
何十万行もあるデータファイルのデータ、容量を軽くしようて思ったけどダメだった
データモデルで読み込んでも半分も軽くならない
上下前次1-新書関写板覧索設栞歴
あと 336 ﾚｽあります
ｽﾚ情報 赤ﾚｽ抽出 画像ﾚｽ抽出 歴の未読ｽﾚ AAｻﾑﾈｲﾙ
ぬこの手 ぬこTOP 1.164s*