データソースの変更でレポートを作り直した経験はないでしょうか？
データソースの設定は地味ですが運用上一番重要です。
この記事では
- 基本的なデータソース設定の流れ
- 設定を簡単にするためのノウハウ
を紹介します。
細かいノウハウの積み重ねで、作業工数を大きく減らせますのでぜひ読んでください。
基礎 データソースの作成と変更について
データソースの作成
Excelファイルの売上明細の情報をサンプルとして取り込みます。
データソースの設定手順
- リボンの中にある「Excel」をクリック
- ファイルのウィンドウで「売上.xlsx」ファイルを選択
- Excel内のシート一覧から対象のシートを選択
- 「読込み」ボタンを押す
- 取込みが完了すると右のフィールドパネルに表示されます。
この手順だけ見るとデータソースの設定は簡単に見えます。
ですが簡単に取り込めるように下準備が必要です。
データ準備の2つのチェックポイントを紹介します。
ポイント1 シート名にはわかりやすい名前をつける
Excelのシート名でPower BIに取り込まれます。
「sheet1」でなく、わかりやすい名前をつけましょう。
Power BI上でも変更できます。
ですが、作り直すたびに手修正が必要となるのでたいへんです。
ポイント2 数字型か日付型の項目をいれる
数字型か日付型のデータがあるとヘッダーを自動認識してくれます。
ヘッダーを設定する手間を省くことができます。
ヘッダーを自動認識する別の方法
実は２つのチェックポイントをしなくてもきれいに取込める方法があります。
それはExcelの「テーブル機能」です。
そちらについて解説します。
- Excelで「挿入」のメニューを選択
- シートのデータの１つをクリックする
- 「テーブル」を選択
- ポップアップメニューから「先頭行」にチェックをして「OK」を押す
- テーブル名にPower BIで表示させたい名前を登録します。
機械からみるとExcelのどのセルを取り込めばいいか判断が難しいです。
データ範囲を定義して他のツールからテーブルとして扱いやすくする機能です。
設定しないとおきる問題点を知りたい方は[Excelのテーブル設定をしないといけない理由]の記事を参照してください。
Power BIの取り込み画面はこのように変わります。
データの更新
データの更新は「更新」ボタンで可能です。
- 元のExcelファイルのデータを更新
- Power BIで「更新」ボタンを押す
データソースの変更
次の２つの変更について説明します。
- Excelファイルの保存場所かファイル名を変更した
- Excelデータの項目を追加や削除、項目名の変更した。
「1. Excelファイルの保存場所かファイル名を変更した」場合
データソースの設定を変更します。
- データ変換の下矢印をクリック
＊下をクリックなので注意 - 「データソース設定」を選択
- 「ソースの変更」をクリック
- ファイル指定の画面で新しいファイルを指定する
「2. Excelデータの項目を追加や削除、項目名の変更した」場合
「更新」を実行すると最新の項目一覧が反映します。
項目の追加削除をPower BIからしないこと
データやモデルの画面に項目の追加や削除の項目があります。
ここで変更するとエラーになるので、ここでは操作しないでください。
データの取得はPower Queryというツールで行っています。
Power BI上の項目削除や項目名変更の操作はPower Queryに記録されます。
エラーを修正するには、Power Queryで変更しないといけなくなります。
応用 Q＆A集
フォルダ内のファイルをすべて選択したい
毎月締めデータを特定のフォルダに保存することは運用上よくあります。
2020_5月度_売上.csv
2020_6月度_売上.csv
2020_7月度_売上.csv
といったファイルの保存方法です。
これを一括でロードする手順を紹介します。
データソースの設定で「フォルダ」を選ぶとできます。
- 「データを取得」を選択
- 「フォルダ」を選択する
- フォルダーのポップアップがでますので、パスを設定する
- 「結合」ボタンから「結合および読込み」を選択
＊「データの結合と定義」との違いは設定画面が起動するかどうかだけ - サンプルファイルから取り込み項目のベースとなるファイルを選択する。
以上で設定は終わりです。
取り込んだ後のデータをみると5,6,7月のデータが結合されて取り込まれてました。
csvファイルを取り込みましたが、Excelでも大丈夫です。
他の種類のファイルと交じるとうまくとれない事があるので、ファイルの種類は統一してください。
相対パスでファイルの場所を指定したい
相対パスを使うのは主に次の２ケースです。
- フォルダを変えた時に再設定したくない
- 他の人に自分のPower BIファイルを渡す
結論から言うと相対パスの指定は、私の知る限りできません。
暫定としてパラメータを使う方法を紹介します。
手順：パラメータにして、起動時に設定する
- Power Queryでパラメータ設定をする
- Power BIをテンプレートとして保存する(オプション)
の２つの設定をします。
データソース設定から始めます。
- Excelを選択
- 取込み対象のシートを選択
- 「データの変換」をクリック
Power Queryの画面が表示されるので、Pathを保存するパラメータを追加します
4.「パラメータの管理」を選択
5.「新規作成」リンクをクリック
6. パラメータ設定をします
作成したPathのパラメータをコードに埋め込みます
- 売上明細を選択
- ソースを選択
- コードを変更
元のコード
= Excel.Workbook(File.Contents("c:\Temp\PowerBI\売上.xlsx"), null, true)
変更後のコード
= Excel.Workbook(File.Contents(FilePath & "\売上.xlsx"), null, true)
終わったら「閉じて適用」をクリックして、Power Queryの画面を閉じます。
最後にPower BIをテンプレートファイルとして保存します
- ファイルから「名前をつけて保存」
- ファイルの種類から「Power BIテンプレートファイル」を選択
保存したテンプレートファイルを開いてください。
FilePathの入力を促すダイアログがでます。
利用者には最初に必ずFilePathを入力してもらいます。
これがテンプレートファイルの動きです。
まとめ
データソースの設定でよく使うExcelやcsvで説明しました。
Power BI上で定義を変えないようにするのが操作を簡単にするコツです。
データソース作成時
- ファイルやシート名にはわかりやすい名前をつける
- 数字型か日付型の項目をいれる
データソース変更時
- Power BIの「更新」のみ使う。
Power BIのデータ定義で変更しない
データソースの設定は、創意工夫で運用コストを大きく削減できる部分です。
- フォルダ内のファイルをすべて選択したい
- 相対パスでファイルの場所を指定したい
の２つの例を今回紹介しました。
よく使われる運用パターンがありましたら、また紹介します。
