当サイト主催のセミナーの受講後にいただいた質問とその回答をブログ記事としてまとめるシリーズの第5弾です。
なお、当サイトのセミナーにご参加いただいた方からは、セミナー内容に関するご質問やPowerBIに関する疑問や困りごとに関するご相談などを無料で承っていますので、ご受講いただいた方は遠慮なくご質問ください。
受講者からの質問内容
データベースから出力したファイルが年毎のシートになっているものを加工してダッシュボードで表示させる際に、年別の項目を入れたいのですが、読み込んだ時点で1つのシートにリレーションが張られてしまいうまく加工できません。
どのような操作をしたら年別のダッシュボードを出力できるようになるのか教えてください。
データベースから出力したデータや手作業で集計したデータが年別にシートに分かれて存在するというものよくある状況ですね。
シートごとにファイルを分ければ簡単ですが、できればそのままPower BIに取り込みたいところです。
もちろんPower BIでは、複数のシートを一気に取り込むことが可能ですので、今回は、複数シートのデータを一つのデータにまとめ、年度の項目をデータごとに付与する操作についてご説明していきます。
大まかな手順としては、
1.クエリでExcelの特定のシートを読み込む
2.PowerQueryエディタでデータの取り込み時の処理を編集
といったイメージです。
1.クエリでExcelの特定のシートを読み込む
今回の質問者様からいただいたデータはこんなイメージのものでした。
各シートの項目は「顧客」「売上」「出荷量」「担当」でそろっており、年度だけが異なるものです。ただし、各データには、年度や日付に相当するデータがありません。
年度ごとにデータを作成し個別に利用する場合には、年度や日付を意識しない場合もありますので、このようなデータが社内に存在することはよくあります。
データを作成する際に、最初から年度もしくは日付型のデータが一緒に増やされていれば、より簡単になりますが、このようなデータもPower BIであれば一度データの取り込みについて設定を行えば設定以降は、シート（年度）が増えても、同様にデータを読み取ることが可能です。
では、実際の操作について説明していきます。
a.Power BI Desktopを起動し、クエリを編集をクリック
b.Power Queryエディタが起動
以降の作業は、Power Queryエディタ上で実施します。
c.新しいソースでExcelを選択
d.データソースに取り込みたいExcelファイルを指定
今回のファイル名は、「同じ形式の年度の異なる複数シート.xlsx」としています。
e.ナビゲータで2016のシートを指定
ここで複数のシートを選択すると、それぞれ別のクエリが作成され、別のデータとして取り込まれてしまうため、複数のシートを指定せず、いったん2016の一つだけを指定します。
f.2016のシートが取り込まれる
指定した2016のシートだけが取り込まれるクエリが作成されますので、この後、作成された「クエリの設定」（画像右端）を編集していきます。
2.PowerQueryエディタでデータの取り込み時の処理を編集
a.クエリの設定の編集
このままだと2016年のデータしか取り込まれませんので、以下設定を行います。
・プロパティ：複数シート取り込み（プロパティ名は何でも構いませんがわかりやすい名称にしておきます）
・適用したステップ：「ソース」以外を削除（各項目の左に表示される×アイコンをクリック）
これで、Excelファイルのみが指定され、どのシートをどのように取り込むかを改めて設定できる状態になります。
b.すべてのシートをまとめて取り込むために「Data」項目の展開アイコンをクリック
ここで個別のシートを指定せずに、「Data」項目の展開アイコンをクリックすると、すべてのシートに含まれるデータをすべて展開し、一つのテーブルに保存するための処理となります。
「Data」項目が展開され、それぞれのシートに含まれる4つの項目が表示されます。今回は各シートの項目が揃っているので、一気に取り込みが可能ですが、もし項目が揃っていないようなデータの場合には、データにずれが生じますので、ご注意ください。
c.不要な列項目を削除＆列名変更
このままだと不要なデータが取り込まれてしまうため、データのプロパティを表している「Item」「Kind」「Hidden」などの列を削除します。（Shiftボタンを押しながら、3つの列をまとめて指定して右クリックで「列の削除」を選択）
この時点で、列としては必要なものだけが残っている状態となります。
ちなみに、Power Queryエディタでは、手動で行った操作がExcelのマクロの保存のようにステップごとに右側の「適用したステップ」の一項目（例：「展開されたData」など）として保存されていきますので、一度設定すれば改めて設定する必要はありません。
これで不要な列は削除できましたが、各シートのヘッダーに当たる部分が重複して表示されている状態ですので、ヘッダーを設定し、不要な行を削除します。
d.ヘッダーを設定＆不要な行をフィルタで削除
まずは、ヘッダーの設定です、ホームメニューから「1行目をヘッダーとして使用」を選択します。
するとデータの1行目がヘッダーに昇格しますが、「Name」（シートの名称）項目だった列が「2016」という名称になってしまいますので、列名を「年度」に変更しておきす。
これで列名の設定は完了しましたが、10行目などに「顧客」「売上」といった複数のシートに存在する項目名がそのまま残っていますので、この行をフィルタで削除します。
「顧客」列を右クリックし、フィルタを表示させ「顧客」のチェックを外します。
ここでは、「顧客」というテキストデータが含まれる行が除外されますので、例えば別の列として。「売上」列で「売上」を除外するというった形でも同じ結果が得られます。（売上や出荷量といったデータは基本的に顧客が含まれる行に存在しますので、一つだけ設定してあげればOKです）
これで、年度ごとに集計できるデータとしての準備が整いました。
最後に「閉じて適用」ボタンを押して、クエリを保存しておきます。
なお、今回の設定方法を行っていただく、その後同じ項目が含まれるデータという条件が整っていれば、2019年や2020年のデータを含むシート「2019」「2020」などが追加されても自動ですべてのシートを取り込んでくれます。
3.レポートを作成する
あとはレポート上で表示したい形でレポートを作っていただければOKです。今回の記事ではレポート作成部分は割愛します。
元データのExcelとPowerBIDesktopのサンプルファイルもダウンロードできますので、必要な方は以下よりダウンロードしてみてください。
ファイルサンプル：同じ形式の年度の異なる複数シート.xlsx
ファイルサンプル：同じ形式の年度の異なる複数シートの取り込みサンプル.pbix
まとめ
いかがでしたでしょうか？
今回のポイントは、
・いったん一つのシートを取り込んでから設定をし直す
・クエリエディタで、欲しいデータの形に整えていく
・その後新たなシートが追加されても自動でデータを取り込んでくれる
ということでした。
弊社のセミナーを受講済みの方は、このような細かな部分の質問でも可能な限りご質問に回答しています。
たまたまこの記事を見られた方Power BIに関して、ちょっとした質問先が欲しいんだよねーという方がいらっしゃれば、ぜひ弊社のセミナーの受講をご検討ください。
ちなみに、現時点ではPower BIセミナーは入門編、応用編は、「DAXとデータモデル編」だけですが、新たな応用編のコンテンツとして、「クエリエディター編」も提供したいと考えています。
※受講後に、セミナー内容に直接関係のない内容に関する質問をお受けするサービスは、今後受講後の期限を設けたり、有料となる可能性がございます。
