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

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

2017/02/27

[Power BI] Excel ユーザーにとっての「メジャー」とは?

先日の Power BI勉強会#3、ご参加ありがとうございました。
ご参加いただいた方で、これから Power BI を勉強したい、という人が多かったのですが、Power Pivot や Power BI Desktop を使いはじめると、早い段階で「計算列」と「メジャー」について学ぶことになります。「計算列」は Excel ユーザーにとっても馴染みがありますが、「メジャー」はなかなか理解しづらいという話をよく聞きます。

例えば、「Power BI メジャーとは」でインターネット検索すると、マイクロソフトの以下のコンテンツを見つけることができます。Power BI 関連のコンテンツは、このご時世でも結構日本語化されているんですよ。

Power BI Desktop のメジャー
https://powerbi.microsoft.com/ja-jp/documentation/powerbi-desktop-measures/

チュートリアル:Power BI Desktop で独自のメジャーを作成する
https://powerbi.microsoft.com/ja-jp/documentation/powerbi-desktop-tutorial-create-measures/

実は Excel ユーザーにとってメジャーを理解する近道、もしくは Power BI Desktop を理解するポイントは、Excel のピボットテーブルと比較するのが一番だと思います。

Power BI Desktop を操作するときは、テーブル、フィールド、レコードを扱いますが、「セル」という概念はありません。Excelユーザーが Power BI Desktop でデータを扱うとき、このセルのイメージが邪魔をすることがあります。
同じように、Excel ピボットテーブル レポートを作成する場合、表やテーブルをデータソースとして範囲指定した後は、テーブルのフィールドをドラッグ&ドロップしてピボットテーブル レポートを作ります。そこにセルの概念はありません。


この操作は、Power BI Desktop でもほぼ同じ操作性です。

ピボットテーブル レポートの作成で、数値しか含まないフィールドのチェックボックスをオンにして選択すると、そのフィールドが「数値」であることを Excel が判断して、Σ 値エリア(ボックス)に追加し、「数値」の列であることから「合計」の計算を自動的に行います。その結果はピボットテーブル レポートに自動的に追加されます。


これを「暗黙のメジャー」と呼びます。そうなんです。これが「メジャー」なんです。
つまり、メジャーとは、テーブルから何等かの計算結果を求めるための仕組みです。Excel の場合は、ピボットテーブル レポートを作成する際に、合計を求めたり、平均を求めたり、最大値や最小値をもとめる、個数を数える、といった「計算結果」を集計方法から選択することができます。


重要なポイントは、この設定では元のテーブルやワークシートに列を追加して数式を入れたり、集計行を追加しセルに数式をいれた結果を参照していません。元のテーブルやワークシートに対して、なんら列や行の追加・修正をせずに計算結果を求めています。

「暗黙の」と呼ばれるのは、数値列であれば自動的に合計が既定になり、その他平均などのメジャーが用意される、という意味です。そのため、Power BI Desktop では数値フィールド名の先頭にΣマークがつきます。

Excel のピボットテーブルの [値フィールドの設定] と [集計方法] で合計や個数、平均といった計算を指定するように、Power BI Desktop でも [値 フィールド] のドロップダウン リストから計算方法を変更することができます。


そして、Excel ピボットテーブル レポートで設定できる、もう一つの計算方法のグループがあります。[値フィールドの設定] の [計算の種類] タブを選択すると、以下のような計算の種類を選択することができるドロップダウン リストが表示されます。


比率を計算するもの、累計を扱うもの、順位を扱うものなどを選択することができます。
同様の計算すべてを Power BI Desktop でやるには、一部ダイアログの設定で可能なものもありますが、多くはメジャーを「自分で」作成することになります。その時に処理を記述するのが、ワークシート関数に似た DAX (Data Analysis eXpressions) と呼ばれるものです。

Excelユーザーからすると、ピボットテーブル レポートの「計算の種類」で設定するだけで結果を得られるものを、DAXを使った数式でメジャーを作る「手間」がかかります。そのかわり、「計算の種類」には無い複雑な計算を独自に作成することができるようになります。

過去に私のブログで上記と似た内容を投稿しているのでそちらも参照ください。

Power BI Desktop - Excel とどこが違うのか(2)
https://road2cloudoffice.blogspot.jp/2016/04/power-bi-desktop-excel-2.html

DAXについては日本マイクロソフト Data Platform Tech Sales Team Blog さんの DAX 入門シリーズが参考になります。
連載:DAX入門
https://blogs.msdn.microsoft.com/dataplatjp/dax/

先日の勉強会で「四半期がうまく扱えなくて・・・」という質問をいただきましたが、プロパティの設定変更等では対応できません。カレンダーテーブルを作成して対応します。この件は、上記の DAX 入門の「カレンダーテーブルの作成」に解説がありますので、是非ご一読ください。

Power BI Desktop のメジャーは、Excel ピボットテーブルの [集計方法] や [計算の種類] であり、かつ DAX による数式を作成して独自の計算をすることが可能な仕組みです。
GUI のオートフィルターのフィルターオプションで指定する条件なども DAX を使って数式で指定することが可能です。その結果を「メジャー」として定義して、さらに作成したそのメジャーを使って、他のメジャーで計算が可能です。このメジャーの作成に慣れてくると、分析用の事前準備ではなるべくデータにフィルターをかけずにテーブルを扱うことができたら、と感じます。
前にご紹介した、データ分析の事前準備の Power Query で、本当にデータの絞りこみが必要かどうかは、この DAX によるメジャー作成に慣れているかにもよるでしょうね。

2017/02/19

M言語 Power Query Formula Language

先日、Power BI 勉強会#3 で「M言語 Power Query Formula Languageとは?」というお話をする時間をいただきました。
冒頭「今回初めて参加された方はどのくらいいらっしゃいますか?」と伺ったところ、結構な方が初めての参加で、これから Power BI を勉強しよう、という人が多かったので、スライドなしで、Power BI の概要を前半の時間を使ってご紹介しました。
よって当初予定の用意したスライドをかなりの速足でご紹介してしまい、大変申し訳ないと感じています。

勉強会スライド 
https://doc.co/UY3RDb

昨日お伝えしたかった内容を再度まとめておきたいと思います。

データを取得し、準備するETL機能とクエリ エディター

Power BIは、Power BI Desktop や Power BI サービス(Web)などのアプリケーションやサービスを総称した名称ですが、Excel にも Power BI アドインというものがあり、その中でも Power Query は Excel 2013 ではアドインとして、Excel 2016 では標準機能の「取得と変換」となって利用可能になっています。
この Power Query の機能はいわゆる ETL(Extract, Transform, Load)機能と呼ばれる、データを抽出し、分析しやすいよう変換し、保存する、という役割を受け持つもので、Power BI Desktop もほぼ同じ機能を持っています。

その機能を実現するのが「クエリ エディター」です。


クエリ エディターは、Power BI Desktop および Excel の Power Query アドインまたはExcel 2016 取得と変換で共通の見た目、操作性を持つウィンドウとして起動します。

マクロ記録のようにクエリ エディターの操作を記録

このクエリ エディターは、そこで行われた1つ1つの操作をクエリの設定ウィンドウの [適用したステップ] として記録していきます。そのときの操作内容を記録する言語が M 言語と呼ばれたり、Power Query Formula Language と呼ばれます。
記録されているステップ名を選択すると、プレビューグリッドにはその操作の結果が表示され、プレビューグリッド上部の数式バーに M言語で記述された数式が表示されます。


操作手順を記録しているので、いまやった操作を「取り消したい」場合は、適用したステップに表示されているステップを削除すると、前の状態に戻ることができます。

M言語の特徴

クエリ エディターで利用する(記録される)M言語には、ある特徴があります。クエリ エディターでは、プレビューグリッドに表示された表やテーブルの操作が記録されるため、記録される M言語の関数は「テーブル」を扱ったものになります。
M言語による数式は、Power Query 関数 (もしくは M言語関数)を使いますが、その関数の表記は以下が基本形です。

クラス.関数(参照するステップ (, その他の引数))

参照するステップの多くは「直前の」ステップになります。クエリ エディターの操作は表を対象することが多いため、クラスの部分が Table の関数が多く使われます。

数式バーはステップ1つの数式のみ確認できますが、[詳細エディター] を使うことで、記録されているすべてのステップの数式の確認が可能です。行の絞り込みや、列の削除や追加の操作は、Table.○○○という関数が使われています。


Tableに関連する関数のパラメーターとして、日付に関する関数や、文字に関する関数などが使われます。Excel のワークシート関数の数式と同じように、関数のパラメーターとして他の操作を行う関数を組み合わせることができます。

たとえば、以下の数式は、フィルターされた行、というステップ名(および変数名)で、Table.SelectRows という関数を使って、行の絞り込みをします。絞り込みの対象は、直前のステップの[変更された型]というステップの結果のテーブルで、条件は、[日付]列の各行の日付データが Date.IsInCurrentYear 関数を使って「今年」であるものを絞り込んでいます。

フィルターされた行 = Table.SelectRows(変更された型, each Date.IsInCurrentYear([日付])),

詳細エディターで確認できるクエリは、複数の操作ステップを let ~ in で括って、in の直後に最終ステップ名(変数)を記述します。そのステップの結果がプレビューグリッドに表示されます。

詳細エディターとM言語を使ってゼロから記述するのか?

結論から言えば、それはハードルが高く、難しいです。
現時点で詳細エディターは、開発環境としての機能は皆無です。インテリセンスのような入力支援もなく、ヘルプ機能や、エラーチェックもないに等しい状態です。
苦労して動くものを作ったとしても、業務内における引継ぎを考えるとお勧めできるものではありません。

現状は、クエリ エディターで行った操作の記録として再利用し、修正が必要であれば最小にとどめることが肝要だと感じています。

ただし、カスタム関数と呼ばれるクエリを使うと便利な場合が出てきます。その作成では詳細エディターでM言語を使わざるを得ません。

カスタム関数を作る

関数なので、引数を宣言します。記述方法は let ~ in の直前に引数を (param1 as text)=> のような記述で設定します。文字列の引数を param1 という名前で定義して、let ~ in の中の数式に渡します。このように引数を設定したクエリは「クエリ関数」になり、他のクエリから参照することができます。


まとめ

空のクエリからM言語を使ってゼロからクエリを記述する、、、というクエリの活用はハードルが高く、エディターの支援機能などを見ても、まだ万人向けとは言えません。多くの操作はクエリ エディターのリボン コマンドから可能で、毎月のアップデートで機能追加されています。
また、この後工程での「分析」のフェーズでも、データの「整理」が可能です。特に「日付」に関しては、クエリ エディターでやるべきか、分析のフェーズで DAX のタイムインテリジェンス関数やカレンダーテーブルですべきか、といった判断が必要になるケースもあります。
データを収集し、分析し、レポートする、という Power BI の一連の流れの中で、機能的にできることを把握し、適切な場面で、適切な機能を使えるようになりたいですね。

以上、昨日お伝えしたかったポイントをまとめてみました。

2017/01/22

Power BI コミュニティ 勉強会#3 に登壇します

昨年末から企画していた Power BI コミュニティ 勉強会 #3 に登壇させていただく予定です。
日時は2月18日(土) 午後1時~6時で、場所は品川の日本マイクロソフトさんの会議室(セミナールーム)です。

https://powerbi.connpass.com/event/48844/

https://powerbi.connpass.com/event/48844/

が、すでに満席じゃありませんか!

前回の Power BI 勉強会 #2 は、セミナールームのキャパが 60 人だったため、次回は 90 人部屋でしましょう、すぐに申込みでいっぱいになったしね、、、と話していたのですが。今回も3日目くらいで満席になったようです。

みなさん、、、アジェンダというか、まだ誰が、何を取り上げるか公開していないんですけど(笑)
この勉強会に対する期待の大きさをひしひしと感じます。

今回も私の枠では Excel からの視点で Power BI との関わりをご紹介したいのですが、Power BI という「サービス ブランド」から Excel のプレゼンスはだんだん少なくなっているのが現状です。

2010年のころは、SSAS (SQL Server Analysis Service) のアドインとして Power Pivot が公開されて、Excel と SSAS の連携、そして Excel を主体とした BI の強いメッセージを発信していました。

しかし、現在 Excel アドインの紹介は Power BI のサイトで「Power BI Publisher for Excel」を除いて露出が相当減りました。また、Power BI Desktop が公開されて以降は「Excel は要らない」というメッセージも発信されるようになり、Excel と Power BI の距離感が変わったように感じられる人も多いと思います。

「Power BI」に無料版が登場、Excel不要の単体アプリとクラウドで動く (日経ITPro 2015/04)
http://itpro.nikkeibp.co.jp/atcl/news/15/042401440/

ただ、このことは Power BI と Excel の距離感が遠くなったわけではないと思います。
現実に Excel アドインだった Power Query は Excel 2016 以降では「標準機能」として搭載されるようになりました。データタブにある「取得と変換」が、これまでの Power Query です。

Power BI Publisher for Excel も、Power BI Service のダッシュボードを利用したい Excel ユーザーにとって、ちょっと難解で、手間がかかる手順を短縮し、共有したい「セル範囲」や「オブジェクト(グラフ)」を「名前」と同様の管理方法で Power BI ダッシュボードに登録、更新可能にしたのは、ある意味で Excel との連携をさらに「簡易化」したといえるでしょう。

Power BI Publisher for Excel | Power BI Service
https://powerbi.microsoft.com/ja-jp/documentation/powerbi-publisher-for-excel/

Power BI Publisher for Excel について、もうひとつ付け加えるとすれば、「データモデル作らなくても、連携できるんだ」の一言につきます。また、Power Query / 取得と変換においても、たとえば、ピボットテーブルのデータソースとして「リレーションシップ」で複数テーブルを扱う、という場合、2013 では「データモデル」を使うことが紹介されますが、現状はデータモデルは必須ではありません。

Power BI Service と密接に連携するためには「データモデル」は必須ですが、そうでなければ、データモデルを作らなくても同等のことができる、という「ゆるやかな連携」を提供していることは、良い意味で Excel との距離感を維持しようとしているように思えます。

ということで、Excel と Power BI はまだまだ関わりあって進化していくんでしょうね。

勉強会当日のネタはまだ構想中ですが、もちろん Power BI がらみの話になります。
すでに満席ですが、前回から考えると少しだけですがキャンセルも発生していましたので、キャンセル待ちも若干可能です。当日会場でお会いできるのを楽しみにしています。

2017/01/05

[Power BI] Cortana Intelligence Suite サービスとしての Power BI

本年も宜しくお願い致します。

今年も昨年以上に Power BI まわりはどんどんと進化していくような感じですね。Power BI だけでなく、他のベンダーさんの BI ツールも魅力的なツールになるべくどんどん進化しそうです。

Power BIと他のBIツールを比較したとき、よく比較ツールとして使われるのが、ガートナーさんのBIのMQ(Magic Quadrant)です。昨年2月の調査で、LEADERSに Tableau と Qlik と Microsoft が選ばれていました。

出典:Magic Quadrant for Business Intelligence and Analytics Platforms
Published: 04 February 2016 ID: G00275847
それぞれのベンダーの強みや注意事項がレポートにあるのですが、Microsoft の強みと注意事項はざっくりいうと以下だということです。

強み
・ 利用するための費用が安い
・ 圧倒的なチュートリアル、技術文書等の情報提供
・ あらゆるデータソースへの接続

注意
・ オンプレミスとクラウド、Excel Add-ins と Power BI Desktop、戦略の不透明さ
・ 高度な分析は Excel の 予測だけではできない - Azure/Cortana が担う
・ 今後改善・広がる可能性があるが、使われ方の幅に問題あり
・ 価格やパッケージが変化しすぎたため、BIや分析につよい営業力(体制)がない

BIツールという視点だけでみると、やはり Power BI の強みは「タダで始められる」というほどの費用の低さでしょう。フリーミアムモデルですからね。

一方で、Excel の予測シートや、予測関数の説明や紹介記事をみて、これで「予測できる!」と喜ぶ人がどれだけいるのでしょうか。Power BI が「セルフサービス BI」と言っているのは、「一人でできる」という意味合いだけではなく、「専門家がいらない」という意味で、現場のユーザーによるセルフサービス BI だと思います。ガードナーさんのレポートにもあるように、Power BIは、Power BI だけ見ているとミスリードしそうで、どうやら「Cortanal Intelligence Suite サービス」を見ないと全体像が見えないと思います。そこから見えるのは、データを分析するために、あらゆる種類の、膨大なデータを扱い、そのデータから「Machine Learning」を使って、予測モデルを導き出すこと、そして、その導き出したモデルを使って「視覚化」するツールが Power BI である、と明確に定義しています。

以下の図は Cortanal Intelligence Suite の説明で使われる図ですが、Power BI は右下の「Dashboard and visualizations」を受け持っているようです。


あまたあるデータの項目・種類から、注目している結果に影響があるデータを見つけ出すのが大変なんですよね。その因果関係(因果ではなく相関・・・)を証明するのが回帰分析などです。

Machine Learning は Power BI でデータ分析をやっていくと避けては通れないどころか、本質になるかもしれませんね。ビックデータになればなるほど、IoTになればなるほど、非構造化データまで扱おうとすればするほど、Machine Learning に頼らないと処理できないでしょう。

もしかすると Power BI アドインで、Excel から Machine Learning と連携してワークシート上でテーブルやグラフを作る、ということもあり得るかもしれませんよね。

Excel 視点から見ると、これまでは SQL Server の SSAS, SSIS, SSRS のアドインでしたが、Power BI 2.0 以降は SQL Server の影が見えなくなってきています。今後は、Cortana Intelligence Suite のサービスと連携する「アドイン」が出てきてもおかしくないでしょうね。

今後も Power BI は目が離せません。

2016/12/29

[Power BI] 日本政府環境局 JNTO さんのデータからのインサイト(6)

第5回からの続きになります。

日本政府観光局が公開している訪日外客数のデータを使って、Power BI Desktop や Excel、Power BI Service でインバンドのインサイトを探してみよう、という試みの第6回目です。

前回でフィルーターオプションと同じ操作性を持つ機能を使って不要な行を取り除き、必要な行のみを残しました。今回は「列の操作」を中心にご紹介します。

前回、[Column2] 列の見出し行(タイトル)をダブルクリックして [国名] に変更し、第1列の [Name] を同じく [年] に変更しました。


ところで、その横の列が [5月] になっていることに違和感を感じた人も少なくないはずです。その並びは、[5月] [伸率] [6月] [伸率_1] [7月]・・・と続き、[9月] [伸率_4] [1月] [10月] [伸率_5] [11月] ・・・となりました。

この順序は、1行目を見出し行にする前の ColumnX の [X] が原因です。


後に国名となるカラムの名前は [Column1] で、[5月] のカラム名は [Column10] です。[9月] は [Column18] で、その横の伸率は [Column19]、そして [1月] が [Column2] です。この ColumnX の数値 [X] の順番で並んでしまっているのです。1,2,3,4・・・ではなく、1,10,11,12・・・,2,20,21,22・・・ですね。

1月、2月、3月 ~と並べ替えたくなりますが、今回の場合、最後の処理で 「列のピボット解除」 を行って、[月] の列をつくり、その列のデータとして月を入れるので、途中で1月、2月~のように並べ替える必要はありません。

(1) 必要な列を選択して [他の列の削除]


第1列の [Name] を [年]に、第2列の [Column2] を [国名] に変更したら、次は必要な列を残します。不要な列の削除の方法は2つあって、1つはその通りのまま「不要な列をすべて選択して削除する」です。一方で必要な列は [年] と [国名] と [1月] から [12月] までの [月] の列だけなので、それらをすべて選択して [他の列の削除] を選ぶことで、不要な列の削除が可能です。というより、必要な列のみを明示的に指定して残す、と言ったほうがいいでしょう。

Ctrlキーを押しながら、列名をクリックすると、複数選択が可能になります。選択された状態で、右クリックのコンテキストメニューから [他の列の削除] を選べば、選択している以外の列が削除されます。


[列の削除] を選ぶか、[他の列の削除] を選ぶかは、元のデータがどのように追加・変更される可能性が高いかで変わります。

個人的な経験談からすると、Power Query を使い始めた当初は削除する列を選択して [列の削除] を行っていました。ところが、新しいデータに不要な列がたまたま追加されたため、削除する列を明示的に指定しているこの方法では、追加された不要列は削除されずエラーになりました。
必要な列が「変化する」ものではないので、この場合は、必要な列をすべて「指定」して、その他の列を削除する指定方法が最も「再利用に適した」方法でした。

訪日外客数のデータで、必要な列だけを残すと以下のようになります。


(2) 列のピボットを解除する


この [列のピボット解除] の考え方が、もしかしたら「難しい」と感じている人もいるかもしれません。
というのも、非常に残念ながら Excel 入門的なトレーニングで、最初の勉強用の Excel の表として「クロス集計表=ピボットテーブル」を入力するための表として紹介する教材が多いからです。


このような縦にも横にもデータの条件を入れて、そのクロスした位置(セル)にすべての条件を満たす数値を入れる表を「クロス集計表」といいます。たとえば、C2のセルは、「支店が東京で、担当が山田で、4月の数値という条件を満たす合計は 38」という意味です。

人間が見るにはとても見やすい表なので、まずは、空のクロス集計表を作り、罫線をきれいに引いて(罫線の練習)、小計には SUM 関数をつかったり、SUBTOTAL 関数を紹介したり(関数の練習)、見出しセルでは書式の変更でフォントや位置を変えたり、といったことをする教材です。

ただし、これは人間が見やすい「最終形の表」であり、いわゆる「ピポットテーブル」としての「結果の表」です。本来この結果の表を作成するための「元の表」は以下のような、リスト形式またはテーブル形式のようなデータでなければいけません。


 クロス集計表は「結果の表」であり、人間が見やすいように「修飾された表」です。クロス集計表に新しい支店や、新しい担当者、月のデータを追加するのは容易ではありません。それを苦労して修正しているケースを見ることもありますが、追加するデータはリスト・テーブル形式の「追加行」として追加し、このテーブルを元にしてクロス集計表を作る手順が、最も簡単で、最も正確で、最も間違いがない方法です。

もっと言えば、実際のデータはさらに細分化されている場合がほとんどです。
支店が東京で、担当が山田で、月は4月で、製品はAの売上が10、製品のBの売上が8、製品のCの売上が15、製品Dの売上が15で、合計が 38 といった感じです。SUMIFSの関数の題材としては面白いと思いますが、データ分析をするための「元の表」はクロス集計表ではなく、リスト・テーブル形式の表でなければなりません。

今回の題材である日本政府環境局さんのデータは、この「クロス集計表」です。残念ながら、このクロス集計表のままでは、さらなるデータ分析が難しいのです。

そして、このクロス集計表は「人間にとって見やすい」表であり、かつ「印刷に向いている」表でもあるため、官公庁が Web で公開・提供しているデータはクロス集計表であることが多いのも事実です。

このようなクロス集計表をリスト・テーブル形式の表に変換する機能を Power Query / 取得と変換は持っています。それが [変換] タブの [任意の列] グループにある [列のピボットの解除] です。


必要な列を残すときの考え方に似ていて、「解除したい列」を選択して行うか、「解除したくない列」を選択して「他の列を解除する」かを選ぶことができます。

ピボットを解除した表は以下になります。


(3) データ型とデータの変換


第3列の [属性] は見出しの名前を [月] に変更し、第4列の [値] は [訪日外客数] に変更します。

この列の操作ではあと2つの変換を行います。まず、簡単なものは、 数値のデータである [訪日外客数] の列を、「数値である」設定をします。

列見出しのアイコンは、その列のデータの種類を表します。[訪日外客数] は実は数値なのか、テキストなのかわからない状態の [?] アイコンです。このアイコンをクリックすると変換データのタイプが表示されるので [整数] を選びます。


アイコンが [123] に変わり、3桁カンマがなくなります。これで計算が可能な「数値」になりました。
この型の変換は、[変換] タブの [任意の列] グループの [データ型の検出] でも可能です。この場合は自動的に型を検出し設定してくれます。

次に、[月] 列のデータが 4月、5月・・・なのであれば、[年]列のデータも 2003年、2004年とするか、[年]、[月] 両方とも数字にしたいところです。 最終的には、Excel において日付のデータは「シリアル値」として持ちたいところです。テキストか、数字かの、どちらかに揃えておいて、最後にはシリアル値にしておきます。

今回は [年] のデータに文字列の「年」を加えて、2003 を 2003年 にしてみます。

[年] の列を選択し、[変換] タブの [テキストの列] グループの [書式] の [サフィックスの追加] を選択します。
ダイアログが表示されるので、追加する「年」を入力します。


[OK] を押すと、[年] の列のデータすべてに「年」の文字列が追加されます。かつ、この列のデータはアイコンが [ABC] と示すようにテキストとして認識されます。


(4) 新しい列を追加してシリアル値のデータを作成する


最後に、[年] と [月] の列のデータから、[年月] という、最終的に「シリアル値」になるデータ列を追加します。

[列の追加] タブの [カスタム列の追加] コマンドをクリックすると「カスタム列の追加」ダイアログが表示されます。

新しい列名には「年月」を入れ、カスタム列の式: には以下を入力します。

=Date.FromText([年]&[月])


[OK]を押すと、以下のように新しい列が追加され、年月のデータが作成されます。


年月日として「1日」が既定値になりました。データ型は日付です。

さきの「カスタム列の追加」で使った Date.FromText という関数は、Power Query Formula Language (PQFL) と呼ばれるものですが、別名称があり、「M言語」とも呼ばれます。

https://msdn.microsoft.com/en-us/library/mt211003.aspx

Date.FromText は日付を表す文字列から日付型のデータを作成するM言語の関数です。

Power Query / 取得と変換においてはこの「M言語」の知識が必要になる場面が出てきます。ただし、Excel のワークシート関数のような頻度で使うものではないと思います。(もしそうだとしたら、かなり苦行になります(笑) 理由は後ほど。)

これまでのクエリ エディターの拡張や、[詳細エディター]での入力支援の「無さ」から考えると、M言語を直接記述して何かやる、というより、リボンのコマンドを選択して、その順番を記録して、「適用したステップ」と fx の数式バーで修正する方向に行くような感じがします。

ここまでの作業で、データを取得して、必要な変換はすべて終わっています。
次回は、最後のほうで触れた M言語について紹介したいと思います。その後で、このデータを使って、訪日外客数のデータを利用した分析やレポートを作っていきたいですね。

では、次回をお楽しみに。

[PR] M言語(Power Query Formula Language)について書いている書籍はコレ!
 
Powered by Blogger.

自己紹介


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