2017/05/23

[Excel BI] ピボット テーブルとメジャー

先日、Power BI 勉強会で「使える!クイック メジャー」というお題でお時間をいただき、メジャーの紹介をいたしました。勉強会にご参加いただいたみなさま、ありがとうございました。また、このブログで「Excel ユーザーにとってのメジャーとは?」というタイトルの記事を過去に投稿しています。参照数などから、この話題は興味のある人が多いようで、エクセル ユーザーも「メジャー」や Power BI といったキーワードが気になっているようですね。

Office 製品は、機能はもう十分です、追加は要りません、と言われた時期もありましたが、現在、Excel はちょっとした改善ではなく、ガラリと使い勝手が変わる機能強化が押し寄せてきているように感じます。

そのような機能強化の中で「これは・・・」と私も驚いた機能をご紹介します。先日の勉強会では、メジャーを理解するにはいったんExcelを(セルを基本とするワークシートを)忘れたほうがいいかもしれない、という紹介をしたのですが、実は、Excel のピボット テーブルで「メジャー」を使う、というお話です。

データ モデル利用のデメリット

VLOOKUP関数などを使わず、いわゆる「表引き」を実現する「リレーションシップ」の機能は、Excel 2013から標準機能として搭載されました。

[ブログ] テーブルのすすめ ピボット テーブルとリレーションシップ
https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html

[Office Support] Excel のテーブル間にリレーションシップを作成する
https://support.office.com/ja-jp/article/fe1b6be7-1d85-4add-a629-8a3848820be3

このリレーションシップ機能は、分析するデータを「データ モデル」に格納して、ピボット テーブルを作成しますが、当初、データ モデルにより使えなくなる機能がいくつかありました。その代表が「日付のグループ化ができない」と「集計フィールドが使えない」でした。
データ モデル利用によりグレイアウトされた集計フィールド
日付のグループ化などは機能拡張で後日対応しましたが、集計フィールドは日付のグループ化のような後日対応はありませんでした。集計フィールド同等の機能は、Power Pivot のメジャーや計算列で対応が可能だったからだと思います。

しかし、Power Pivot はすべてのエディションで利用可能ではありません。一部古いバージョン用を除いて、ダウンロードセンターから Power Pivot アドインのダウンロードもできなくなりました。
例えば、Office 365 Business の Excel で Power Pivot を利用することはできません。Office 365 ProPlus と Excel 2016 の Professional Plus のエディションに限られ、このエディションにはアドインとして最初から同梱されてます。つまり、所有している Excel のアドインに同梱されていなければ利用できない、ということです。

Excel 2016 のデータ モデルのピボット テーブル

では、Power Pivot を利用できないエディションでメジャーの作成ができないのか、ということです。
Excel でメジャーの作成を検索すると Power Pivot を使う情報がほとんどですが、Excel 2016 以降、Power Pivot アドインを含んでいない Office 365 Business 含め、いずれのエディションでも、ピボット テーブルでデータ モデルを利用すると、Power Pivot なしでも「メジャー」の作成が可能になっています。

以下のようなシンプルな列で構成されるテーブルを使って検証してみます。


このような表やテーブルからピボット テーブルを作成する時、[ピボットテーブルの作成] ダイアログ ウィンドウの [このデータをデータ モデルに追加する] のチェックを入れて、データ モデルにテーブルを追加します。ピボットテーブルは「データ モデル」に追加され、そこから作成されます。


複数テーブルの分析をしなくても、このチェックをオンにしてデータ モデルからピボットテーブルを作ることができます。
ピポット テーブル作成後、フィールド リスト ウィンドウに表示されるテーブル(サンプルでは [Table01])上で右クリックをすると、[メジャーの追加] を含むコンテキストメニューが表示されます。


[メジャーの追加] をクリックすると、メジャー のダイアログ ウィンドウが表示されます。ここで DAX を使ってメジャーの数式を作成・編集することが可能です。


集計フィールドの代替でメジャーを利用

Excel の集計フィールドの説明や解説では、テーブルの列の数値から消費税を計算したり、売上から原価を引いて粗利を計算したり、といった例が紹介されます。
データ モデルを使ったピボット テーブル レポートでは、集計フィールドのかわりにメジャーを使うことができますが、数式に相当する表記方法はメジャーの表記方法(=DAX数式)を使う必要があります。

たとえば、数値A列の数値に 0.08 を乗算する消費税の計算のようなものだと、集計フィールドを使うと以下のような数式になります。
集計フィールドによる消費税計算
メジャーでそのまま同じ式を使うと以下のようになります。

これは本物のメジャーなんですね。ですから、ワークシート上の構造化参照式のような数式はエラーになるんですね。='table01'[数値A]*0.08 という数式を入れた時のエラーは以下です。

この数式は無効または不完全です: 'Calculation error in measure 'table01'[mj2]: A single value for column '数値A' in table 'table01' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'。

'table01' の数値Aは単一の値として定義されていない(列で複数の行を持っている)から計算できません。min や max、count や sum などの集計関数を使って、単一の結果を取得して、それを使いなさい、というような内容です。

ピボット テーブル レポートの基本的な「動き」は、レポートの行ラベルまたは列ラベルとして設定された要素でグループ化をして集計するものです。よくあるのは行は製品区分、列は年度、のようなマトリクス表です。また、集計対象の要素が数値データで構成されていれば「合計」を計算し、数値以外を含んでいるデータであれば「個数」を集計します。これがいわゆる「暗黙のメジャー」というわけです。


ピボット テーブル レポートで計算が合わない原因

暗黙といわれるせいか、あまり意識されていないかもしれませんが、ピボット テーブルにおける数値列の計算は、まずは合計を計算します。たとえそれが列に対するパーセンテージを計算するものであっても、行ラベルのグループで「合計」をしてから、列合計を分母として、行ラベル合計を分子にして割合を計算、という順序です。(それゆえ実務では、サンプルとして使っている消費税の計算は、メジャーではなく、元のテーブルに列を追加して各行にて端数処理を行い合計すべきか、合計してから消費税を計算し、端数処理を行うかは注意しなければなりません)

ちょっと話がそれますが、ピボット テーブル機能が今ひとつ一般的にならず、人気がない、もしくは難しいといって避ける人が多いのは、個人的に上述の「まず、行・列のラベルのグループで合計しているんだよ」という暗黙の計算を意識していないせいではないかと思います。

ピボットあるあるの話で、かつ、間違った解説をしている記事がネット上にいくつかあるのですが、集計フィールドの解説で「受注金額は、単価*数量 で求められるので、ピボット テーブル レポートで 単価*数量 の数式の集計フィールドを追加すればいいんですよ。」と紹介しているものがあるのですが、これは完全な誤情報です。それ、本当に間違いです。Google での検索で結構上位にくるものでも、このような説明をしている記事を見ることができます。本当に注意してください。以下その解説です。

単純に以下を考えてみましょう。佐藤さんは、5月に、単価 100円のものを1個、単価1000円のものを1個売りました。佐藤さんの5月の売上はいくらでしょう?

100円*1個で100円と、1000円*1個で1000円、合計は 1,100円ですよね。

これをピボット テーブルの集計フィールドで、受注は 単価*数量 だからといって、受注の集計フィールドを作って計算すると、2,200円になります。2件しかありませんからすぐに間違いに気が付きますが、もし、件数が多い場合などすぐには気が付かないでしょう。

この計算は、まず、佐藤さんというレコード(行)を集めます。その次に、単価の列で合計します。単価は 100 と1000 が1つづつあるので 1100 です。次に数量の列で合計をします。1が2つあるので 2 になります。最後に集計フィールドで指定されている数式による演算を行います。1100 * 2 なので結果は 2200 です。

つまり、集計フィールドの例としてあげている数式の、受注金額=単価*数量 は、

受注金額 = SUM(テーブル[単価]) * SUM(テーブル[数量])

を計算しています。

これは、利益のような加算、減算であれば問題ありません。利益 = 売上 - 原価 の数式は、1行ごとに計算したものを最後に合計しても、総売上から総原価を引いても同じです。

ピボット テーブル レポートの計算結果が間違っていることがあるので使いたくない、という話を聞きます。「元データが汚いよね」というのも現実ですが、この行・列ラベルでの小計が優先されることを意識していない場合もあるかもしれません。

ここでメジャーに話が戻るのですが、その意味で、ピボット テーブルのメジャーの数式では、=[単価] * [数量] という数式を記入すると上述のエラーになります。明確に以下のように数式を書かなければなりません。そして、その段階で数式がおかしいことに気が付いてほしいわけです。

= SUM('テーブル'[単価]) * SUM('テーブル'[数量])

なお、受注金額の合計を求めたいのであれば、元データに単価*数量の列を追加するか、Power Query/取得と変換 のクエリエディターで計算列を追加し、あらかじめデータ モデルに計算データを取り込むことになります。Power BI Desktop の場合はデータを取り込んだ後で、計算列の追加を使ってもいいでしょう。

2017/05/12

[Power BI] 新しいライセンスと無料版の変更についての発表

日本では GW の真っ只中 5/3 に、Power BI の新しいライセンスと、それにともなう無料版の変更についての発表がありました。

Power BI はいくつかのサービスやコンポーネントから成り立つ「サービス ブランド名」です。今回の変更は、「Power BI Service」にかかわるライセンスの変更で、Excel アドオンの「Power Query」や「取得と変換」の機能そのものへの影響はありません。まず、ここがポイントですね。ただし、Power BI Desktop については、利用できる機能の範囲や容量がライセンスによって変わります。

以下は、英語版の情報と、日本マイクロソフトさんによる日本語訳の情報です。

Microsoft accelerates modern BI adoption with Power BI Premium

Power BI Premium の発表 および Power BI (無料) における変更点について

日本語訳情報のページにある以下の図がわかりやすいと思います。

Power BI Premium は Power BI Pro ライセンスと、ユーザー数カウントしない Power BI user ライセンスの組み合わせです。クラウド上の Power BI の「ノード」を占有することで、パフォーマンスの確保が可能で、ユーザー数でライセンスカウントをしないものです。ただし、ユーザー数が多くなれば、推奨されるノード数を増やさなければなりません。必要なノード数は「カリキュレーター」で求めることができます。

頻繁に使う、使わない、という一般ユーザーの想定にもよりますが、1ノードで1500人くらいには対応するようです。実際にカリキュレーターで試してみてください。なお、P1ノードと呼ばれる1ノードあたりの月額使用料は 545,000円 です。また、オンプレミスで Power BI Report Server の利用もノード数と同等分追加コストなしで利用可能になる、とのことです。

利用数が多くなれば、ユーザーあたりのラインセンス量が低くなることから、Power BI Premium の恩恵は規模の大きな組織、企業向けと言えるでしょう。

一方、Power BI Free は良い話と、注意しなければならない変更があります。
良い話は、多くの「Pro」と同じ機能が利用可能になる、という点です。
・接続できるデータソースの種類
・容量の上限
・更新頻度
・ストリームデータの行数制限

一方、Power BI Free の位置づけは「個人」(つまり、企業・組織での共同作業でなない)と明確に位置付けられたため、ダッシュボードの共有機能が利用できなくなります。
この意味は「共有の設定」ができないだけでなく、共有コンテンツの閲覧もできない、というものです。
上述については、Power BI の Community の掲示板のディスカッションから判断できました。
If I am a "Pro" user, can I share my dashboards with "Free" users? In our organization, there are really two types of individuals, Report Writers and Report Consumers. It would not make sense for us to pay for Pro licenses just for the users that we would be viewing some of the reports that we are putting together since they would not be using any of the other features of a pro license.
もし自分がProユーザーであれば、Freeのユーザーと自分のダッシュボードの共有はできますか?組織にはレポートを作る人と、レポートを見る人がいます。Proライセンスの機能を使わないユーザーであっても、レポートを見るためには Pro ライセンスが必要というのは理解できない、、、と言っているユーザーに対する管理人(MSFT)の返答は以下です。
That’s correct @nickjgill, as we announced this morning Power BI Premium tackles that issue. We have a calculator that can help organizations figure out which option makes sense depending on their mix of Pro Users and readers. Depending on size and usage, the solution might be different.
そのとおりで、その問題に対しての取り組みが Power BI Premium です。Proユーザーとリーダーの割合などカリキュレーターが支援します、、、

ソースはこちらです。

常時、Power BI Service を利用して、データインサイトの共有を即時にしたいのであれば、基本は Power BI Pro を1ユーザーあたり月額 1,090円でサブスクリプション購入する、共有コンテンツを利用する人数が多いようであれば、Power BI Premium の購入を検討する、という流れでしょう。ワークスペースにあるコンテンツ(ダッシュボード、App など)へのアクセスができないのが Free 版、と思って現時点は間違いないでしょう。

これらの変更は 6月1日からなので、今後さらに詳細の情報が公開されると思います。

こちらのブログも参考にしています。

2017/04/12

[Power BI Desktop] 期待のクイック メジャーが利用可能になりました

ついに待望の機能「クイック メジャー」が追加されました。

2017年4月現在、クイック メジャー (Quick Measures) は「簡易分析」という日本語訳が当てられていますが、近い将来、すべての訳は クイック メジャー に変更されそうです。

クイック メジャーは、Excel ユーザーにとってみればピボットテーブルの「計算の種類」です。Power BI Desktop ユーザーにとっては「自動DAX式生成機能」といえるでしょう。複雑な DAX 式を手で作るのではなく、ダイアログで条件などを指定することで「自動生成」してくれる機能です。

ピボットテーブル レポートを使って分析をしている Excel ユーザーにとっては、DAX 式学習のコスト削減になることは間違いありません。
ピボットテーブル レポートの「計算の種類」のリストから選択し、ダイアログボックスで条件設定するだけでできるのと同じ計算を、Power BI Desktop でやるには?という質問に、「DAX でイチから書くしかないですね」といつも言われ、その DAX 式が結構複雑になることから、Power BI Desktop の利用・移行を躊躇している人も多かったと思います。

たとえば以下のピボットテーブル レポートでは、計算の種類の「基準値との差分の比率」 - 「基準アイテム (前の月)」を使って、前月との差分を計算しています。


繰り返しになりますが、この列の計算結果は、ピボットテーブルの「計算の種類」からダイアログの設定のみで算出可能です。数式を弄ることは一切ありません。

これと同じ計算結果を Power BI Destop のレポートでは、テーブルのビジュアルで、クイック メジャーを使うことで、DAX を直接記述する必要はなくなりました。


このメジャーの DAX 式は以下になります。この DAX 式は、日本語のエラーメッセ―ジを含めて、すべて自動的に作成されたものです。


前月との差分の比率(MoM% = Month Over Month %) を計算するには、上記のような DAX 式を書かなければならないわけですが、それをクイック メジャーのダイアログで条件設定することで可能になるのは、すごく利用のハードルを下げてくれました。

クイック メジャーはまだ「プレビュー」機能です。バージョン 2.45.4704.442 64-bit (2017年4月) 版から利用可能ですが、 オプションのプレビュー機能のチェックをオンにする必要があるので注意してください。


とりいそぎ現時点でマイクロソフトから提供されている クイック メジャー の情報のページは以下になります。ぜひ参考にしてください。

Use Quick measures to easily perform common and powerful calculations (Preview)
https://powerbi.microsoft.com/ja-jp/documentation/powerbi-desktop-quick-measures/

2017/04/11

[Excel] 取得と変換が標準になります(更新チャンネル注意)

(注) 2017年3月の「取得と変換」データ タブの変更は更新チャンネル「Insider」限定です。
通常利用されている Office 365 の更新チャンネルではまだ適用されてません。
さらにパッケージ版(永続ライセンス)への変更についての発表はまだないようです。(2017年4月現在)

PowerBI エクセル アドインの Power Query は Excel 2016 より標準機能の「取得と変換」となりました、という話はこのブログで紹介してきたメインの内容でもあります。

Excel 2016 と Power Query (取得と変換)
https://road2cloudoffice.blogspot.jp/2015/11/excel-2016-power-query.html

その「取得と変換」について、さらに大きな変更が今年行われます。その日本語の記事がマイクロソフトより公開されていました。

統合された取得と変換

これまで外部データの取り込み機能として提供していた「外部データの取り込み」をリボンからはずして、「取得と変換」のみに変更する、というものです。2016年に標準機能になり、2017年には元の機能を置き換える、という流れですね。

ポイント1
2017年3月の更新は Insider 限定です。
Windows用 Excel2016 に対する更新(2017年3月)

通常は「Current(最新機能提供チャンネル)」や「Deferred の最初のリリース」などです。
更新チャンネル更新機能は以下のページが参考になります。
https://technet.microsoft.com/ja-jp/office/mt465751.aspx

チャンネルとリリース日については以下が参考になります。
https://technet.microsoft.com/ja-jp/library/mt592918.aspx

Office Insider はいち早く最新機能を利用し、検証するためのプログラムです。
https://products.office.com/ja-JP/office-insider

ポイント2
この更新は Office 365 サブスクリプションのみです。
上述の「統合された取得と変換」ページに以下の注意書きがあります。

そのため、Office Insider に登録していないユーザーの Excel では、まだこの変更は適用されませんのでご注意ください。

ご自分の Office が何かを調べる方法は以下のページの方法が参考になります。
使用している Office のバージョンを確認する方法

この方法で「製品情報 Office」の下に「サブスクリプション製品」とあれば、それは Office 365 の Office (笑) です。サブスクリプション製品という単語がなく、「ライセンス認証された製品」であれば、それは Office 365 ではない、永続ライセンスの Office (昔からの Office) です。

繰り返しになりますが、Office 365 サブスクリプション製品であっても、更新チャンネルの確認をしてください。最近は Insider プログラムの内容が一般公開されることが多く、更新チャンネルが「最新機能提供チャンネル」であっても、記事にある最新機能の更新確認ができないことも多々あります。

更新チャンネルの種類と特徴については、以前にブランチ(分岐)という呼び名も使われ、過去のネットの記事を参照すると混乱しがちですが、以下のマイクロソフトの記事を参考にしてください。

Office 365 ProPlus 更新プログラム チャンネルの概要

2017/04/03

[Office 365] Access Services 終了と PowerApps/Flow

Office ファミリーの1つで、今も利用者が多い Access に関連する、インパクトの大きい情報が公開されました。(2017年3月27日)

[Office Retirement Blog] Updating the Access Services in SharePoint Roadmap

Access Services は Office 365 から「リタイア」することになった、というものです。今年の6月以降は新規アプリケーション作成ができなくなり、来年の4月には「シャットダウン」する予定のようです。
利用していたデータは SharePoint リストに移すよう提案しています。
オンプレミスの SharePoint Server では、プロダクトライフサイクルポリシーにそって、次のバージョンでも Access Services や Access Web Apps 機能は提供され、もちろん、デスクトップの Access にこの決定は影響ないと上記のリンクでは紹介されています。Office 365 のサービスだけに影響します。

もしかすると「Access Services 」の説明が必要な方がいらっしゃるかもしれません。

Access Services は、SharePoint の機能(アプリ)の1つで、Access を使ってクラウド上の SQL Azure にデータを追加・更新・削除が可能で、そうして設計したデータベースを SharePoint に登録することでブラウザのみでデータを扱うこともできるようになります。

データベースのエンジンは SharePoint を経由して SQL Azure になり、テーブルの作成、データの入力や更新などは Access を使うことができるので、Access の知識でクラウドのデータベースを利用できるのがメリットといえます。

Access は1990年代から2000年初頭に「エンドユーザーコンピューティング」の代表格として扱われました。Access で簡易アプリケーションの開発をやられた方、それらを利用した方は相当多いんじゃないでしょうか。
エンドユーザーコンピューティングが目指していた、現場の人が、ノン・プログラミングまたはほんのちょっとだけのプログラミングで、業務を支援する仕組み開発は、業務アプリケーションもクラウドを利用したものが多くなるにつれて、ローカルの PC ではなく、インターネットやクラウド上に移っていくわけですが、Excel VBA や Access のような息の長いサービスは出てきてないように思われます。

技術や環境の移り変わりがものすごく早いため、ともいえるのですが、上述に近いエリアのサービスや製品は、記憶にあるだけでも結構あります。

InfoPath
(終了のお知らせ) https://blogs.office.com/2014/01/31/update-on-infopath-and-sharepoint-forms/
Visual Studio LightSwitch
(終了のお知らせ) https://blogs.msdn.microsoft.com/lightswitch/2016/10/14/lightswitch-update/
WebMatrix
(終了のお知らせ) https://blogs.iis.net/webmatrix/webmatrix-product-support-ends-on-november-1st-2017
Project Siena (Beta)
(終了のお知らせ) https://social.technet.microsoft.com/Forums/en-US/14cc68e9-7663-4611-a442-ba8d9d8834cb/welcome-to-microsoft-powerapps?forum=projectsiena

このように乱立気味だったエンドユーザー向けの開発・実行サービスは、どうやら PowerApps と Flow に一本化されそうな流れです。

PowerApps はスマホやタブレットといった新しいデバイスを念頭にしたアプリケーション開発サービスです。画面、フォームを作るもの、ですね。Flow はワークフロー処理を行ってくれますが、イベントによって何か処理をする、という感じでしょうか。

Excel も OneDrive を利用することで「アンケート機能」や「PowerBI連携」が可能になりますが、PowerApps と Excel の連携も可能です。Excel VBA と同じように Excel + PowerApps + Flow が使われるようになるかはこれからですが、Excel VBA の拡張や進化がないと公言されている以上、クラウドを利用するユーザーは PowerApps + Flow を浅くてもいいので学んでおいて損はないと思われます。以下が PowerApps と Flow の概要ページのリンクです。

PowerApps
https://powerapps.microsoft.com/ja-jp/tutorials/getting-started/
Flow
https://flow.microsoft.com/ja-jp/documentation/getting-started/


2017/03/03

[Power Query] 大量のデータベースから、セルに指定した条件でデータを取り出したい

Power Query/Excel の勉強会でお付き合いのある方から以下のような質問をいただきました。

PowerQueryの機能には、MSクエリで可能だったセルの値を使ったパラメータークエリのような機能はないのでしょうか?大量のデータベースから、セルに指定した条件でデータが取り出せて便利だったのですが。
昨年の春頃に、Power BI Desktop および Excel Power Query - 取得と変換 で、「パラメーター」の機能が実装されました。このパラメーターを使えば、設定した値(文字や数値、日付など)の中から選択して、データ抽出の条件として使えることはわかっていました。

Deep Dive into Query Parameters and Power BI Templates
https://powerbi.microsoft.com/ja-jp/blog/deep-dive-into-query-parameters-and-power-bi-templates/

期待している Excel 利用のシナリオは、取り込みたい条件を Excel のセルで指定、たとえば、入力規則で設定したドロップダウン リストから選択などで行い、その指定した条件で元のテーブルからレコードを絞り込んでワークシートに展開する、というような使い方だと思います。

パラメーターを使って絞り込み条件を変更する

Power BI Desktop であれば、新機能の「パラメーター」を使い、 A, B, C, D などのリストで作ったパラメーターで、リストから選んだ条件で絞り込みを行い、データ モデルに結果を Load します。

等しい条件にパラメーターを指定する
この条件を変えたければ、Power BI Desktop の [ホーム] タブ - [外部データ] の [クエリを編集] から [パラメーターを編集] を選び、表示されるパラメーターの入力 ダイアログボックスで、条件(パラメーター)を変更できます。[変更の適用] を行えば、変更した条件で元のデータセットからデータを読み込み、データ モデルを更新します。
[クエリを編集] - [パラメーターの編集] を選択
[パラメーターの編集] で表示される パラメーターの入力 ダイアログ
パラメーター変更後に表示される警告
Power BI Desktop の場合、このようにクエリ エディター ウィンドウを再度開くことなくパラメーターの変更が可能です。この使い勝手は想定通りだと思います。

ところが、Excel Power Query - 取得と変換 では、同様の行の絞り込み条件とパラメーター設定をしても、Excel のリボンから [パラメーターの編集] に相当するコマンドを見つけることができず、同じ操作手順を得ることができませんでした。(使用更新チャンネルは 最新機能更新チャンネル バージョン 1611 ビルド 7571.2109)

もちろん、クエリ エディターを開けば、パラメーターを変更でき、その変更したクエリを適用・保存することで、結果のテーブルの更新ができますが、想定しているシナリオの手順ではないと思います。

条件を「引数」として渡す、それは関数化

では、リボンに [パラメーターの編集] コマンドがない間はどうすればいいか。

クエリに引数で「条件として使う値」を渡せば解決できます。事実、パラメーター管理 (Query Parameters) 実装の前は、引数と関数の組み合わせで対応していたんですよね。

サンプルとして、条件の設定は「入力規則」を設定したセルにしました。といっても、見出しとデータ部分が1行で構成したテーブルとします。

大量データのテーブルから、絞り込み条件を付けたクエリを作成します。この場合の条件は、テーブルの列のフィルター オプションで、まずは明示的に A や B を指定してます。
次に詳細エディターを使って、let の直前に、引数 param1 を使う宣言をします。

(param1 as text) =>
let
 ・・・・・
in

そして、この引数名 param1 を使って、先ほど明示的に指定したステップの条件の値(入力規則で指定し、条件として選択している A や B や C)を入れ替えます。
以下の例だと、ステップは「フィルターされた行 =」であり、絞り込み条件の列として使用している [区分A] に定数として指定されている "A" や "B" などを param1 に置換えます。



この追加・修正を行い、詳細エディターを閉じると以下のダイアログが表示されます。


クエリ ウィンドウ(昔はナビゲーター ウィンドウと呼んでいました)の [>] をクリックしてウィンドウを展開すると、データを絞り込むために作成したクエリーの種類は「関数」になっていることがわかります。(Table01 クエリの前のアイコンが [fx] になっています)


すでにこのクエリは「関数」になっています。この関数クエリに引数として条件の値を渡せばいいのです。

このクエリ ウィンドウは [閉じて読み込む] ことによって、ワークシートには何もロードされない [接続専用] のクエリがブックに保存されます。(パラメーターの入力ボックスに絞り込み条件を指定すれば、その条件で絞り込んだテーブルがワークシートにロードされます)

この関数クエリを使うクエリの書き方は色々な方法がありますが、条件指定の1行データ入力規則のテーブル(列名は[条件]、データは1行)から作るクエリ作成手順を紹介したいと思います。

(1) 条件指定の1行データテーブル内にアクティブカーソルを移動して、[取得と変換] の [テーブルから] を選んで、アクティブカーソルがあるテーブルからクエリの作成を行うクエリ エディターを起動します。
クエリ ウィンドウを展開すると、直前に作成した関数クエリと、いま編集しているクエリの2つが表示されます。(以下のサンプルでは Table01 と pTable1)


(2) [列の追加]タブの [全般] - [カスタム関数の呼び出し] をクリックします。これでカスタム関数を使った列の追加をするためのダイアログ ウィンドウが表示されます。


関数クエリのドロップダウン リストで直前に作成した関数クエリを選択し、引数の param1 には、[条件](これは、1行データテーブルの「列名」として指定した文字です)を指定して、[OK] をクリックします。


関数クエリを使った新しい列 [Table01] が追加されました。


(3) 新しく追加された Table01 の結果が欲しいので、[条件] 列を削除し、[Table01] の「Table」を展開ボタンを使って展開します。元の列名は使用しないのでプレフィックスのチェックを外し、[OK] をクリックします。


この例では列の順序が元のテーブルとは変わってしまいましたが、[区分A] の値で絞り込まれたテーブルのプレビューが表示されます。


 [閉じて読み込む] でクエリの結果をワークシートにロードします。


(4) 条件を変えて、クエリを実行して、テーブルの更新をします。
まず、条件設定のテーブルで他の値を選びます。サンプルでは "D" を選びました。


(5) 次に関数クエリの結果でワークシートにロードされたテーブル上で、右クリックメニューから [更新] をクリックすると、テーブルが指定した条件で絞り込まれたものに更新されます。


いかがでしょう。たぶん、このようなシナリオを想定しているのではないでしょうか。

まとめ

パラメーター(Query Parameters)を使って、ダイナミックに変数、条件を変え、クエリ―結果も変えることができます。
クエリを関数にして、関数の引数を動的に変更することで、パラメーターの利用と同じことが可能です。

ただ、もしメモリー上に余裕があれば、データセットに対してフィルターをかける「取得と変換」や「Power Query」の処理は、データ分析用のデータ準備として最低限にしておいていいと思います。
ある条件で絞り込んだ、生の「テーブル(表)」がワークシート上で必要、というケースは、データ件数が多くなればなるほど非現実的ですよね。(印刷のため・・・は知っています。その昔、連続帳票で何千件のレコードを印刷、、、を経験したことがありますが(笑)。)

どうしても Excel の発想から、テーブルや表に対してオートフィルターで条件を指定して絞り込みを行い、そこから何かしらの処理を行う、という手順を考えてしまいます。
もちろんその手順に問題はないのですが、フィルターをかけて計算をする、という「一括りの処理」は、分析フェーズの DAX の FILTER関数などを使い、数式内で行うのが柔軟な利用方法だと感じています。

最大100万行のワークシートをデータセットの前提としていた旧来の Excel 利用の考え方とは別に、これからは、SSAS(SQL Server Analysis Services) のインメモリ表形式モデルの「データ モデル」を Excel に実装した Power BI 系のモダン Excel では、データ モデルにできるだけ「必要なデータ」を「必要な形に変換」して「ロード」する(Power Query - 取得と変換 - ETL機能としての処理)のが基本だと感じています。そのため SQL Server 2012 以降の xVelocity の最適化の圧縮技術を惜しみなく適用しているような気がしています。

(独り言・・・でも、そのモダンな使い方をすると Power BI Desktop をすぐに使えるよう(それで十分)になるんですよね・・・)

2017/02/28

[Power BI Desktop] 数値の 3桁カンマ、再び。

以前、Power BI Desktop のレポート、ビジュアルで「3桁カンマ」についての投稿をしました。

[Power BI Desktop] 3桁カンマをレポートのビジュアルでつけたい
https://road2cloudoffice.blogspot.jp/2016/08/power-bi-desktop-3.html

Excel では Ctrl+Shift+1 で3桁カンマがつきます、このショートカットはトグルじゃないので、3桁カンマを解除するには書式を標準に戻すショートカットの Ctrl+Shift+^ を使う、Power BI Desktop 上では、データの列でカンマを付けるか、レポートでフィールドやメジャーを選び、モデリング タブの書式設定のカンマを使う、という内容でした。

その中で、いまでも「あれ?」と思う落とし穴があり、まだ治っていない(?) ので、無駄に調べることがないよう、時短のためにご紹介します。

結論から言えば、暗黙のメジャーの1つである「カウント」の計算結果に3桁カンマを書式として適用することはできません


3桁カンマで表示されている、他の集計方法のビジュアル設定と同様なのですが、カウントだけ3桁カンマで表示ができません。カンマ表示されていませんが、モデリング タブの書式設定で [ , ] コマンドはオンになっています。

対策は暗黙のメジャーを使ってカウントするのではなく、明示的に DAX を使って個数をカウントするメジャーを作成し、書式設定で3桁カンマを指定します。


SSAS(SQL Server Analysis Services)から Power BI Desktop を使う人はメジャーを作成することが多いように思います。半面、Excel から Power BI Desktop を使い始める人はピボットテーブル的に使用し始めるので、集計方法から選択するように、用意されている集計方法を暗黙のメジャーとして使いがちです。
あまり騒がれていないのは、DAXでメジャーを作る人が多いからかもしれませんね。
いずれ表示されるようになるかもしれませんが、なにか設定がありそう、と無駄に探さないようにご注意ください。

少なくとも、去年の6月も同じ状況のようでした。
http://community.powerbi.com/t5/Desktop/Cannot-get-numbers-to-format-with-commas/m-p/42485#M16252

もしできるようになっていたらコメント残してもらえると嬉しいです。
Powered by Blogger.

自己紹介


PowerBI コミュニティ勉強会の 沼口 です。
https://powerbi.connpass.com/
最近の Excel は Office 365 のクラウドサービスと 連携する方向性が打ち出されています。この「Road to Cloud Office」ブログでは、Excel ユーザーの視点から Power BI Service や、Office 365 の活用方法を模索した結果をお伝えしています。
Microsoft MVP for Data Platform 2017-2018