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)について書いている書籍はコレ!
 

2016/12/26

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

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

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

各年の複数ワークシートで構成されている訪日外客数のブックを取り込んだところまできました。
今の状態は、クロス集計表で表現されている「セルの範囲」が複数年、縦に連なっている状況です。第4回で説明したように、個々の列の情報は同じものという大前提があります。


ここから「不要な情報を取り除く」作業をする、または「必要な情報のみを残す」作業をします。
そのために最初はちょっと違和感があるかもしれませんが、この複数年のクロス集計が縦に連なったセル範囲を「テーブル」のように扱います。テーブルのように扱うことで、見出し行を追加し、「オートフィルター」機能が使えるようになります。そのフィルター機能を使って「不要な行」を取り除く、もしくは「必要な行だけを残す」操作をします。

(1) タイトル行のベースになる行を探して、タイトル行にする 


これらの操作は、現実のデータを見て検討するので、必ず以下で設定する内容であったり、順番であったりが正しいわけではないことに注意してください。扱うデータは「ナマモノ」であり、調理方法(加工方法)はさまざまです。その時その素材にあった方法を見つけるしかありません。

重要なのは、その操作が「再利用可能か?」を常に意識したほうが良い、ということです。この「再利用可能か?」は所々で解説したいと思います。

今回扱っている訪日外客のデータで、テーブルに変換したときにタイトル行になって欲しいのは、4月、5月、6月といった月であり、その行にあるべき(表示はされていませんが)国名などになります。

一切並べ替えをしないで取り込んだ直後のデータは以下のようなものです。


この時、最初の行は 2003 年の昔のデータなので、基本このワークシートは「変更されない」と「仮定」します。そうすると、「タイトル行」にしたいデータ行は4行目にあります。

このように「最初の3行はとりあえず使わない、要らない」というのがわかっているのであれば、「上位の行の削除」を使って、上から何行を削除する、という操作が可能です。


クエリ エディターの[ホーム]タブは、データを取り込んだ直後によく使う基礎的な機能/コマンドをまとめています。[行の管理]や[列の削減]のグループにはデータ取り込み直後に良く使う機能があり、その中に [行の削除] の [上位の行の削除] があります。

どの列が選択状態になっていても、ここでの指定は「行全部」となります。上位の行の3行を削除、という指定は、すべての列を含む3行を削除します。
また、今回の訪日外客のデータ展開で、第1列目の[Name]にはテーブル名に相当する 2003 などの年が「必ず」入っているので、列全てが null になる「空白行」は存在しません。そのため、(くどいですが)「今回の訪日外客のデータ」では全ての列が空白になる「空白行の削除」は該当しません。

また、この時点で当然疑問に思いますが、2003年のデータの[5月] や [6月]の行をこれから「先頭行」として使う設定を行いますが、2004年以降のデータの[5月]や[6月]は、データとして残っています。これはどうなるんだ?と思われるでしょう。これは、後ほどの作業で結果的に「消す」作業をします。

上位3行を消すと、 [5月] [伸び率] [4月]・・・のデータを含む行が1行目になるので、この行を [見出し行] として使います。そのまま [ホーム] タブの [変換] グループにある [先頭の行を見出しとして使用] をクリックして、1行目を見出し行(タイトル行)に変換します。


この変換により、重複した列名は許されない、もしくは必ず列名がなければいけませんから、[伸率]、[伸率_1] などの名前の自動変更が行われます。


ここからは、そのデータにあった対応を行っていきます。JNTO の訪日外客のデータの場合は、以下の方法で不要なデータを取り除くことができました。これらの方法や考え方は、他のデータでも十分に適用可能だと思います。

(2) フィルターをつかって不要な行を取り除く


今、この状態は、複数のワークシートを追加(Append)し、2003年のデータの上から3行分を削除し、4行目を見出し行にしたに過ぎません。不要なデータはまだ山のようにあります。以下はスクロールしていくと見つけることができる不要なデータの例です。(行が多いときはスクロールせずに、フィルターオプションから、重複しないデータの確認ができます)

「総数」や「アジア計」、「ヨーロッパ計」といった、小計、合計の行は必要ありません。

小計行もいりませんが、コメント・注釈行もいりません。

2003年の上位3行で消した表のタイトルは当然次の年のデータでは出てきます。これらの行は必要ありません。

もし、必要となるデータの国が決まっているのであれば、[Column2] のオートフィルダーで国の名前を直接指定するのも一つの方法です。以下であれば、Column2 に「中国」、「台湾」、「米国」、「韓国」、「香港」がある「行だけ」を選択して絞り込むことができます。


ただ、実際には国のデータは昔のデータにはなかった国のデータが追加されることも多く、必要な国を指定して、国を固定するより、不要なデータを削除するほうが「再利用性」が高いと言えます。今後どんな国が増えるかわからない、という状況は、「今後どんな製品が増えるかわからない」や、「今後どんな営業所が・・・」「今後どんなお客様が・・・」に通じるものがあると思います。

この場合に使いたいフィルターオプションは「指定の値を含まない」フィルターオプションです。


このテキストフィルター オプションを使えば、新しい国が入ってきても対応可能です。もちろん、「新しい不要な行」が入ってきたら、その対応をするのには変わりません。新しい不要な行と、新しい国のどちらが追加される可能性が高いかを実データから想定する必要はあります。

・漢字の「計」がある国名はない、という前提で「計」の文字を含まないものにする
>アジア計、ヨーロッパ計、アフリカ計、北アメリカ計など、地域の小計が排除される

・漢字の「注」がある国名はない、という前提で「注」の文字を含まないものにする
>注1:、注2の行が排除される。もちろん漢字1文字の「注」に問題がありそうならば、その行に含まれる「本資料」や「表中の」などを指定すると確実になります

・「総数」」を含む行を外す
>「2004年 訪日外客数(総数)」といったデータのタイトルを表す行と「総数」の計算行が排除される

・「出典:」を含む行を外す
>表の下にある「出典:日本政府観光局(JNTO)」を含む行が排除される

・「本表で」を含む行を外す
>「*本表で、通年の・・・」のコメント行が排除される

このような設定を繰り返して、すべての不要な行を削除します。確認はオートフィルターのリストを見るとが一番いいでしょう。


Column2 は「国」または「地域」のデータ列になったので、見出し行の [Column2] をダブルクリックすると編集モードになるので [国名] などに変更します。また、第1列目の [2003] は年々のデータかを表すので [年] に変更しておきます。


これで不要な行の削除が終わりました。
長くなりましたので、次回は、列について操作します。
お楽しみに。

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

2016/12/20

[Power BI Desktop] エクセル複数ブックの取り込みが簡単になりました

データベースサーバーや、クラウドサービスをデータソースにするだけではなく、Power BI Desktop や Excel の取得と変換や Power Query は「Excel のブック」や「Excelのワークシート」をデータソースとして活用するシーンも十分に考えられます。

すでに、あるフォルダーにある複数の Excel ブックを取り込む方法を紹介してきましたが、もっと簡単になりました、というブログ記事が 2016年12月19日の Microsoft Power BI Blog にて公開されました。

Microsoft Power BI Blog - December 19, 2016 by The Power BI Team
Combining Excel Files hosted on a SharePoint folder with Power BI



原文は「SharePoint フォルダーの複数のExcelファイル(ブック)を Power BI で結合する」という内容です。SharePoint フォルダーを例にしていますが、SharePoint でなく、ローカルのフォルダなどでも同様の操作になります。

これまで、Excel.Workbook 関数を手で入力する方法を紹介してきたわけですが、それをせずとも、複数ブックを結合することができるようになりました。まだ、Power BI Desktop (2016/11版) でしか確認できていませんが、そのうち Excel の取得と変換や Power Query でも利用可能になるでしょう。

以下は、SharePoint フォルダーではなく、ローカルのフォルダーで試してみての結果です。

・ フォルダーを指定して [編集] ボタンでクエリ エディターを起動


クエリ エディターでは、フィルダーにあるブックの情報(属性)が表形式で表示されています。ここから、名前や拡張子などを使って「オートフィルター」のフィルターオプションと同様の操作で絞り込みました。

・ [バイナリの結合] ボタンで Binary からオブジェクトを取り出す

これまでは、[Content] 列にある [Binary] の中にあるオブジェクト(シートやテーブル)を取り出すために、カスタム列の追加と Excel.Workbook を使って、ブックの中にあるシートやテーブルを抜き出していましたが、今回の拡張から、[Content] 列のバイナリの結合ボタンを押すと、新しいダイアログウィンドウが表示されるようになりました。

[Content] 列にあるバイナリの結合ボタン
バイナリの結合ウィンドウ
この後の操作はこれまでのものと似ているのですが、ここで例えば「4月」のシートのアイコンを選ぶと、4月シートのみが展開されますが、「サンプル バイナリ パラメーター」のフォルダアイコンを選択して、[OK] を押すと、各ブックの Binary にあるシートやテーブルのオブジェクトが抜き出されます。

Data列が追加され、Binaryの中のTableが抜き出された状態
Data列の展開ボタンをクリックすることで、4月、5月、6月のワークシートのデータを結合し、展開された状態になります。

・ テーブルだと本当に楽です

ただし、テーブルでなく「範囲」だと、不要な行を削除したりする必要があります。もちろん、もとのブックに含まれるデータが「テーブル」であれば、不要な行に対する処理は必要ありません。

現時点では Excel 2016 の Office Insider スロー更新チャンネルの「取得と変換」では、上記の機能は確認できていませんが、近いうちに Excel でも利用可能になるでしょう。

2016/12/14

Excel BI - Power BI コミュニティ 登壇の報告

日本マイクロソフト株式会社さんで先月行われた Power BI コミュニティ 勉強会の模様が公開されていました。

Microsoft Japan Data Platform Tech Sales Team Blog
2016/11/26 Power BI 勉強会 – 第二回 が開催されました
https://blogs.msdn.microsoft.com/dataplatjp/2016/11/28/20161126-power-bi-community-2nd/

当日の様子(登壇しているのは私です)
Power BIまわりの情報提供はマイクロソフトさんもがんばってやっていらっしゃいますが、最近は Excel の視点からの情報が少なくなりつつあります。

その代り、Power Query が Excel の標準機能になるなど、Power BI と Excel の関係は変化・進化しているのが現状で、私もひとりで追いかけるのが非常に大変なのが実情です。

Power BI コミュニティ勉強会主催の かがたさんや、日本マイクロソフトの土井さん、コミュニティ勉強会に参加してくれた方と情報交換することが、情報の理解、キャッチアップに非常に役立っているのは疑う余地がないのが現状です。

上記の土井さんのブログには登壇者のパワーポイントも公開しているので、時間があれば Excel ユーザーでもぜひチェックしてほしい内容です。
Powered by Blogger.

自己紹介


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