当サイト主催のセミナーの受講後にいただいた質問とその回答をブログ記事としてまとめるシリーズの第4弾です。
なお、当サイトのセミナーにご参加いただいた方からは、特に期限などなく、セミナー内容に関するご質問やPowerBIに関する疑問や困りごとに関するご相談などを無料で承っていますので、ご受講いただいた方は遠慮なくご質問ください。
受講者からの質問内容
Excelのシステム管理台帳データをPower BIに取り込みたいが、階層構造の作り方と機能数のカウント方法がわからないです。アドバイスをいただけますでしょうか？
いままでExcelで管理していたデータをPower BIに取り込んでサクッと集計できるようにしたいというのも、よくあるニーズですね。
でも、Excelであれば関数（sumifとか）を使って力技でできてしまう集計も、Power BIで実現しようとすると、どうすればよいかわからず、手が止まってしまうこともあるようですね。
ExcelのデータをPowerBIに取り込む際の問題は、いわゆる「データの横持ち・縦持ち」問題ともいうべき、データの構造の問題で、データの持ち方さえ解決できれば、簡単にPower BIで集計できるようになります。
というわけで、今回はPower BI（に限らず他のBIツールでも）意識しておく重要なポイントが含まれますので、ぜひ参考としててみてください。
大まかな手順としては、
1.Excelのデータの構造を把握する
2.必要に応じてデータの持ち方を変換する（Unpivot）
3.レポートを作成する
といったイメージです。
1.Excelのデータ構造を理解する
今回の質問者様からいただいたデータはこんなイメージのものでした。
この表は、事業部や製作所ごとのシステムと、そのシステムに含まれる機能を管理するもので、人の手で管理する分には特に問題はない表です。多くの場合、普通にExcelで管理台帳を作るとこんなイメージになると思います。
普通に管理する分には問題ないのですが、この表からBIツールに取り込んで【機能数】を集計したいとなると、大きな問題を含んだ表と言わざるを得ません。（あるいはExcelのピボットテーブルの機能を使って集計したい場合も同様にうまくいきません）
このような表では、集計したい【機能】が異なる列に分散しているため、BIツール（やピボットテーブル）では簡単に集計できません。
ちなみに、このようなデータの持ち方を「データの横持ち」といいます。
機能の種類（例：[営業]顧客管理とか[生産]在庫管理など）が増えると列が横に増え続けるタイプの持ち方です。
このようなデータをPower BIに取り込もうとすると
・新たな機能が増えるたびにデータの取り込み処理の変更が必要となる（クエリエディターでの処理の変更が必要）
・列が異なるデータを集計するので、単純な集計はできない（新たな列を追加し、列が増えるたびにメンテナンスが必要）
となってしまいます。
BIツールで簡単に集計するつもりが余計面倒になってしまい、「BIツールって使えない、やっぱりExcelが至高だよね」みたいな誤った認識を生んでしまうこともあったりします。
では、BIツールに取り込んで集計するためには、どのようなデータでなければいけないかというと、こんなイメージです。
先ほどのデータとの違いが判りますでしょうか？
主な違いは、
・「機能」が1つの列にまとまって、列名がデータ（BI的にいうとディメンジョンの値）になっている
・「機能」に含まれていた業務（営業とか経理とテキストで表現されていた部分）が別の列として切り出されている
・「機能」関連の列の値となっていた部分（TRUEとFALSE）が「値」列として切り出されている
の3つです。（列「業務」は業務と機能の階層構造を作るために切り出しています）
このようなデータの持ち方を先ほどの横持ちに対して、「データの縦持ち」といいます。
機能の種類（例：[営業]顧客管理とか[生産]在庫管理など）が増えても、列が横に増えず、縦に（行）データが増えるタイプの持ち方です。
人間が目で見て判断するには、「α事業」や「Aシステム」といった項目の重複が目につき、わかりづらいデータのように見えますが、BIツール（やピボットテーブル）での集計にはこの形がベストなのです。
この縦持ちのデータであれば、ディメンジョン（データの分析軸）となる事業本部や製作所、システムや事業ごとに機能数を集計するのが容易です。
Power BIでデータを扱う場合には、基本的にデータを縦持ちにする。
取り込みたいデータが縦持ちでない場合には、どこかでデータを変換する必要があるということをポイントとして押さえておきましょう。
2.必要に応じてデータの持ち方を変換する（Unpivot）
今回の元データは明らかに横持ちのデータですので、どこかのタイミングでデータを縦持ちにしてあげる必要があります。
ちなみに横持ちのデータを縦持ちに変換することを一般的にデータのアンピボット（Unpivot=ピボット解除）と呼びますので、以下ではアンピボットと表現します。
このデータのアンピボットをどのタイミングで実施するかというと、
・Excelの元データを手動で縦持ちにする（Power BIに取り込む前）
・Power BIに取り込んでから縦持ちにする
のどちらかということになります。
（実は、ExcelでもPowerBIで使うアンピボット機能を使えるのですが今回は省略します）
手動でアンピボットするのも芸がないので、今回はPower BIに取り込んでからアンピボットを行う手順をご紹介しましょう。
とりあえず準備として、Power BI Desktopを立ち上げて、対象となるExcelファイルを指定するところまでは詳しい説明は省略します。
実は今回の元データは、セルの結合があったり、本来は別の値として管理すべき業務と機能が同じセル内にあったり、カウントしたいものが文字列だったりと、よくある扱いづらいExcelデータの特徴を兼ね備えています。（元データが悪いわけではなく、Power BIの取り込みを考えると扱いづらいという意味です）
クエリエディター機能を使うと、セルの結合への対応とか、細かい部分にも柔軟にノンプログラミングで対応できます。
一つ一つのステップを詳細に説明すると冗長なので今回は省きますが、クエリエディターの「適用したステップ」にある、【フィルターされた行～変更された型】までで、データの整形を行っています。基本は余計な行などを削除しているイメージです。
そして、準備が出来たらここからが今回の主題のアンピボットです。
今回は、機能の部分を一つの列にまとめたいので、【列：[営業]販売管理～[経理]その他】までを選択した状態で上部メニューの「列のピボット解除」をクリックします。
するとあら不思議。
あっという間に、列名だったものが「属性」という列と「値」という列にきれいに分かれて集計するためのデータの理想形である縦持ちのデータとなりました。
後は、文字数で「属性」の中身を分割したり、列名をイメージする名称にしてあげれば完成です。
最終的なデータの持ち方をイメージできていれば、操作自体は難しくはないはずです。
ちょっと操作のイメージが湧かないなぁという方も、とりあえずクエリーエディターをいじってみてください。
クエリエディターは、元データを直接編集しているわけではないので思った通りのアンピボットが出来なくても、追加したステップを削除してしまえばすぐに元通りになります。
気軽に何度も設定しなおすことができますので、ご安心を。
3.レポートを作成する
さて、データの準備はできたのであとはレポートを作って集計したデータをビジュアル化するだけです。
例えばこんなイメージです。
積み上げ縦棒グラフのビジュアルを使って、機能階層【部門⇒機能】ごとに機能数を事業本部を凡例として集計するグラフを作成してみました。
セミナーでもお伝えしていますが、データの準備さえできてしまえばあとのビジュアル化の作業は大した手間ではありません。
元データのExcelとPowerBIDesktopのサンプルファイルもダウンロードできますので、必要な方は以下よりダウンロードしてみてください。
まとめ
いかがでしたでしょうか？
今回のポイントは、
・Power BIでデータを扱う場合には、基本的にデータを縦持ちにする
・取り込みたいデータが縦持ちでない場合には、どこかでデータを変換する必要がある
・PowerBIにはアンピボット（列のピボット解除）機能という便利機能がある
ということでしたね。
弊社のセミナーを受講済みの方は、このような細かな部分の質問でも可能な限りご質問に回答していますので、クエリエディターがうまく設定できないなどあれば、遠慮なくご質問ください。
たまたまこの記事を見られた方PowerBIに関して、ちょっとした質問先が欲しいんだよねーという方がいらっしゃれば、ぜひ弊社のセミナーの受講をご検討ください。
ちなみに、現時点ではPower BIセミナーは入門編、応用編は、「DAXとデータモデル編」だけですが、新たな応用編のコンテンツとして、「クエリエディター編」も提供したいと考えています。
※受講後に、セミナー内容に直接関係のない内容に関する質問をお受けするサービスは、今後受講後の期限を設けたり、有料となる可能性がございます。
