2023/03/12

[Power Query] for ループのような処理を Power Query で行う方法

connpass や Doorkeeper の REST API を利用してIT勉強会のデータを取得して、Excel であれば JSON データを表形式のテーブルに展開し Pivot テーブルに取りこんで分析する、Power BI であれば、ファクトテーブルとして取りこんでレポート、ダッシュボード作成に利用する、などを過去のブログでも紹介しています。

その中で既に使っているやり方なんですが、意外にこの方法で悩んでいる方もいらっしゃるので、まとめて説明してみます。おおまかな手順は以下です。

1) APIを使ったデータ取得をパラメータを受け取って処理する関数にする
2) 渡したいパラメータ(for の 1 から n に相当)の要素をもったテーブルを作る
3) 要素をパラメータとして関数に渡し、結果をとる
4) エラーが発生している行を削除する
5) 関数の結果からピボットテーブルを利用する

ここから connpass の場合を例にとって説明します。Doorkeeper でも考え方は同じです。

1) APIを使ったデータ取得をパラメータを受け取って処理する関数にする

connpass の API の説明ページは以下で、そこでデータ取得のためのサンプルや、各種パラメータの説明があります。

connpass API リファレンス

https://connpass.com/api/v1/event/?keyword=JAWS のように keyward= に検索したいワードを入れることでレスポンスを JSON 形式で受け取ることができます。connpass の場合はレスポンスのデータ件数の初期値が10なので10件しか出力されないのですが、このcount パラメータは最大100に設定でき、かつ、初期値1の開始位置パラメータの start を設定することで 1-100、その次は 101-200、さらにその次は 201-300 と出力を受け取ることができます。URLは以下のようなものを生成できればいいわけです。

1-100件目を取得
https://connpass.com/api/v1/event/?keyword=JAWS&count=100&start=1
101-200件目を取得
https://connpass.com/api/v1/event/?keyword=JAWS&count=100&start=101
201-300件目を取得
https://connpass.com/api/v1/event/?keyword=JAWS&count=100&start=202

キーワードやcountは固定、start パラメータを上述のように変更してリクエストしすべての結果を貰って処理します。

以下は上述の 1-100件目を取得のURLを使って、connpass から JSON データを取得し、ワークシートにテーブルとして展開する手順です。VBAなどのコードを書かずにダイアログ設定だけで JSON をワークシートのテーブルとして展開できることがわかると思います。

[データ]タブの[データの取得と変換](これが Power Query) グループの [Webから] を選択して、URL の入力ボックスに上述のURLをコピーする。

Power Queryエディタが立ち上がって、API リクエストの結果、受け取ったオブジェクトがアイコンで表示されるので、ここで右クリックメニューから [JSON] を選ぶ。

レスポンス情報の「まとめ」が表示される。ここでわかるのは 1番目から開始して、100件を返信として受け取って、可能な返信の件数は 1,135件ある、ということです。JSONで受け取った100件分は、events の List にはいっているので、[List] をクリックします。

Listが展開されて [Record] となりますが、この Record をクリックしてしまうと1件分だけが展開されます。これを全件展開してたいので、リボンの左はしにある [テーブルへの変換] をクリックします。

[テーブルへの変換] 確認のダイアログが表示されるので、そのまま [OK] 

リスト形式のものが「テーブル形式」に変換されます。これによる赤丸の「展開」ボタンで、すべての行にある「レコード」が展開されます。

ここでレコードに含まれる内容が確認できます。このまま OK で構いませんが、[元の列名をプレフィックスとして使用します] のチェックをオフにして、column1 というプレフィックスを消した方が冗長な列名にならずにすみます。

100件分がテーブルとして展開されます。


リボンの [閉じて読み込む] をクリックすると、Powery Query を閉じて、ワークシート上に JSON のレスポンス結果をテーブルに変換したデータが作成されます。

これが基本形です。
この URL の start に受け渡す部分をパラメータ化して変更できる「カスタム関数」を作成します。

いくつかのやり方があるようです。ここでは以下のように「詳細エディター」で Powery Query の M言語を直接いぢります。

Powery Query エディターが閉じられている状態なら、Excel のリボンの [データ] タブの [クエリと接続] グループの [クエリと接続] をクリックし、クエリと接続 ペインを表示して、先ほど作成したクエリをダブルクリックして Power Query エディターを起動します。


[ホーム]タブの [クエリ]グループの [詳細エディタ-] をクリックします。

p1という文字列型のパラメータを宣言する以下の一部を let の前に入れます。URLの一部になるのでデータの属性は text (文字列) とします。

(p1 as text) =>

そして、URLの部分の start= の直後をダブルコーテーションで閉じて、&p1 を追加します。

ソース = Json.Document(Web.Contents("https://connpass.com/api/v1/event/?keyword=JAWS&count=100&start="&p1)),

[完了]ボタンで詳細エディターを終了します。

これでこのクエリはパラメーター入力が必要なクエリ関数になりました。
名前もわかりやすいものに変えておきましょう。右側の[クエリの設定]ペインの名前プロパティで変更可能です。

2) 渡したいパラメータ(for の 1 から n に相当)の要素をもったテーブルを作る

この作成した関数を使って、1,101、201、301、、、、とパラメータを入力して、すべてのデータをひとつのテーブルすると Excel 上では分析するデータソースとして使いやすいです。ワークシートに展開すると 100万行の制限がありますが、Pivotテーブルで利用するデータソースとして使う場合は、必ずしもデータをワークシートに接続する必要はなく、接続情報として保存することで Pivot テーブルのソースとして使えます。

さて、APIでリクエストをして何件あるかはそのとき時でかわります。ただ、上記で API を投げたとき、少なくとも現時点では 1,135 件あることは分かっているので、1,101,201,,,で、1401 とか、1901 まであればカバーできそうなのはわかっています。

そこで 1, 101, 201, 301... 1901 のパラメータリストを作り、そこのデータをパラメータとして関数に渡して、それぞれ100件分のデータを取りこむ、という手順を Power Query を使って踏んでいます。

まず、[データ]タブの[データの取得と変換]グループにある[データの取得]から、空のクリエを選択します。

19個のパラメータを作成するので、入力ボックスに ={1..19} と入力します。すると、1から19までの数字がはいったリストが作成されます。

リボンのテーブルへ変換をして、リストフィールドの数値を使って 1, 101, 201 を作る数式を使った追加の列を作ります。数式は n*100-99 を使ってみます。
テーブル変換した後で、[列の追加]タブの[カスタム列]を選択します。新しい列名はパラメータ変数名の p1 にして、使用できる列 Column1 をダブルクリックすると カスタム列の式に代入されます。そこに *100-99 を追記して [OK] をクリックします。


パラメータはテキストを渡すので、p1列名の横の [ABC123] ボタンを押して、属性をテキストに変換します。

3) 要素をパラメータとして関数に渡し、結果をとる
4) エラーが発生している行を削除する

ここからがカスタム関数を作った醍醐味です。[列の追加]タブの[カスタム関数の呼び出し]をクリックして、ダイアログを開きます。列名はそのままで、関数クエリのドロップダウンでは作成したカスタム関数が表示されるので、それを選びます。
関数を選ぶと、その関数のパラメータに何をいれるかを指定できるドロップダウンリストがでます。列名として指定した p1 をパラメータとして渡す設定をします。

すると関数名の列が新しく追加されます。

11行以降がデータが無いためErrorになるので、この行を削除します。追加列を選択した状態で、[ホーム]タブの[行の削除]から[エラーの削除]を選択します。

削除が終わったら、列名の左にある展開ボタンをクリックして、100件分のデータの展開をします。列名が長くなるので、[元の列名をプレフィックスとして使用します]のチェックを外し、[OK]をクリックします。(以降のサンプルの図は Column1 を外していない図になります。すみません)

各列の属性を適切なものにします。特に集計をしたい列は[10進数]とする、日付の列は [日付/時刻/タイムゾーン]を選んでから、[日付]を選び、[列タイプの変更]ダイアログで [新規手順の追加] を選択して、Excel が理解できる形式に変更してください。


最後は不要な列を削除します。(必要な列だけ選んで他の列を削除するという操作)

[閉じて読み込む]をクリックすると、Power Queryエディタを閉じて、結果をワークシートに展開します。1136行(=データは1,135)で、最初に確認したAPIの返答の result_available の数と一致していることがわかります。

5) 関数の結果からピボットテーブルを利用する 

ここまでくれば、あとは Pivotテーブルで集計できます。
すでにテーブル形式になっているので、[挿入]-[ピボットテーブル]で、この手順通りであれば [クエリ1]というテーブルが指定されている状態でピボットテーブルを作成します。
connpassの場合だと、started_at が開催日、accepted が申し込み人数で、Power Queryで日付データを Excel がわかる形式に変換しているので、以下のような Pivotテーブルが十数秒でできあがります。titleをカウントして何回の勉強会・イベントが開催されているかを集計しています。

この集計方法の最強にして最大の長所は「更新」できることです。一度、このピボットテーブルを作成してしまえば、[データ]タブの[すべて更新]をクリックすることで、再度 connpass に対して REST API を投げて、最新のデータを取得します。その間、Excel 下部のエリアに以下のような「バックグラウンド クエリを実行しています」メッセージが表示されます。

このメッセージが消えたら、ピボットテーブル上で右クリックメニューを表示して、そこから [更新] を選ぶことで、ピボットテーブルも最新になります。

注意すべき点

正確な集計にするためには keyword で取得した後のデータに「ゴミ」が混じっていないかのチェックを入念にしています。実際、この方法で取得したデータのテーブルはファクトテーブルであって、ある意味履歴のようなものです。対象とすべきデータなのかどうかのチェックはスタースキーマであればディメンジョンテーブル(マスターテーブル)と付き合わせるので、ピボットテーブル分析にいく手前で別のステップを踏んで、分析用のデータソーステーブルを作成します。[中止] などの言葉がタイトルにあれば省く、などの例外処理も気がついたら入れていくので、クエリは一度作ったら終わりではなく、常にデータの信頼性を担保するためのチェックをします。このケースの場合は、厳格な命名規則が適用されているわけではないので、毎週、毎月のように新規に追加されたデータから、クエリが正しいかを確認する作業をしています。

一方で、ゴミの有無だけではなく、データの取りこぼしが発生することも確認しています。理由ははっきりとわかっていませんが、一度取りこぼしたデータは次回の更新時にとってくることはないため、一次的なものというより keyword クエリにかからない connpass 側のデータの持ち方かと思います。この場合は強制的に event_id を指定すると詳細を取りこむことができるので、対象となるURL/event_idで強制取り込みリストを作り、そこを参照させて個別にデータを取りこむようにする工夫をしています。

過去の投稿と被っているところも多々ありますが、何かしらの参考になれば幸いです。

2022/04/24

[Power Query] OneDrive パーソナル (onederive.live.com) の Excel ブックに接続する方法

Power BI は OneDrive for Business (中身は SharePoint で、URL に my.sharepoint.com が含まれるもの) のフォルダへの接続を前提にしているようで、サービス名が同じでややこしいのですが、個人が使う OneDrive (URL に onedrive.live.com が含まれるもの)への接続は「簡単には」許していないように思えます。それはもう7年も前のことで、以下のような投稿をしています。このときは OneDrive パーソナル(と便宜上呼びます)への接続方法を見つけることができませんでした。(2015年当時)

Power Query から https 経由で SharePoint の Excel ブックを開く

Googleスプレッドシート(ベータ)に接続する

最近 Power BI ユーザーさんのブログで Google スプレッドシートがベータとはいえ、Power BI から取り込み可能になった、と知りました。[データを取得] から「Google」キーワードで検索すると、ベータですが Google スプレッドシートが表示されるようになりました。

Googleスレッドシートを表示している URL を使い、Google アカウントでサインインして Power BI に取り込めました。また、Power BI Service にアップロードして、資格情報(Google アカウントによるサインインと、プライバシーレベルを Organizational)の再設定を行うことで、[スケジュールされている更新] が完了することを確認しました。

コネクタのリファレンス Googleシート(ベータ)
https://docs.microsoft.com/ja-jp/power-query/connectors/googlesheets

OneDrive パーソナルにある Excel ブックに接続したい

Google スプレッドシート同様に [データを取得] で OneDrive といれても、それらしい、わかりやすいサービスは列挙されません。[OneDrive 内の Excelブック] があればベストなのですが、残念ながら、そういう用途は SharePoint という前提なのでしょう。

データ分析的なフィロソフィーとしては、手入力の Excel をデータソースとしたくないのはわかります。それをするなら Power Apps でデータを収集すべきで、生スプレッドシートをいじらせるのは「綺麗なデータを用意する」という観点からもあまりお勧めできないからでしょう。そう、目指すは以下のような世界。

Power Apps、Power Automate、および Power BI を SharePoint Online と統合する

しかし、そのデメリットを理解した上でも、Google スプレッドシートが OK ならば、簡単に利用できる OneDrive パーソナルに保存した Excel ブックの Power BI への取り込みは世界中の Excel ユーザーと Power BI ユーザーが感じているはずで、2022年にもなれば何らかの解決方法があるのではないか?と思い、少しだけしつこく探して、、、見つけました! 2017年の投稿で、たしかにこの Embed の機能が2016年前後に追加されたのを覚えています。(訂正: Excel Web App で 2011年には存在していました)

PowerBI support for OneDrive Personal as data source
https://community.powerbi.com/t5/Power-Query/PowerBI-support-for-OneDrive-Personal-as-data-source/td-p/129125

実際に試して、上述の Google スプレッドシートの手順同様に Power BI Servcie にアップロードして、[スケジュールされている更新] もうまくいきました。その手順をあらためて紹介します。

1) OneDrive の Excel ブックで右クリックメニューで [埋め込み] を選択します

2) この操作がはじめての場合は URL生成のメッセージが右ペインに表示されるので [生成] をクリックします。[生成] ボタンの下に書いてあるように、これによりサインインせずにこのファイルを埋め込んだブログやWebページで表示できるようになります。セキュリティに注意してください。

3) プレビューと iframe コードが表示されるので、この iframe のコード内の、resid= から &em=数 まで (width の前) をコピーします。

4) Power BI に Web ソースとして入力する URL を作ります。以下の文字列に、上記 3) でコピーした文字列を追加し、&app=Excel で閉じます。

https://onedrive.live.com/download? <文字列コピー> &app=Excel

上記の例だと以下のようになります。

https://onedrive.live.com/download?resid=6E1B8001F99D3597%2179199&authkey=AACEgb0W3bmJHXs&em=2&app=Excel

5) Power Query で Web からデータをインポートを選び、URLをコピーします。[基本] で構いません。[OK] をクリックします。


6) ワークシートにテーブル オブジェクトが含まれていればテーブル名が表示され、SharePoint Online (OneDrive for Business) と同じようなナビゲーションが表示されます。


7) この pbix を Power BI  Servcie にアップロードし、データセットの [データ ソースの資格情報] のプラバシーレベルで [Public] を指定。(これは Embed コードは「一般公開」の設定のため - このプライバシーレベルは仕事で使う場合は常に注意ですね)


8) 資格情報を設定することで [スケジュールされている更新] の設定が可能になる。


以上となります。

この設定は Excel の取得と変換の [Web から] でも利用できます。


足かけ7年のモヤモヤが解消されました。

ただし、繰り返しますが、セキュリティとしては匿名アクセス可能な一般公開の URL を生成しています。一般公開されているデータ、特に公になっても問題の無いデータに限られ、不安であれば、SharePoint Online/OneDrive for Business で組織アカウント サインインの元でのデータ活用に切り替えたほうが良いと思います。ベータの Google スプレッドシートが認証を通しているのも納得できます。Goolge スプレッドシートも有力な選択肢ですね。

既知かもしれませんが、参考になれば幸いです。

2022/04/19

[Power BI] カスタム関数をつかったQueryを含むデータセットが自動更新できない

(追記: Excel のブックをそのまま Power BI Service にあげて使用するのではなく、Power Query + Excel Pivot Table レポートを Power BI Desktop でレポートを書き換えています。Power Query クエリの多くをそのまま移行できるのは、モダンExcel + Power BI の組み合わせの使い勝手の良いところです)

データを元にしたレポートやダッシュボードを複数のユーザーと定期的に共有することは、Excel で工夫・時間をかけて実現できますが、Power BI Service を含む Power BI は、その目的のためにつくられたこともあり、Excel での苦労や時間を軽減してくれます。

特に、モダンExcel な使い方をしているユーザーにとって魅力に見えるのは、レポートのデータ更新の自動化 につきるかと思います。

以前紹介した投稿 [Excel] Excel で JSON データを読み込む で利用してる connpass を使ったレポートなども、個人でデータを確認する分には Excel は柔軟で使いやすいのですが、他の人と、定期的に共有するとなった場合の共有方法については非常に悩みました。Excel ユーザーとしては OneDrive の活用が理想だったと思いますが、OneDrive と PowerQuery (取得と変換)の組み合わせはあまり良くありません。OneDrive といえども他社のファイルサーバー/サービス同様にxlsxのファイル保存先としてしか使えず、メールやメッセンジャーなどで保存先のパスを共有しながら、画像を貼り付けるような使い方になってしまいます。

もし、クラウドに保存している xlsx の「データ更新」ボタンが定期的、自動的に押され、そこにあるデータを表示できたら、、、

これは Pivot Table レポートや Power Query を活用し、複数の人とそれらを共有しなければならないデータエンジニアがいつも思うことです。これを可能にするのが、Power BI Service のダッシュボードやレポートで、データセットの設定にある「スケジュールされている更新」です。


この設定を行い、レポートおよびダッシュボードを共有することで、自動更新された最新データの表示が可能になります。以下は17日、18日の設定時に発生したエラーを含む対応がうまくいき、データセットが自動更新された履歴です。成功すると [状態] が完了になります。


解決しなければならなかった課題は複数あったのですが、今回、関連する日本語の情報を見つけることがなかなかできなかったので、そのうちのひとつを今回ご紹介したいと思います。残りもおって紹介したいと思います。

エラーメッセージ

データセットの[更新スケジュールの設定] で表示されたエラーの内容は「現在、以下のデータソースが更新をサポートしていないため、このデータセットの更新をスケジュールできません、、、」「・Query1 のデータソース」でした。


Query1 は具体的なクエリの名前を指しているわけではありません。
さらに具体的な Query に含まれるエラーの原因を探します。上の図の青い文字のリンク「データセットの表示」をクリックすると、エラーが発生しているデータセットの更新の状況を確認することができ、[最新の情報に更新済み] に更新しようとした日時が表示され、その横に注意マークがあるのでそれをクリックしてみました。するとどうやらデータセットに含まれる「動的データソース」があるために更新されない、と明示的に書かれていました。


このデータセットには、動的データソースが含まれています。動的データソースは Power BI サービスでは更新されないため、このデータセットは更新されません。詳細情報: https://aka.ms/dyanmic-data-sources。のメッセージ画面

このダイアログで紹介している https://aka.ms/dynamic-data-sources のページを開くと、実は非常に詳しく Power BI におけるデータ更新について書かれているのですが、これを全部読み込んで理解するのはなかなかつらいものがあります。以下、このページの日本語の URL です。

Power BI でのデータの更新
https://docs.microsoft.com/ja-jp/power-bi/connect-data/refresh-data

今回のケースにおける問題となった動的データソースは、connpass から REST API を使い JSON データを取りこむ際につかった start パラメーターが原因でした。どうしてこれが原因であるかがわかったのは後述する YouTube 動画のおかげです。

    ソース = Json.Document(Web.Contents("https://connpass.com/api/v1/event/?keyword=JAWS&count=100&start=" & p1)),
しかし、この start パラメーターによるページングの機能を使って、対象とするデータを Power BI に取りこむ(import) ことをしなければ集計ができません。この時点で日本語の情報が少なかったので、Power BI の UI を英語に切り替えて、英語でエラーメッセージやダイアログのメッセージを拾って検索することにしました。

ヘルプは上級者も含むユーザーを対象としているため、解決につながる直接的なイメージをその段階では思い浮かべることができなかったかもしれませんが、今思うとこの段階で Web.Contents のヘルプを読み込むべきでした。以下のヘルプの 例1 が解決方法だったのです。

Web.Contents
https://docs.microsoft.com/ja-jp/powerquery-m/web-contents 

解決方法は動的データソース表記を変更する

英語のキーワードのよる検索の試行錯誤の結果、以下の YouTube の解説ビデオを見つけることができました。

Making sure your Power BI web data source refreshes online (RelativePath, Query, BrowserContents)

上記の YouTube をみて、それから Web.Contents のヘルプをみると ? をつかったパラメーターの表記は NG で、ダイナミックURLを受け付けない静的ベース URL に対して、RelativePath や Query プロパティをつかった方法を使えばいいことがわかります。

上述のソースは以下のような表記方法に変わりました。
    ソース = Json.Document(Web.Contents("https://connpass.com/api/v1/event/?keyword=JAWS&count=100",
        [Query=[start=p1]])),

複数のパラメータの表記方法などは Web.Contens のヘルプに書かれています。
これで、パラメーターの変数を変えたいために動的URLを使うカスタム関数を作成し、その関数がクエリに含まれるためにスケジュールした更新が失敗する、といったことを回避することができます。

以上、参考になれば幸いです。

2022/02/05

[Excel BI/Power Query] そのフィルター大丈夫ですか?そのピボットテーブル レポート本当に正しいですか?

かなり前に書きかけていた記事を追記・完成させて公開します。

Excel は大量のデータから欲しいデータを絞り込み、集計をするのは得意とするところですし、それを実現するために、SUMIFS 関数などの「数式・関数」を使ったり、オートフィルターやテーブル機能などの「機能」を使ったり、VBA を使ったプログラミングで実現するなど、さまざまな方法を使うことができます。それらの中でも「ピボットテーブル」は本当によく使います。

今回データを「絞り込む」ということに関して、以前、私自身がはまって、その理由が小一時間わからず、あらためて Excel の機能の特性を勉強させられたので、共有したいと思います。特に、ピボットテーブルと取得と変換 (Power Query) のクエリエディタを組み合わせて使っている方は、データの絞り込みの仕組みの違いを意識していないと、以下のような思わぬところで落とし穴にはまるかもしれません。

データソースからデータを絞り込む方法 - ピボットテーブル

実際の元データは4年分の勉強会開催のデータでしたが、ここでは簡単なサンプルを使って説明します。ピボットテーブルの元データとなるデータソースは外部の Web サーバー上から取り出すテーブルですが、Excel 上にサンプルのテーブルを作り、それを元データとするピボットテーブルを作成します。
テーブルからピボットテーブルを作成する
このケースであれば、全部で9件分のデータをピボットテーブルでは「タイプ」のAからFにまとめて集計しています。元のデータソースは「テーブル」で作成しているので、11行目に新しいレコードが追加されても、ピポットテーブルは「更新」するだけで、元データソースの範囲を再指定することなく、追加された新しいレコードを含めた集計結果をピボットテーブルで返します。(これも知らない人が多いので注意です!)
元のデータソースにレコードを追加して、ピボットテーブルでレポートを更新する
いつもやっている、見慣れた操作とその結果です。
このピボットテーブルレポ―トから、タイプの A と B の集計の必要がない場合は、ピボットテーブルの「フィルター機能」を使うと思います。ピボットテーブルで絞込みをするための方法は2つの方法があって、1つは行ラベルにある矢印ボタンからフィルターを設定する方法、もう1つはピボットテーブルのフィールドリストにある「フィルター」を使って絞り込む方法です。ここでは行ラベルのボタンを使ってフィルターをかけてみます。
行ラベルのボタンでフィルターを設定する
何も問題ありませんよね。
「このレポートを作成・提出して終わり」といった Excel の使い方であれば、ピボットテーブルを使って作成するレポートはここで終わりだったと思います。

自分のケースの場合は、上記のサンプルのように Excel のテーブルが元データソースではなく、WebサービスからAPIで取得するものでした。その為、データを取得し、絞り込むところは取得と変換(旧Power Query)のクエリエディタで行っていました。結果、元のデータソースに日々データが追加されるような場合でも、不要なデータ(上記の例ではタイプAとB)を「Power Query エディタ-」で除外し、取得と変換の「更新」ボタンで最新データを取りこんで、新規に追加されたデータをピボットテーブル レポートに表示していました。

さて、サンプルに戻り、上記のケースと同様の状況を作ってみます。データソースのテーブルに新しいデータを追加して、ピボットテーブルを更新してみます。ピボットテーブルではAとBを除くフィルターが設定されている状況です。
データを更新、追加してピボットテーブルを更新する
タイプGの30、Bの30、Hの100を追加します。ピボットテーブルを更新することで、Bはフィルターでチェックを外しているので、集計対象になりません。Gは40から70に更新され、総計も110から140になりました。
ところが、Hのレコードはピボットテーブル レポートに反映されません。

この絞込みのフィルターの条件は「AとBをはずす」ではなく、絞り込むときに存在していたA,B,C,D,E,F,Gからチェックが付いている「C,D,E,F,Gだけを選ぶ」なのです。よって、新規にデータソースに追加されたHはピボットテーブル レポートに反映されないのです。

私がはまったのはまさにこのケースで、「ちょっと楽してピボットテーブル レポートの列フィルタで絞り込み」した結果、除外ではなく、そこに表示されているものだけを選択する、になってしまったため、その後でデータ/ レポートの更新を行っても、追加された新しいタイプのレコードはピボットテーブルに反映されない状態になったのでした。

クエリエディターの絞込みは「除外」(注:基本は・・・です)

ややこしいのは、この Excel の絞り込みの操作と、クエリ エディターでの操作がほぼほぼ見た目が同じということです。クエリエディタで行のフィルターをかける操作は、ピボットテーブルと同じような操作で、列名にあるボダンを使って指定できます。
クエリエディタ―の列名ボタンからフィルターを設定する
繰り返しになりますが、ピボットテーブルのフィルター設定と同じ操作でも、上記の場合、その意味は違っていて、■(すべて選択)が押されている状態から A, B を外した場合は、まさに「AとBを外す」という条件になります。詳細エディターで確認すると、以下のようなM言語による式が記録されています。

フィルターされた行 = Table.SelectRows(変更された型, each ([タイプ] <> "A" and [タイプ] <> "B"))

上記の式では、Table.SelectRows に それぞれの行の[タイプ]がAではなく、かつ、Bでもない、という条件を引数として与えています。<>は「等しくない」を表します。

よって、全件が入っている元データから「取得と変換」のクエリ エディターを使って行の絞り込みを行い、その絞り込んだ結果を使ってピボットテーブルを作る方法は、レポートに新しいタイプの新規の行がきちんと追加されているので、問題ありませんでした。この使い方の多くは、レポートに不適切なレコードが入ってきたら、クエリエディターのフィルターオプションでそのタイプを除外するように設定して、必要なデータのみレポートで利用していました。

トラブルのケースでは、ついうっかりピボットテーブル側で行の絞り込み条件を設定しそのままにしてしまい、たまたま元のテーブルに新しいタイプのレコードが追加されたことを「知っていた」ので、ピボットテーブル レポートが正しくないことに気が付きましたが、これ、実際のデータは数万件で、毎月追加される件数もかなりの数ですから、気が付かないことのほうが多いかもしれませんね。

しかし、このトラブル、これで終わりではなかったのです。

クエリ エディターの絞り込みは必ず「除外」ではなく選択する数による

ところが、別件でまたピボットテーブル レポートに新しいデータが追加されないケースに遭遇しました。今度はピボットテーブルの列フィルタは使っていません。そこでクエリ エディターを見ると、なんと除外ではなく、Excel のピボットテーブルのフィルタ同様に「選択」になっていました。


= Table.SelectRows(変更された型, each ([タイプ] = "C" or [タイプ] = "D" or [タイプ] = "E" or [タイプ] = "F" or [タイプ] = "G"))

上記の式では、タイプがCまたはDまたはEまたはFまたはGと等しいもの、という指定になっています。これでは11種類めの新規のタイプが追加されてもクエリ エディターによる絞り込みテーブルには追加されず、ピボットテーブル レポートにも追加されません。

サンプルの10種類のタイプをもつレコードを用意して検証してみると以下のような結果になりました。

除外が1つの場合
= Table.SelectRows(変更された型, each ([タイプ] <> "A"))

除外が2つの場合
= Table.SelectRows(変更された型, each ([タイプ] <> "A" and [タイプ] <> "B"))

除外が3つの場合
= Table.SelectRows(変更された型, each ([タイプ] <> "A" and [タイプ] <> "B" and [タイプ] <> "C"))

除外が4つの場合
= Table.SelectRows(変更された型, each ([タイプ] <> "A" and [タイプ] <> "B" and [タイプ] <> "C" and [タイプ] <> "D"))

除外が5つの場合(ここで条件式が変わる)
= Table.SelectRows(変更された型, each ([タイプ] = "F" or [タイプ] = "G" or [タイプ] = "H" or [タイプ] = "I" or [タイプ] = "J"))

以降除外が9までは「=」の指定

除外が10個の場合=すべて除外 (また <> が使われる)
= Table.SelectRows(変更された型, each ([タイプ] <> "A" and [タイプ] <> "B" and [タイプ] <> "C" and [タイプ] <> "D" and [タイプ] <> "E" and [タイプ] <> "F" and [タイプ] <> "G" and [タイプ] <> "H" and [タイプ] <> "I" and [タイプ] <> "J"))

なるほど、過半数以上になると条件式が「反転」するようですが、みなさん、これ、意識してますか? 

これ以来、自分は行のフィルター設定をいぢる場合は、ドロップダウンで表示されるGUIを使って指定しても、かならず fx の数式の条件が = なのか、<> なのかを確認するようになりました。

行フィルターを使用している、みなさんのクエリ エディターやピボットテーブル レポート、本当に新しいレコード含まれていますか? 

ちょっと怖いですね。

以上、参考になれば幸いです。
Powered by Blogger.

自己紹介

自分の写真
1989年新卒で日本IBMに入社しダウンサイジング担当としてホストコンピュータと繋げるオフコン、UNIX、PCサーバーのプロジェクトを担当。1997年 MSKK(現日本マイクロソフト)入社、NT4出荷に伴い企業向けサポート部門のビジネスマネージャーとして Excel 使いとなり、2002年 にMSMVPなどをサポートするユーザーコミュ二ティ部門を設立、部門をリード。2006年にMSKK退職後、企業向けのITトレーニング会社・団体に携わり、2014年頃よりPowerBI勉強会主催メンバーの一人として参画、そのコミュニティ活動で MSMVP for Data Platform PowerBI 2017受賞。https://mvp.microsoft.com/ja-jp/PublicProfile/5002635 同年にMVP Awardを返上し、アマゾン ウェブ サービス ジャパンに入社、コミュニティプログラム担当として現在に至る。