【Excel】Power Queryを語るスレ【Power BI】 (356レス)
【Excel】Power Queryを語るスレ【Power BI】 http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/
上
下
前
次
1-
新
通常表示
512バイト分割
レス栞
1: 名無しさん@そうだ選挙にいこう [sage] 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/ http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/1
2: 名無しさん@そうだ選挙にいこう [sage] 2019/10/25(金) 01:10:27.86 ワークシート上の名前定義した特定セルをPower Query内で参照する方法。 「テーブルまたは範囲から」データの取得をするのと似ている。 PowerQuery use a cell values in a URL https://stackoverflow.com/questions/49281763/powerquery-use-a-cell-values-in-a-url 定義名を宣言して M言語内引数のパラメータ部分にその定義名を置き換える。 http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/2
3: 名無しさん@そうだ選挙にいこう [sage] 2019/10/25(金) 01:18:45.31 テーブル内の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}}) http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/3
4: 名無しさん@そうだ選挙にいこう [sage] 2019/10/25(金) 02:36:24.34 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[外部表の集計対象列]))) みたいな? => <= は「順次代入する」の意味? ""で囲まれた列名のアタマに付いた#は、列名にブランクが含まれている場合の特殊記号? http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/4
5: 名無しさん@そうだ選挙にいこう [sage] 2019/10/25(金) 17:57:32.47 # にはサンプル云々の意味がある模様・・・ http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/5
6: 名無しさん@そうだ選挙にいこう [sage] 2019/11/03(日) 12:27:45.07 ?同じフォルダ内に複数のEXCELファイルがある。 ?そのすべてのEXCELファイルに全く同じ構成のテーブルがある(テーブル名、カラムの定義も一緒)。 そのすべてのテーブルをマージしたいんですが、ソース取得のコードってどう書けばいいでしょう? http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/6
7: 名無しさん@そうだ選挙にいこう [sage] 2019/11/03(日) 17:16:19.22 >>6 行数を除いて、同じフォーマットの表であれば、 読み込むときに先に結合させては? 入り口は、このあたりも参考になるかと。 ExcelでPowerQueryを使ってデータ収集分析 https://qiita.com/mosugi/items/71df310b35db81722d1e 「データの取得−ファイルから−フォルダ−から」入って フォルダーパスを指定したら、「結合」ボタンも出る。 その中のメニュー「データの結合と変換」を選ぶと 読み方(xlsxファイルだと、どのsheet/範囲/テーブルか、余計な行は削除するか、ヘッダーはどうするか、カスタム列の追加、列の型等) をサンプルで指定できそう。 ※ たぶん、エディターで自動生成される「サンプル ファイルの変換」で読み方を指定するのかと。 Power Query内に自動でパラメーター関係のフォルダーやクエリー等が生成されるけど、 このあたりの仕組みは、当方まだ解明できていない。 これをやってから読み込めば 出来上がるクエリーが1つになり、以後の扱いが楽になるかと。 ただし、 1. xlsx表内の目的のsheet名が異なっていると、面倒かも? 同じテーブル名だったら直接指定できるかもしれない。 2. 目的のフォルダーにさらにフォルダーがあって、別置きのものが入っていたりすると、 それも読み込んでしまう模様。 http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/7
8: 7 [sage] 2019/11/03(日) 17:18:19.58 >>6 で、コードは >>7 で出来た詳細エディターを参考に。 http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/8
9: 名無しさん@そうだ選挙にいこう [] 2019/11/27(水) 15:26:03.51 Power Pivot使ってる人ってどの位いるんだろう? 自分は何とか使えるようになったけど、セミナーに出席しても使えてそうな人殆どいなさそう... http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/9
10: 名無しさん@そうだ選挙にいこう [sage] 2019/11/27(水) 16:04:13.51 >>9 日本語の解説書籍やWeb記事がまだ少ないからねぇ・・・ セル結合とかで、紙の帳票っぽく仕上げるとか、 IFの入れ子が多数な、複雑な関数を組んで作業列をなくすとかで、 見た目をよくすることばかりに血道を上げるより、 まず、テーブル形式(≒クエリー)がどれだけ便利かに気付いて貰えばと。 これ使うと、出来ることがだいぶ増える。 他の人がステップ(マクロ)の仕組みを理解するのは、 VBAよりも比較的わかりやすいし。 WebスクレイピングではPhythonより出来ることは限られるけど、 敷居の低さ、わかりやすさでは、こっちの方が良さそうに見える。 ビッグデータと呼ぶのが正しいかどうかは知らんけど、 特に、いろんなWebサイトで公開されてるデータ処理。 計算がバッチ処理・カスケード処理だから、 何十万行でも、普通のPCで出来るし。 VBAやPythonとの補完も出来そうだし。 http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/10
11: 名無しさん@そうだ選挙にいこう [sage] 2019/11/28(木) 00:59:16.42 パワーピボット使ってる。カラムの異なるデータがみるみる集計できるから多用してる。 でもあれも変なところで物足りなくて、時間表記でセル書式の[h]:mmなんかがサポートされてなかったり。 http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/11
12: 9 [] 2019/11/28(木) 10:12:04.47 >>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では考えてたけど。 今度試してみよう。 http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/12
13: 名無しさん@そうだ選挙にいこう [sage] 2019/11/28(木) 23:08:44.86 >>12 そう。 「メニュー−データ−データの取得と変換−Webから」 でURLを指定するヤツ。 Power QueryではPythonみたいに、 Web画面がテキスト入力させたりとかボタンを押させたりのものだとタイヤ雨出来ないけど、 複数ページあって、URLがページ数を反映しているものだったら、 対応できる模様。 こんなのとか。 [Power BI / Excel] 複数にまたがる Web ページからデータを取得する https://road2cloudoffice.blogspot.com/2017/07/power-bi-excel-web.html http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/13
14: 名無しさん@そうだ選挙にいこう [sage] 2019/12/12(木) 18:38:41.37 2016vbaでcsvから作ったクエリのrefreshallしても更新されないことがある? うちの環境で一旦endしても更新されない時があるのは謎。 ボタン分けて継続の作業をしていますが、その間に手動ですべて更新しています http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/14
15: 名無しさん@そうだ選挙にいこう [sage] 2019/12/12(木) 20:14:38.01 >>14 vbaを噛ましてないけど、Office soloのExcelで、 1つのcsvから多段にクエリーを展開していくとき、 「すべて更新」で上手くいかないことはある。 正直、謎。 自分なりの回避方法は、 1. シーケンシャルに再計算させたいクエリーはそれぞれのプロパティで 「バックグラウンドで更新する」:OFF 「すべての更新でこの接続を更新する」:ON https://i.imgur.com/ZBFAHRz.png 2. 「クエリと接続」の一覧で上から順番に並べる。 3. クエリーテーブルをsheetに置いてある場合は、 計算順に左からsheetのタブを並べ替える。 これでも「最後の再計算が終わった」と思っても、 まだ中間のクエリーの1つが再計算しだしたりする・・・ 保険で「すべて更新」は2回することにしている。 MSに提案してもいいかも、 もっと確実にしてくれと。 http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/15
16: 名無しさん@そうだ選挙にいこう [sage] 2019/12/12(木) 20:21:32.88 >>15 ありがとうございます 同様の事象があることがわかり安心しました。 仕事で使えると思って頑張って学習しています。 確認しながら進めて聞きます。 http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/16
17: 名無しさん@そうだ選挙にいこう [sage] 2019/12/18(水) 10:10:52.07 外部データ取り込みで他EXCELファイルのテーブルを読みに行く時、 そのテーブルのカラムのデータ型はanyで、nullも文字列も小数点以下の数値(正数・負数)も含むんだけど、 Table.ExpandTableColumnのタイミングで小数点以下が丸められてしまう。 追っていくと、SampleFileでファイルを呼び出した時点で丸められてしまっているようだ。 なった事あるって人居ませんか? http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/17
18: 名無しさん@そうだ選挙にいこう [sage] 2019/12/18(水) 11:33:18.06 自己解決したので勝手に語る。 読み込み元ファイルの問題だった。 でも謎が残る。 元データのテーブルの該当データは通貨型。単純な掛け算式が入っていて、丸め処理はしていないから、セル表示では丸められているが、内部的には小数点以下を持っている。 これを、表示設定で小数点をセル表示上で見えるようにしてあげたら、クエリの結果に小数点以下が表示されるようになった。 因果はわかったけど、仕様が謎。 データ型変更、ステップのどこにも入ってないのにね。 他、関連するかどうかもわからないけど、勝手に型変更をしているステップがあって、Int64になってた箇所があった。この型も小数点以下の取り扱いが無く、丸められるので注意が必要。 http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/18
19: 名無しさん@そうだ選挙にいこう [sage] 2019/12/18(水) 17:20:14.25 >>18 thx. 取り込む時、手動で全列の書式は指定する。 M関数で日時を扱う時も、元の書式が違うと、 違う関数になるし。 http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/19
20: 名無しさん@そうだ選挙にいこう [sage] 2019/12/21(土) 00:08:19.02 読み捨てで読み込んだデータでは直接ピボット作れないのね 何十万行もあるデータファイルのデータ、容量を軽くしようて思ったけどダメだった データモデルで読み込んでも半分も軽くならない http://mevius.5ch.net/test/read.cgi/bsoft/1571540368/20
上
下
前
次
1-
新
書
関
写
板
覧
索
設
栞
歴
あと 336 レスあります
スレ情報
赤レス抽出
画像レス抽出
歴の未読スレ
AAサムネイル
Google検索
Wikipedia
ぬこの手
ぬこTOP
0.005s