【Excel】Power Queryを語るスレ【Power BI】 (30レス)
1-

1 2019/10/20(日) 11:59:28
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
ワークシート上の名前定義した特定セルを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
テーブル内の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
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
# にはサンプル云々の意味がある模様・・・
6 2019/11/03(日) 12:27:45
?同じフォルダ内に複数のEXCELファイルがある。
?そのすべてのEXCELファイルに全く同じ構成のテーブルがある(テーブル名、カラムの定義も一緒)。

そのすべてのテーブルをマージしたいんですが、ソース取得のコードってどう書けばいいでしょう?
7 2019/11/03(日) 17:16:19
>>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
>>6
で、コードは >>7 で出来た詳細エディターを参考に。
9 2019/11/27(水) 15:26:03
Power Pivot使ってる人ってどの位いるんだろう?
自分は何とか使えるようになったけど、セミナーに出席しても使えてそうな人殆どいなさそう...
10 2019/11/27(水) 16:04:13
>>9
日本語の解説書籍やWeb記事がまだ少ないからねぇ・・・

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

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

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

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

計算がバッチ処理・カスケード処理だから、
何十万行でも、普通のPCで出来るし。
VBAやPythonとの補完も出来そうだし。
11 2019/11/28(木) 00:59:16
パワーピボット使ってる。カラムの異なるデータがみるみる集計できるから多用してる。

でもあれも変なところで物足りなくて、時間表記でセル書式の[h]:mmなんかがサポートされてなかったり。
12 9 2019/11/28(木) 10:12:04
>>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
>>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
2016vbaでcsvから作ったクエリのrefreshallしても更新されないことがある?
うちの環境で一旦endしても更新されない時があるのは謎。
ボタン分けて継続の作業をしていますが、その間に手動ですべて更新しています
15 2019/12/12(木) 20:14:38
>>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
>>15
ありがとうございます
同様の事象があることがわかり安心しました。
仕事で使えると思って頑張って学習しています。
確認しながら進めて聞きます。
17 2019/12/18(水) 10:10:52
外部データ取り込みで他EXCELファイルのテーブルを読みに行く時、
そのテーブルのカラムのデータ型はanyで、nullも文字列も小数点以下の数値(正数・負数)も含むんだけど、
Table.ExpandTableColumnのタイミングで小数点以下が丸められてしまう。

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


なった事あるって人居ませんか?
18 2019/12/18(水) 11:33:18
自己解決したので勝手に語る。

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

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

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

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


他、関連するかどうかもわからないけど、勝手に型変更をしているステップがあって、Int64になってた箇所があった。この型も小数点以下の取り扱いが無く、丸められるので注意が必要。
19 2019/12/18(水) 17:20:14
>>18
thx.

取り込む時、手動で全列の書式は指定する。
M関数で日時を扱う時も、元の書式が違うと、
違う関数になるし。
20 2019/12/21(土) 00:08:19
読み捨てで読み込んだデータでは直接ピボット作れないのね
何十万行もあるデータファイルのデータ、容量を軽くしようて思ったけどダメだった

データモデルで読み込んでも半分も軽くならない
21 2019/12/21(土) 11:44:24
yahoo知恵袋に、「読み込み先を”データモデルへの読み込み”に変更して,ブッククエリのみを作製するとそのまま『PowerPivot』で読み込みできます」とあったのを見つけたんだけど、自分では未検証です。

↓ソース
Power Queryを使って、200万行ほどのデータを扱うことはできませんか?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
>>21
ヒントありがとう!試してみます。
23 2020/01/18(土) 14:22:44
プライバシーレベルの設定で実際にどのような影響(効果)が出ますか?
社内のネットワークドライブに置いてある複数のブックからクエリを作ってそれを結合する様な使い方です。
24 2020/01/26(日) 20:29:57
パラメータの管理ってどういう使い方が想定されてるの?
パラメータに設定されてる値の変更はクエリエディタ開かないとできないよね?
25 2020/01/26(日) 21:57:03
あれはクエリエディタで汎用の関数やクエリを作るときの道具だと思う
Power BIのレポートのパラメータ編集なんかやるときに使うものでしょ
26 2020/05/28(木) 23:39:01
容量の大きなテキストから2つのテーブルを作るとき、今はクエリを2つ作ってそれぞれ実行するので、2回データの読み込み待ちがあるのが気になります。
読み込み1回で出来るよとか、なにか時間を短縮する方法を知ってる人がいたら教えて下さい。
27 2020/07/28(火) 07:38:56
pdfファイルも読み込めることを今頃気付いた。

Webページの特定項目をXPath指定でもして読み込めないかしら?
28 2020/07/30(木) 18:39:44
これ読んで、辿るのもしんどいけど
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
これはExcelアドオンソフトなのかしら?

https://seotoolsforexcel.com/xpathonurl/
30 2020/08/28(金) 10:53:58
(備忘)
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なので、
元のクエリーがプロパティで「バックグラウンドで更新する」のチェックが外れていても
チェックが入った状態と同等で実効される(?)ため、
シーケンシャルに計算が実行されないのか?
1-
スレ情報 赤レス抽出 画像レス抽出 歴の未読スレ AAサムネイル

ぬこの手 ぬこTOP 0.469s*