データ分析プロジェクトでは、複数のチームメンバーが連携して作業を進めることが一般的です。しかし、チームでのコラボレーションには多くの課題が伴います。本章では、Power Query を使用したデータ収集、結合、変換を含むプロジェクトにおいて、コラボレーションを円滑に進めるための課題とその解決策について詳しく解説します。
23.1. データの一貫性の確保
23.1.1 ローカルファイルへのアクセス
Pwer BIやExcelで共同作業作業する際に最も一般的に直面する課題の１つは、データソースがローカルのエクセルファイルやテキストファイルにある場合です。
データの置き場所が個人によって異なっている場合、クエリの数が増えてくると、コードに書かれたファイルパスを修正する作業は大変な負担になります。
この問題を解決するには、ファイルパスにパラメータを使用することです。
最初に作成されていたクエリには、ファイルパスが直接書かれています。
let
Source = Csv.Document(File.Contents("C:\Data\CSV\SalesData.csv"),[Delimiter=",", Columns=18, Encoding=65001, QuoteStyle=QuoteStyle.None])
in
Source
上記の様にパラメータをセットすると、クエリは以下の様に修正できます。
let
Source = Csv.Document(File.Contents( FolderPath & "SalesData.csv" ),[Delimiter=",", Columns=18, Encoding=65001, QuoteStyle=QuoteStyle.None])
in
Source
これで、各自のパラメータを変更するだけでクエリを動かすことができます。
パラメータを操作し、開発、テスト、本番などのフェーズに応じてデータソースを切り替えて使用することができます。
23.1.2 Power BI でテンプレートを作成する
Power BIテンプレートは、Power BI用の特別なファイル形式です。既存のレポートからPower BIテンプレートをエクスポートすることができます。エクスポートされたファイルは.pbitファイルとして保存され、元のレポートのデータは含まれません。
テンプレートを開くと、元のレポートにパラメータが含まれている場合、パラメータの値を提供するよう求められます。
パラメータが設定されると、提供された値に基づいてレポートが読み込まれます。
また、事前に各人が利用するフォルダーパスが分かっている場合、以下の様に値の一覧を作成し、テンプレート形式で保存しておきます。
次回テンプレートを開くと、以下の様にフォルダーパスを入力するか、選択するようにできます。
ExcelのPower Queryエディタでもパラメータの設定はできますが、起動時にパラメータ値の入力を求めるダイアログを表示することはできません。
23.1.3 OneDrive for Business または SharePoint 上のファイルからデータをインポート
チームの一部が更新されたデータセットを使用している一方で、他のメンバーが古いデータを使用しているような状態は望ましくありません。クラウドストレージ（SharePoint、OneDrive）上でデータを共有し、常に最新のデータを利用可能にします。
手順
- OneDrive for Business または SharePoint にログインし、ワークブックをアプリで開きます。
- 「ファイル」＞「情報」の順に選択し、「パスのコピー」ボタンをクリックします。
- Power Query のデータソース選択で「Web」を選びます。
- 「Webから」のダイアログに、パスを貼り付け、最後の「?web=1」は削除して「OK」を押します。
- 「Webコンテンツへのアクセス」ダイアログが表示されたら、「組織アカウント」を選択し、「サインイン」した後、「接続」します。
- 「ナビゲータ」ダイアログが表示されますので、インポートするデータを選択します。
OneDrive for Business あるいは SharePoint からデータをインポートすると、コードは File.Contents ではなく Web.Contents を使用して開くようなります。
let
Source = Excel.Workbook(Web.Contents("https://xxxxxx-my.sharepoint.com/personal/xxxxxxxx/Documents/xxxxxxxxxx/Sample%20-%20Superstore.xls"), null, true),
Orders1 = Source{[Name="Orders"]}[Data]
in
Orders1
URLのデコード
Web.Contents の中に書かれているURLは、URLエンコードされているため、日本語やスペースなどが入っていると %20 などの文字に変換され、ごちゃごちゃしてしまいます。もう少しすっきり書きたい場合、取得したパスを変換サイトなどを使用してデコードします。
このデコードされたURLに交換してもアクセスすることができます。
23.1.4 SharePoint フォルダーコネクタ
フォルダー内の複数の Excel ワークブックやテキストファイルに接続する必要がある場合は、SharePoint サイトにファイルを保存し、SharePoint フォルダーコネクターを使用することができます。
- まず、23.1.3 で行ったように対象のフォルダにあるExcelファイルを開き、「パスのコピー」を行います。
- Power Query エディタで「データの取得」から「SharePoint フォルダー」を選びます。
- サイトＵＲＬに、先ほどコピーしたパスを貼り付け、サイト名以降は削除します。
- Microsoftアカウントで「サインイン」した後に「接続」をクリックします。
- ナビゲータダイアログにサイト内のファイルの一覧が表示されますので、「データの変換」を押します。
-
Folder Path列で目的のフォルダーに絞り込みます。 -
Contents列のBinaryにファイルがあるので、Excel.Workbook関数を使って開きます。
以降の操作の仕方は、「第２章 データ統合戦略」「第５章 コンテキストの維持と管理」「第１０章 ヘルパークエリ」などを参照してください。
23.2 クエリの管理
作成したクエリは、GitHubなどのサービスを利用して共通の環境でバージョン管理を行うことが求められますが、Power BI Desktopアプリにはバージョン管理機能がありません。そこで、以下のように管理を行います。
23.2.1 詳細エディタからコードをコピーする
GitHubなどのツールでクエリをテキストベースで管理するためには、Power Queryエディタの詳細エディタの内容を１つ１つコピーして、テキストファイルで保存することが一番初歩的な方法です。ファイル名にはクエリ名と拡張子「.m」をつけます。
23.2.2 Power BI Helper
この方法は、誰にでもできますが、クエリの数が増えてくると負担が大きくなります。そこで、「Power BI Helper」というツールを使用すると、この作業が簡単に行うことができます。
「Power BI Helper」は、RADACADのホームページにメールアドレスを登録することで、ダウンロード先のメールを受け取ることができます。
インストールが終わって起動すると、以下のような画面が表示されます。
このツールは Power BI Desktopと連携して動作するため、Power BI Desktop を起動して、該当のファイルを開いている状態で操作を行います。
最初に「Connect to Model」ボタンをクリックします。
「Choose the Power BI file」の項目に、現在開いているPower BIファイルの名前が表示され、テーブルなどの情報が表示されます。複数開いている場合は、選択ができます。
「M Script」のタブを選択すると、Power Queryのクエリコードが表示されます。
このコードを範囲指定してコピーしてもいいのですが、「Documentation」タブからXMLファイルに出力することができます。テーブルの情報やDAX式なども1つのファイルになって出力することができます。
23.3 データ プライバシー ファイアウォール
クエリを作成する際、異なるデータソースを組み合わせることは一般的です。しかし、それが原因でエラーが発生する可能性があります。このエラーはフォーミュラ ファイアウォール、またはデータ プライバシー ファイアウォールが働いた結果です。データ プライバシー ファイアウォールは、データセキュリティとプライバシーを強化し、意図しないデータ漏えいを防ぐために設計された重要なメカニズムです。
- データ漏えい防止: データが意図しない形で他のソースに渡るリスクを低減。
- 法規制への準拠: GDPRやHIPAAなど、データプライバシーに関する法規制を守るため。
- セキュリティ強化: 特に機密データや個人情報が含まれる場合に重要。
ファイアウォールは、Power Query のクエリをパーティションと呼ばれる独立した単位に分割します。パーティションは互換性のあるデータソースの身にアクセスでき、他のパーティションを参照する場合、ファイアウォールを介して安全なアクセスを行います。
- パーティションは互換性のあるデータソースにのみアクセスできる。
- 他のパーティションを参照する場合、ファイアウォールを介した安全なアクセスが必要。
23.3.1 他のパーティションへの参照
ExcelのシートにURLを入力して保存します。
Power Query エディタで、そのエクセルファイルを読み込み、URLを取り出すクエリを作成します。
let
Source = Excel.Workbook(File.Contents("C:\Data\WebUrl.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
Column1 = Sheet1_Sheet{0}[Column1]
in
Column1
このクエリの結果を参照してウエブにアクセスするクエリを作成します。
このルールに違反すると、以下のようなエラーが発生します。
Formula.Firewall: クエリ 'クエリ1' (ステップ 'Source') は他のクエリまたはステップを参照しているため、データ ソースに直接アクセスできません。このデータの組み合わせを再構築してください。
let
Source = Web.BrowserContents(Url)
in
Source
このクエリの結果は、以下の様になります。
あるデータソースを参照してデータを取得するクエリの結果を、別のクエリで使用してアクセスしようとすると、Formula Firewallのルールに違反します。
このエラーの解決方法の１つは、パラメータを取得するロジックと、そのパラメータを使ってアクセスするロジックを同じクエリに入れることです。
let
Source = Excel.Workbook(File.Contents("C:\Data\WebUrl.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
myURL = Sheet1_Sheet{0}[Column1],
WebContents = Web.BrowserContents( myURL )
in
WebContents
上記の警告が出ますが、プライバシーレベルの設定を両方「パブリック」あるいは「組織」にすればアクセスできます。
もう1つの方法は、エクセルからURLを取得するクエリを関数にすることです。
() as text => // これを追加する
let
Source = Excel.Workbook(File.Contents("C:\Data\WebUrl.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
Column1 = Sheet1_Sheet{0}[Column1]
in
Column1
これで、先ほどのクエリが正常に動作します。
23.3.2 互換性のあるデータソースへのアクセス
先ほどのプライバシーレベルの設定では、両とも「パブリック」で設定を行いましたが、一方を「パブリック」もう一方を「組織」とするか、どちらかを「プライベート」あるいは両方を「プライベート」に設定すると以下のようなエラーが表示されます。
一度設定されたプライバシーレベルは、「データソース設定」から変更ができます。
- パブリック, なし : 他方が「パブリック」あるいは「なし」に設定されていれば、クエリは実行できます
- 組織 ： 他方が「組織」に設定されていれば、クエリは実行できます
- プライベート : 常にファイアウォールエラーが発生します
23.3.3 プライバシーレベルを無視する
機密性の低いデータを扱う場合、プライバシーレベルの設定を無視することで、チェックをスキップできるため、パフォーマンスの低下を防ぐことができます。
また、Power BI のオプションで、プライバシーレベルを無視する選択ができます。
この設定を使用すると、機密または機密データが露出する可能性があります。