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

Power BI 関連の話をしていると、たまに「良いサンプルデータがない」ということを聞きます。しかし、世の中には、サンプルとして使える「データ」が「実は」公開されています。
データ分析の素材として、すぐに使える形式のものが少ないのが玉にキズですが、それを補う ETL 機能を持っているのが、Power BI Desktop や Excel の取得と変換です。（ただ、この点は他の会社の BI ツールも同様です）

日本マイクロソフトの Data Platform Tech Sales Blog さんの「Power BI Desktop を使って訪日外国人 (インバウンド) 統計データを可視化する」で紹介されていた、日本政府観光局（JNTO)さんが公開されているデータが、とても興味深いのと、Power BI Desktop で十分に利用できるサンプルなので、それを使って何ができるか、をご紹介したいと思います。

http://www.jnto.go.jp/jpn/index.html

JNTOさんは統計情報として「訪日外客統計」を Web サイトで公開しています。

訪日外客統計の集計・発表

このリンク先のページには、PDFのレポートで、毎月、どの国から、何人訪日しているか、公開されています。
PDFだと、今の Power Query / Power BI Desktop / Excel でも取り込みが難しいのですが、レポートではなく「統計データ」が、Excel ブックとして公開されています。

http://www.jnto.go.jp/jpn/statistics/visitor_trends/index.html

このページで公開されている「国籍/月別 訪日外客数 (2003年～2016年)(Excel)」がとても興味深いデータの集まりで、Power BI などで利用しやすい形式になっています。

http://www.jnto.go.jp/jpn/statistics/since2003_tourists.xls

なんといっても（xlsxではないのは置いといて）、Excel ブックなので、ローカル PC のディスクにダウンロードしないで、直接 Power BI Desktop や Excel から参照できます。

具体的な手順や設定はもちろん大切ですが、まずは、このデータを使ってどんなことを探ってみたくなるのか、考えてみました。

そんなに難しく考えないでいいんです。難しく考えないで、「このデータは出せる？」という思いつきを「簡単にできるかどうか」検討することが、トレーニングとして重要であり、そのデータから「あれは？」「これは？」を素早く、数多く取り出すことができれば、データへの考察が深まるでしょう。（取り出すことに時間がかかるのであれば、それはやるべきではないのかもしれません。しかし、重要なのは、それはそのコストが高いのではなく、その時点で取り出すためのスキルが無い、ということど同意だと思います。もちろん、そのスキルを得るために費やした時間とコストは存在しますが。）

まず思いつくのは公表されているデータの中で「最新」はいつで、その最新のデータから訪日外客数が多い国のトップ5とその数値が欲しい、みたいな感じではないでしょうか。

URLの記述が、/since2003_tourists.xls なので、2003年以降のデータがどんどん追加されていっているようです。実際にブックをダウンロードしたのが以下です。

2003年から「シート」が追加されていくタイプです。
印刷レポートも兼ねることから、この手のデータはクロス集計（ピボット集計）の形式が主流なのは仕方ありません。今は、Power BI Destop / Excel 取得と変換の「ピボットの解除」があるので、涙目になることはありませんね！

このブックから「最新のデータ」を抜き出す方法を考えます。

(1) VBAは使わない(シートの名前から「年」を判断しない）
(2) すべての国のデータが入っているわけではないが、データが入っている月を最新とする
(3) Power BI Service のダッシュボードやレポートでメンバーと共有し、モバイルで確認できるようにする

つまり、Power BI Desktop や Excel の Power BI アドインとVBAを除いた機能や関数で、最新データの年・月を出してみよう、ということです。もちろん、データソースを目視すると最新データの年月はわかりますが、データソースの更新をするだけで、抜き出す「最新の年月」も「更新される」イメージです。

この「最新データの最新とは何日か？」というのは、レポートを作成する上で必要になるのですが、意外に簡単ではありません。



このような月次データだと、あまり「更新された最新日」に重要性を感じられないかもしれませんが、いずれ題材となるであろう「気象庁 地震データ」などは、いつのデータなのかが非常に重要な要素となります。 このあたりはバランス感覚が必要ですが、データの分析においては「いつなのか」という日付・時刻が大切になることが多々あるので、ぜひ意識してください。

ということで、JNTOの訪日外客数ブックから、Power BI Desktop や Excel へ分析しやすい形でデータを取り込む必要があります。

ポイントは以下です。

• ブックにあるワークシート名を指定しないで、必要なワークシートをすべて取り込む
  (ワークシートが追加された場合、削除された場合の対応）
• クロス集計表のピボット列をピボット解除し、テーブル形式に変換する
• 必要な行だけを取り込む
• 必要な列だけを取り込む
• 必要な列を追加し、値を作成する

ブックにある、2003年から2016年までの14枚のワークシート、来年には2017年を含めたワークシートを取り込むようにする設定、よく見るクロス集計表をテーブル形式に変換する方法などは、今後、官公庁によって作成されるデータを利用するには必須の方法になるでしょう。

次回は、具体的なブックの取り込み方、テーブル形式への変換を手順を追って説明することになるでしょう。お楽しみに。

次回はこちらです。

Read More

[ピボットテーブル] 日付 時刻 の自動グループ化を無効にする

過去に何度か取り上げているこの話題ですが、いつの間にか Excel 2016 のオプションで、この日付/時刻列の自動グループ化のオフ/オンの設定が可能になっていました。

以前は、グループ化された直後に Ctrl+Z で解除をしていました。

https://road2cloudoffice.blogspot.jp/2016/04/excel-2016.html

または、レジストリでオフにする方法が紹介されていました。

ピボットテーブルで時間グループ化をオフにする

Excel 以外のデータソースに接続して、データモデル経由でピボットテーブルを利用する場合は必要な機能ですが、ワークシートだけの利用だと、ちょっと使いづらく、やはりコンテキストメニューからのグループ化のほうが使いやすいんですよね。

Read More

[Power Query / 取得と変換] FIND と Text.PositionOf、そして Text.Middle

セルA1 に "AB-345" の文字列がある場合、"-"(ハイフン）の位置を調べるワークシート関数は、

=FIND("-", A1)

で、3 を返してくれます。ちなみに、もし、ハイフンが「無かったら」#VALUE エラーになるので、IFERROR を組み合わせますよね。もしなかったら、-1 を返す、という数式が以下です。

=IFERROR(FIND("-",A1),-1)

同じことを Power Query Formula Language (M言語）でやろうとすると、以下になります。

Text.PositionOf([文字列],"-")

この関数は 2 を返します。「０から始まる！」のパターンです。もし、ハイフンがなかったら、この関数は -1 を返してくれます。

ちなみに、ワークシート関数の FIND は大文字・小文字は区別されますが、SEARCH は区別されません。 PositionOf は FIND 同様に区別します。

おおよそ、文字の位置がわかったら、そこから前の部分を抜き出すとか、そこから後ろを抜き出す、といった使い方をします。

AB-345 からハイフンの前にある"AB"を抜き出すには LEFT 関数を、後ろにある 345 を抜き出すなら MID 関数を使うことになります。

エラー処理をしないワークシート関数を使った単純な数式が以下です。A1セルに"AB-345"が入力され、A1を参照し、ハイフンの前と後ろを抜き出す例です。

=LEFT(A1,FIND("-",A1)-1)

=MID(A1,FIND("-",A1)+1,LEN(A1))


同じことを Power Query のクエリ エディターでやると以下のような数式になります。

Text.Start([文字列],Text.PositionOf([文字列],"-"))

Text.Middle([文字列],Text.PositionOf([文字列],"-")+1)

ワークシート関数の MID と違って、ハイフンの後ろの「長さ」を指定する必要がないのはラクですね。

Read More

Power BI 勉強会 @ 品川マイクロソフト 登壇します

登壇します！と、思ったら、すでに満席でした・・・

http://connpass.com/event/38446/

Excelのユーザーのための、とうたっていますが、「取得と変換」の話になります。
逆説的なお題ですけど、以下のように思うんですよね。

• CSVファイルの取り込みで「取得と変換」使わない理由がわからない 
• 複数のワークシートからデータまとめる仕事なのに「取得と変換」を使わない理由がわからない
• 複数のブックからデータをまとめるのに「取得と変換」を使わない理由がわからない
• その、ピボットテーブルの表を活用するのに、「取得と変換」を使わない理由がわからない
• VLOOKUPを使った数式を1万行とかのセルにコピーして「遅い」とかいうのはきつい・・・

「取得と変換」を Power Query に置き換えてもらっても同じですけどね。

データ分析結果を共有するなら、Power BI Desktop & Power BI Service です。ここで Excel にこだわる必然はないですよ。Power BI Publisher for Excel はありますが、そのダッシュボード作ることができるスキルがあれば、Power BI Desktop でレポート作っちゃった方が幸せになれると思います。

Excel 中心の作業や業務であれば、Power Query = 取得と変換 は今後必須になるでしょう、というか、いまどき CSV ファイルのインポートでテキスト ファイル ウィザードを使い続けるのはいかがなものか・・・です。取り込む時点で、データの絞り込みやら、細かくできないんですから。
そこから VBA もありですが、その前に取得と変換やってみましょうよ、と思いますね。

リレーションシップも取得と変換によるクエリがベースになります。Power Pivot は使わなくても、ほぼ同様のことを取得と変換でできます。今となっては Power Pivot の必要を感じません。
DAXをやってメジャー作るなら必要か、と思っていたら、Excel の実装はすごくて、データモデルとして登録すれば、Power Pivot を使わなくても、データモデルのピボットテーブルで、メジャーを DAX で作れるんだから、すごいよね。

みたいな話をしたいのですが、いかんせん50分くらいしかないので、絞り込まなきゃいけません。
少しでも参考になる情報を共有できれば！と思っています。

Read More

[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