[Power BI Desktop] 3桁カンマをレポートのビジュアルでつけたい

セルの書式設定がある Excel からすると「なにそれ？」なタイトルですが、Power BI サービスで分析結果のデータを共有するために、レポート、ダッシュボードの準備で Power BI Desktop を使っていると、早い段階で「？？？」と、はまるネタです。数値データに3桁カンマをつけたい、です。

ちなみに、Excel で数値データに3桁カンマを付けるのは、当然リボンのコマンドでもできますが、ショートカットキーは、Ctrl+Shift+1 (つまり、Ctrl+!)です。
さらに、ちなみに（笑)、この Ctrl+Shift+1はトグルではないので、3桁カンマの解除はこのショートカットを再度押してもできません。標準の書式を設定するショートカットがあり、それが Ctrl+Shift+チルダ(~)です。チルダは、ひらがなの「へ」ですよ。標準に戻すこのショートカットは「知っていれば」意外に使う場面が多いと思います。

ExcelユーザーにとってのPower BI Desktopは「ピボットテーブル レポート & グラフ」

私はここに行き着くまで結構時間がかかりました（笑）。
というか、Power BI Desktop のレポートに、もっとも近いのは Excel の「Power View」なんですが、あまりに知名度低いし、今となっては「黒歴史」的に、無かったことにしたいような扱いの機能なので、あえて、ピボットテーブル レポートとピボット グラフ、としました・・・。技術的に Silverlight と HTML5 に翻弄されちゃいました、という感じでしょうか。

[Microsoft] Excel 2013 で Power View シートを作成する

Power View を無かったことにしたいような扱いの話はこちらで紹介してます。

[Road2CloudOffice] Power BI と Excel

さて、Power BI Desktop はもちろん、Excel のピボットテーブル レポートもそうなんですが、最終的なレポートの形が、おぼろげながらも「ぼんやり」とないと、何のデータをどこから手を付けていいかわからなくなります。

ただ、そんな中でも、Power BI Desktop で、取り込んだテーブルの列の「合計」や「個数」はビジュアル（視覚化）の「カード」を使えば、すぐに「レポート」で表示することができます。このあたりの操作性や動きはピボットテーブルに近く、数値のみの列であれば合計が表示され、数値以外の数値があれば個数が表示されます。

ピボットテーブルの「フィールドリスト」のようなウィンドウから数値の列(フィールド）を選べば、自動的に「合計」が表示されます。縦棒グラフのビジュアルが使われるので、カードのビジュアルを選ぶと、もし桁が多いと以下のように表示されます。

これは、ビジュアル(視覚化）の書式-データラベル-表示単位が「自動」になっているためで、これを「なし」に変更します。感覚的にはここに「桁区切りを使用する」がありそうなのですが、ありません。

そして、この操作手順の状態で、リボンの「モデリング」の書式設定に「桁区切り記号」があるのですが、グレーアウトして選択することができません。

このカード ビジュアルの数値に3桁カンマをつけたいのです。が、レポートでカードのビジュアルを選んだ状態だけでは、桁区切り記号のコマンドはグレーアウトしている・・・

最初のころ、これで悩みました。

そのころの対応は、いったんデータに戻って、その列（フィールド）に対して「桁区切り記号」をつけていました。データ側の列に桁区切り記号をつけると、レポートのビジュアルの数値も桁区切り記号がつきます。

ただ、この方法、独自にメジャーを作ったときに、対応する列はなく、関連する列をすべてに桁区切り記号をつけても、3桁カンマがつかないのです。

書式設定はビジュアルではなく、フィールドを選択して行う

つまり、データに戻ってフィールドに書式設定はあまり良いやり方ではななかったわけです。
レポートのビジュアルに表示されている数値に3桁カンマをつける＝レポートで「モデリング」タブの「書式設定」のグレーアウトを外すのは、表示形式を変えたいビジュアルを選択するのではなく、右端にあるフィールドリストのフィールドを選択すると、書式設定可能になるのです。

対象となるビジュアルは選択する必要はなく、作成したメジャーはデータにはありませんが、フィールドリストに表示されるので、そのメジャーを選択すると、リボンで書式設定が可能になります。

わかってしまえば、なんともない操作なのですが、結構長い間適切な操作をしていなかったので、ご紹介しました。

[追記]
[Power BI Desktop] 数値の 3桁カンマ、再び。
https://road2cloudoffice.blogspot.jp/2017/02/power-bi-desktop-3.html

Read More

[Power Query / 取得と変換] ブックにある複数のワークシートをまとめる 2

前の投稿で、複数のワークシートをまとめる、というお題を取り上げましたが、これ、「今、Excel で開いているブックにある、複数のワークシートにあるテーブルをまとめる」という処理でした。

Excel でデータを扱うのなら、Excel でブックを開くのが鉄則

ただ、この手の処理は、同じブックで処理するよりも、元データと、加工・処理するブックは「違う」パターンが多いです。元データのブックは、別の部門だったり担当者が準備したり、更新したりしていて、そこから加工するのは自分、みたいな感じです。

Excel の処理として、扱うことのできるブックは「Excelで開いている」ことが鉄則です。

そのため、基本的には Excel でデータを扱いたいのであれば、まずは Excel でブックを開く、という処理が「必須」でした。 Excel で一度開けば、Excel という Application を頂点とした、「ブック - シート - セル」という階層構造で処理することができるからです。

VBAでも同じです。ただし、裏技的にブックを開かずにデータを取り出すことが技術的には可能でしたが、広くお勧めできるものではありませんでした。

[VBA] ブックを開かないで読む (Office TANAKA)
http://officetanaka.net/excel/vba/tips/tips28.htm

Power Query でブックを開かずにデータを参照する

という前置きは置いておいて、Excel でブックを開かずに、別ブックから、そこに含まれている各ワークシートのテーブルを一気に取り込んでしまう処理は、アニメーションGIFで一気に紹介できるくらい「簡単」なんです。
 前回は、既に開いているブックのシートにあるテーブルから、それぞれクエリを作成しましたが、今回は、ブックをExcelで開くことなく、指定するだけで、そこにあるテーブルを「テーブル」という条件で絞り込み、リスト化して、一気に展開する、という流れです。

(1) ブックを選んで、テーブル選ばす

[新しいクエリ]  - [ファイルから] - [ブックから] で、Excel ブックを指定できます。このとき、ブックに複数のワークシートがあれば、ナビゲーターウィンドウにそれぞれが表示されます。テーブルも同様です。

通常はここでワークシートやテーブルを選ぶ、または、[複数のアイテムの選択] オプションをチェックして、複数のワークシートやテーブルを選びます。

複数のワークシートやテーブルを選んだ場合は、その数ぶんの「クエリ」を自動的に作成します。それぞれのクエリが作成された後の、たとえば追加（ユニオン）の処理は、前の回で紹介した手順と同じです。

このテーブルやワークシートのオブジェクトを「選択する」条件を含めてクエリ化したいのです。
もう一度、ナビゲーターウィンドウをみると「フォルダー」のようなアイコンがあります。実は、これは Excel のブックそのものを表しているんです。

このフォルダの形をした「ブックアイコン」を選択して、[編集] ボタンを押すと、ブック内のオブジェクトとその属性がテーブル形式で編集可能になります。

ここで、オートフィルターの要領で、必要なオブジェクトの絞り込みが可能になります。

アニメーションGIFでは種類(Kind)でTableを指定して、テーブルオブジェクトだけに絞り込んでいます。

もちろん、名前で「～から始まる」や「～を含む｝で絞り込みをかけることも可能です。

そして、このブックだけしか選ばない方法の最大のポイントは、作成するクエリはたった1つしかない、ということです。よって、テーブルが何個あろうが、クエリは１つであり、以下で紹介するように、複数のテーブルを追加するのは、たった1回ボタンを押すだけでいいのです。

(2) 追加(Append = Union)は「同じ形式」であることだけ気を付け、展開する

絞り込んだ後、テーブルの中のデータを取りだす（展開する）のは、ボタン１つを押すだけでOKです。

ただし、展開する前に不要な列を削除します。不要な列を削除、というよりは、「必要な列だけを残す」という作業が本質になります。

アニメーションGIFでは、展開する [Data] 列と、元のデータがどこかわかる [Name] 列を残して、他の列を削除しました。

[Data] 列の先頭行にある展開・集計するボタンで、展開します。

 

(3) 別のシートが増えても対応可能なクエリ

このクエリはクエリ内の適用済みステップで、ワークシートやテーブルを明示的に指定していません。 「指定したブックにある、テーブルオブジェクトをすべて集めて展開する」というクエリになっているからです。

元データのブックが更新されたら、加工用に用意したクエリを更新することで、最新のデータを取り込みます。もちろん、名前の知らない新しいワークシートの新しいテーブルが追加されても、クエリの編集なしにデータを取り込むことができます。

(4) 列順序は関係なく、列名が重要

テーブルオブジェクト(ListObject)は「構造化参照」されますが、このクエリにおいても構造化参照されています。列名を使って、それぞれのテーブルの列の追加を行っているのです。
そのため、列の順序、順番は関係なく、同じ「列名」を持つオブジェクトが「追加」されます。

[日付] [番号] [数値] という列順序のテーブルと、[数値] [日付] [番号] というテーブルは、問題なく追加可能だ、ということになります。

業務のほとんどは、まとめるべきワークシートの名前が決まっていることが多いですが、日付や期間、時期のようなワークシート名だと、ブックを開くたびにワークシートの数と、新たなワークシートが追加されている、というケースも少なくありません。
VBAを使えば解決していましたが、逆にいえば VBA を使わなくても、Power Query / 取得と変換の機能でここまでできることは、知っていて損はないと思います。

Read More

[Power Query / 取得と変換] ブックにある複数のワークシートをまとめる

私もよく参考にする日本マイクロソフトの「Data Platform Tech Sales Team Blog」ですが、最近の投稿で「Excelマクロから卒業?」というタイトルのブログがありました。

私も以前に紹介したことのある、上記のブログのポイントである「ピボットの解除」などは、たしかにこれまで VBA で行うことになる難しいお題でした。これがクリックだけでできるようになるのは、「これからは Power Queryか？」とか思いますよね。

特にブックやワークシートを扱う系の処理では、これまで VBA の独壇場だったエリアに Power Query の適用が可能になっているので、ますます VBA卒業？とか言われそうです（笑）。（まぁ、卒業の前に入学してから・・・みたいな話もあるのだけど）

今回はブックにある複数のワークシートを扱う（まとめる）方法をご紹介したいと思います。
2つのケースを説明しますが、いずれもデータを含む表は「テーブル」になっているものとします。もちろん、Power Query ではテーブルになっていない「セル範囲」の表も扱うことができますが、その場合必要な列や行を指定して、データだけにするステップが追加する必要があります。

テーブルにしていれば、データだけを絞り込むステップは必要ありません。

ワークシートの数も名前もわかっている場合

考え方として、各ワークシートにあるテーブルから必要なデータを取り出すクエリを作成し、それらを「追加（Append)」します。データベースの世界や Access の世界では、この「追加」を「ユニオン(Union)」と呼びます。追加やユニオンは、基本的には同じ構造（列名を含む）のテーブルをつなげるイメージです。

4つの支店や部門みたいなグループがあって、そのデータをワークシート毎に分けている、といったサンプルです。札幌、東京、大阪、福岡などのワークシートがある、という感じですね。

それぞれのワークシートに「日付」、「番号」、「数値」のデータがあって、札幌、東京、大阪、福岡からデータを抜き出す、みたいな感じですよね。（最初から、地域みたいな札幌～福岡を入れる列をいれて１つのテーブルに・・・というのはわかります。が、ここではシンプルなサンプルを使いたいので、あしからず。）

名前がわかっている（＝固定されている）ので、それぞれのワークシートから必要なデータ（もしくは全件）を条件を付けないで、全件取り込むクエリを作ります。これらから、4つのテーブルを追加（ユニオン）するクエリを作ってみます。実務では必要な「列」だけにしたり、条件を付けて行の絞り込みをすることになるでしょうね。

また、この例では、絞り込み条件なしで、各ワークシートにあるテーブルを全件読み取るクエリを「接続のみ」で作成します。元データと同じテーブルのワークシートを追加しても仕方ないですからね。さらに、どのシートから読み込んだかをわかるように新たに列を追加して、ワークシート名を追加します。

今は複数のクエリの追加(Append)は簡単になりました。以前は、2つしか追加できなかったので、2つを追加してから、その追加クエリにクエリを追加して・・・を繰り返したのですが、今は、3つ以上のクエリを追加するを選ぶと、一気に複数のクエリをAppendすることができます。

Append1というクエリが、4つのテーブルのクエリを追加＝Unionしたクエリです。
ワークシートにクエリ結果を全く展開していません。このクエリの使い方は、ピボットテーブルのデータソースにして、欲しい形でピボットテーブル レポートを作成することになります。

ここで、Excel のピボットテーブルの機能としての知識が必要になります。

ピボットテーブルの作成は「挿入」タブの「テーブル」グループにある「ピボットテーブル」から行いますが、作成のダイアログで [外部データ ソースを使用] を選び、[接続の選択] で「Append1」の接続（クエリ）を選びます。この指定で、作成するピボットテーブルの元データに作成したクエリを指定することが可能になります。

はい、これ、複数のテーブルからピボットテーブル レポートを作ることになりますが、データ モデルを使っていません。よって、データ モデルを使ったピボットテーブルの「制限」がありません。
使うかどうかは別として、「集計フィールド」と「集計アイテム」が使えます。

でも、正直なところ、今から Power Query + ピボットテーブルで集計フィールドと集計アイテムを覚える必要はないと思います。集計フィールドは Power Queryの追加の列そのものですし、Power Query とデータ モデルもしくは Power Pivot でメジャーを使うのが主流になりそうです。

このサンプルは、1回きりだと意味がありません。それぞれのテーブル データが更新される、追加されるなど、元データに変更がある場合、一度このクエリとピボットテーブルを作れば、クエリ＋ピボットテーブル更新で、最新データにすることができます。

そう考えると、このケースは「別ブック」である場合のほうが、より実務的ですよね。

次は、別ブックの場合の手順を紹介したいと思います。このとき、ワークシートの数は名前を知らなくても、取り込むことができます。となると、なんかVBAっぽいでしょ（笑).

Read More