この記事はOffice 365 Advent Calendar 2018 (12/11) に参加しています。
Power BIでSharePointのファイルやリストを扱うにはどうすればいいのか。
専用コネクターが用意されているけど「実際どうやってSharePointと繋いでいるのか」という話をあまり聞かない気がするので、備忘も兼ねて書いていきます。
なお、筆者はオンプレSharePointを触れる環境にありません。
扱う内容はSharePoint Onlineが前提となりますが、オンプレSharePointユーザーの方にも参考になる内容です…多分。
Power BIで使えるSharePointコネクター
1. SharePoint フォルダー
2. SharePoint Online リスト
3. SharePoint リスト
この三つがSharePointに絡むコネクターです。
SharePoint Online リストとSharePoint リストは動作上の違いが確認できなかったので一緒にご紹介します。
各コネクターの共通部分
接続の前準備
どのコネクターも、利用するには必ずサイトのURLを入力する必要があります。
基本はhttps://xxx.sharepoint.com/sites/sitesname
サブサイトのコンテンツを読み出したいならhttps://xxx.sharepoint.com/sites/sitesname/subsitesname
ここで!マークにマウスオーバーすると設定のヒントを見ることができます。
SharePoint系コネクターの場合、「サブフォルダーを含めずにSharePointサイトのルートURLを入力します」と表示される。
ここで言うルートURLとは、以下のURLなら/sitesnameまでの部分。
https://xxx.sharepoint.com/sites/sitesname/Lists/Test%20List/AllItems.aspx
後ろのリストやライブラリーを表す部分を消さずにダイヤログに入力するとエラーが出ます。
正しいルートURLを入力すると、OKボタンが有効化されてSharePointのコンテンツにアクセスできます。
アクセス許可の設定
初めてSharePointコネクターを利用する際はアクセス許可を設定する必要があります。
アクセス許可には幾つか種類がありますが、SharePointを利用する場合は基本的にMicrosoft アカウントを利用します。
適用対象レベルは一番下のサイト名が入ったURLを選びます。
それ以外のURLは会社のポリシー次第ですが、アクセスが拒否されてエラーになる事が多いと思います。
[サインイン]をクリックするとお馴染みOffice365のサインイン画面が出てくるので、自身のアカウントでサインインします。
サインインすると、以下の画面表示になるので[接続]をクリックしてSharePointにアクセスします。
SharePoint フォルダー
ドキュメントライブラリーと接続する
SharePoint フォルダーコネクターはドキュメントライブラリーからファイルを読み出すのに使用します。
このコネクターでサイトを読み出すと、サイト上のドキュメントライブラリーに存在する全てのファイルが一覧で表示されます。
[編集]をクリックして、Power Queryで読み出すファイルを指定しましょう。
各列の説明
- Content
- Binary(ファイルの中身)
- Name
- ファイル名
- Extension
- ファイル拡張子
- Date accessed
- ファイルへの最終アクセス日時
- Date modified
- ファイルの最終更新日時
- Date created
- ファイルの作成日時
- Attributes
- ファイルサイズ、コンテンツタイプ、ファイルの種類
- Folder Path
- ファイルが存在するライブラリー、もしくはフォルダーのパス
使い方
- Content列にあるBinaryを直接クリックして特定のファイルを読み出す。
- 列名部分にある[ファイルの結合]ボタンで複数のファイルを結合する。
-
[ファイルの結合]ボタンは同じテーブルを持つファイルを結合する時に使います。
- 例えば売上集計の1月分と2月分を一つのテーブルとして扱う時です。
- Name列やFolder Path列を利用してフィルターをかけることでより有効に使う事が出来るでしょう。
Folder Path列の命名規則
https://xxx.sharepoint.com/sites/サイト名/ドキュメントライブラリー名
https://xxx.sharepoint.com/sites/サイト名/ドキュメントライブラリー名/フォルダー名
https://xxx.sharepoint.com/sites/サイト名/サブサイト名/ドキュメントライブラリー名
https://xxx.sharepoint.com/sites/サイト名/サブサイト名/ドキュメントライブラリー名/フォルダー名
覚えておくと何処のサイト、ライブラリー、フォルダに何が入っているかの確認に便利です。
使用上の注意
このコネクターを利用した場合、ドキュメントライブラリーで列を利用して付けたプロパティを確認する事ができません。
この様にカテゴリー分けをして整理していても、列の値を利用してフィルターを掛けたりするのはあきらめましょう。
SharePoint Online リスト(SharePoint リスト)
リスト、またはドキュメントライブラリーと接続する
SharePoint Online リスト(SharePoint リスト)コネクターは、リストとドキュメントライブラリー両方の読出しに対応しています。
このコネクターでサイトを読み出すと、サイト上のリストとドキュメントライブラリーの一覧が表示されます。
読み出すリストかライブラリーにチェックを入れ、[OK]をクリックします。
各列の説明
列の種類が多いので、主要な列に絞って解説します。
- Id
- コンテンツに自動で割り振られる一意のID
- Title
- リストやライブラリー新規作成時に必ず作成される[タイトル]列
- この列から右側にユーザー作成列やデフォルトで存在している更新日などの列のプロパティが表示されている。
- OData_云々
- 作成時に列名を日本語で作成したユーザー作成列。
- 英語で列名を作成した場合、OData_は付かない。
- OData__UIVersionString
- アイテムのバージョン数
- Modified
- アイテムの更新日
- Created
- アイテムの作成日
- Attachments
- 添付ファイルの有無
- GUID
- アイテムに割り振られたSharePointのGUID
- AttachmentFiles
- 添付ファイルの情報(リストのみ)
- FieldValuesAsHtml(FieldValuesAsText、FieldValuesForEditも同様)
- リスト、ライブラリーに存在する当該レコードの全ての列の値
- File(ライブラリーのみ)
- アップロードされたファイルの名前、URL等のレコード
- Folder(ライブラリーのみ)
- フォルダー名やフォルダー内のファイル情報のレコード(ライブラリにフォルダーが存在している場合のみ)
- Author
- アイテムの作成者のレコード
- Editor
- アイテムの更新者のレコード
- CheckoutUser
- アイテムを現在チェックアウトしているユーザーのレコード
使い方
- リスト
- リストの内容を読み出す。
- 添付ファイルを読み出す。
- ライブラリー
- 列のプロパティに基づいてファイルを選別し、読み出す。
このコネクターでは、列のプロパティを直接読み出す事が出来ます。
読み出すファイルをルールに基づいて自動で変えたり、複雑なフィルター条件を設定したい場合は便利です。
使用上の注意
このコネクターを利用した場合、リストの添付ファイルやライブラリーのファイルを事前処理無しで読み出すことはできません。
Power Queryでカスタム列を使いファイルのURLを生成し、そこからBinaryを読み出す工程を入れる必要があります。
ライブラリーの場合、ファイルを扱うにはFile列を展開する必要があります。
リストの場合、ファイルを扱うにはAttachmentFiles列を展開する必要があります。
場合によっては上記に加え、FirstUniqueAncestorSecurableObject列を展開する事もあります。
ドキュメントライブラリーをPower Queryで読み出す
ドキュメントライブラリーのファイルはFile列内に読出しに必要な情報が格納されています。
右上の展開ボタンを押してFile列を展開しましょう。
ここで展開する列はLinkingUrl、Name、ServerRelativeUrlの三つがあれば十分だと思います。
展開すると、以下の様になります。
- LinkingUrl
- 絶対URLが入っている為、一番加工しやすいURL
- 但しフォルダー及びフォルダー内のファイルはURLが表示されない
- Name
- ファイル名
- ServerRelativeUrl
- ファイルの相対URL
- ファイルの相対URL
読み出す前処理
ここではLinkingUrlを利用した場合の読出し方法を説明します。
ServerRelativeUrlを使う方法は後述のリストを読み出す方法がそのまま使えるので、ここでは解説しません。
完全URLの生成
まず、LinkingUrl列の構造を見てみると以下の様に絶対URLの後ろに?d=で始まる余計な部分が付いています。
https://xxx.sharepoint.com/sites/サイト名/ドキュメントライブラリー名/ファイル名?d=wd28...
これを外すため、LinkingUrl列を選択して[変換]タブの[テキストの列]から[抽出]-[区切り記号の前のテキスト]をクリックします。
出てきたダイヤログに半角?を入れて[OK]を押します。
これで邪魔な部分が外れ、LinkingUrl列にファイルの完全URLが完成しました。
Binaryの生成
ここからPower BIで読み込むためには、LinkingUrl列のURLからファイルのBinaryを生成する必要があります。
[列の追加]から[カスタム列]を使い、以下の式を入力します。
Web.Contents([LinkingUrl])
これで[カスタム]列にLinkingUrl列に格納されていたファイルのBinaryが生成されました。
読出し
後はSharePoint フォルダーコネクターで解説したBinary読出し手順と一緒です。
必要なファイルを読み出してあげましょう。
リストをPower Queryで読み出す
リストの添付ファイルはAttachmentFiles列内に読出しに必要な情報が格納されています。
右上の展開ボタンを押してAttachmentFiles列を展開しましょう。
ここで展開する列はFileName、ServerRelativeUrlの二つです。
展開すると、以下の様になります。
- FileName
- 添付ファイル名
- ServerRelativeUrl
- 添付ファイルの相対URL
読み出す前処理
相対URLではBinaryを生成できません。
なので別の列の情報を使って完全URLを生成します。
完全URLの生成
まずFirstUniqueAncestorSecurableObject列からUrl列を展開しましょう。
Url列とServerRelativeUrl列を見比べてみると、以下の様に違いがあります。
- Url列
https://xxx.sharepoint.com/sites/サイト名
- ServerRelativeUrl列
/sites/サイト名/リスト名/List/Attachments/37/ファイル名
完全URLを作るには、Url列のsharepoint.com以降にServerRelativeUrl列の内容をくっ付けてあげれば良さそうです。
Url列を選択して[変換]タブの[テキストの列]から[抽出]-[区切り記号の前のテキスト]をクリックします。
出てきたダイヤログに半角/を入れて、詳細オプションを展開します。
「区切り記号のスキャン」を「入力の先頭から」、「スキップする区切り記号の数」を「2」に設定して[OK]を押します。
URLの必要な個所だけ抜き出す事が出来ました。
そしてUrl列とServerRelativeUrl列を選択し、[変換]タブの[テキストの列]から[列のマージ]をクリックします。
何も弄らず[OK]を押しましょう。
これで結合済み列に添付ファイルの完全URLが生成されました。
Binaryの生成
Power BIで読み込むためには、結合済み列のURLからファイルのBinaryを生成する必要があります。
[列の追加]から[カスタム列]を使い、以下の式を入力します。
Web.Contents([結合済み])
これでカスタム列に添付ファイルのBinaryが生成されました。
読出し
後はSharePoint フォルダーコネクターで解説したBinary読出し手順と一緒です。
必要なファイルを読み出してあげましょう。
まとめ
-
SharePoint用のコネクターは三つあるが、実質二つ。
- SharePoint Online リストとSharePoint リストの挙動が同じとは…。
-
ライブラリーとリストでファイルの読み出し方が全く異なる場合がある。
- 中でもリストコネクターの扱いは初心者殺しになること間違いなし。
- GUIだけでポチポチやるのは無理でした。
-
自由にSharePoint上のファイルを扱いたいなら覚えるべき二つ
- =Web.Contents([列名])
- 完全URLの作り方
SharePointを便利に使えば使う程、Power BIで読み出す手順が面倒になる謎仕様には頭を捻る。
せめてSharePoint フォルダーコネクターで列の値が取れればなぁ。