[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 上では、データの列でカンマを付けるか、レポートでフィールドやメジャーを選び、モデリング タブの書式設定のカンマを使う、という内容でした。

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

結論から言えば、暗黙のメジャーの１つである「カウント」の計算結果に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

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

Read More

[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 によるメジャー作成に慣れているかにもよるでしょうね。

Read More

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 取得と変換で共通の見た目、操作性を持つウィンドウとして起動します。

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

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

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

M言語の特徴

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

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

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

数式バーはステップ１つの数式のみ確認できますが、[詳細エディター] を使うことで、記録されているすべてのステップの数式の確認が可能です。行の絞り込みや、列の削除や追加の操作は、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 の一連の流れの中で、機能的にできることを把握し、適切な場面で、適切な機能を使えるようになりたいですね。

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

Read More