はじめに
ファイルの全量の現状把握やSharePointの運用管理といったシーン、まずはどのようなファイルが保存されているのか、といったことが必要になります。
このような事態は、プログラミングスキルがなくても、ExcelのPower Queryで実は解決できてしまうんですよね。
今回は、データソースから集計の自動化に役立つPower Queryを、
- ファイルサーバーのファイル コンテンツ・パスの把握
- SharePoint フォルダのファイル コンテンツ・パスの把握
- SharePoint ListsからPower Automateで利用できる
IDの把握
といった活用を目的に記事を書いていきます！
Power QueryはExcelを想定しています。
バージョンは、Excel for Microsoft 365を想定
Twitterの友人より
＞バージョンは、Excel for Microsoft 365を想定
とありますが、
ExcelからSharePointを参照するUIが表示されるのは Microsoft 365 Apps for Enterpriseの場合で、
Microsoft 365 Apps for businessの場合はUIが表示されないです。Power Query Editorでコーディングすればアクセスできるのですが...
＞バージョンは、Excel for Microsoft 365を想定
— ちゅん🐤 (@KotorinChunChun) April 21, 2024
とありますが、
ExcelからSharePointを参照するUIが表示されるのは Microsoft 365 Apps for Enterpriseの場合で、
Microsoft 365 Apps for businessの場合はUIが表示されないです。Power Query Editorでコーディングすればアクセスできるのですが...
Microsoft365 Apps for businessとEnterpriseの比較
— ちゅん🐤 (@KotorinChunChun) February 27, 2022
やっぱり表示されるメニューが違う。
ちなみに無料のPowerBI Desktopなら、Enterprise持ってない人でも、GUI操作によってSharePointの取得クエリ書ける。それをExcelにコピーするのもありかも。 pic.twitter.com/M8T534KNsq
バージョンについてはExcel for Microsoft 365でも差異があるようです！
ご指摘感謝です！
Power Queryとは何ぞや、の部分は下記をご参照ください。
今回ご紹介するテクニックは、
- 転職して新しい環境に移った
- 新しいプロジェクトにアサインされた
といった環境の変化に対し、非常に有益なテクニックです。
1. ファイルサーバーを調査する
フォルダの中のファイルを調べるには、Excelの[データ]タブ > [データの取得] > [ファイルから] > [フォルダーから]と選択します。
外付けHDDを丸ごと選択してみると、こんな雰囲気で一覧が表示されます。
ここで[データの変換]をクリックすると、これだけでドライブの配下のファイルが漏れなく表示されるわけです。
たったこれだけ・・・というので驚きを隠せません。
読み込める情報は以下のものです。
| 列名 | 内容 | 内容の説明 |
|---|---|---|
| Content | 内容 | ファイルのバイナリデータ |
| Name | 名前 | ファイルの名前 |
| Extension | 拡張子 | ファイルの拡張子 |
| Date accessed | アクセス日時 | ファイルに最後にアクセスされた日時 |
| Date modified | 変更日時 | ファイルが最後に変更された日時 |
| Date created | 作成日時 | ファイルが作成された日時 |
| Attributes | 属性 | ファイルの属性（例：隠しファイル、読み取り専用など） |
| Folder Path | フォルダーパス | ファイルが保存されているフォルダーのパス |
ファイルの一覧を作成する
上記からファイルの一覧を作成するために必要な列は、NameとFolder Path、
拡張子別の集計がしたい場合はExtensionを使いましょう。
「Excelのデータをフォルダーから集計する」といったシーンにはContentから、データをひとまとめにすることができます。
Folder PathとNameの結合でファイルのフルパスも取得できるので、非常に便利です。
ファイル サーバー丸ごと指定して実行すると、思わぬ量が出力されてしまい、大変なことになるので絞り込みをしっかりしましょう。
ファイル種別を拡張子から算出するカスタム関数
Extension列で拡張子が抽出されているので、Power Queryでファイルを分類することも可能です。
下記のカスタム関数を用いて、ざっくりとファイル種別を判別することができます。
let
GetCategoryByExtension = (extension as text) as text =>
let
ext = Text.Lower(Text.AfterDelimiter(extension,".")),
mapping = [
doc = "Word",
docx = "Word",
xls = "Excel",
xlsx = "Excel",
xlsm = "Excel",
ppt = "PowerPoint",
pptx = "PowerPoint",
accdb = "Access",
mdb = "Access",
eml = "Outlook",
msg = "Outlook",
pdf = "PDF",
txt = "テキストファイル",
jpg = "画像ファイル",
jpeg = "画像ファイル",
png = "画像ファイル",
gif = "画像ファイル",
mp4 = "動画ファイル",
avi = "動画ファイル",
mov = "動画ファイル",
mp3 = "音声ファイル",
wav = "音声ファイル",
html = "ウェブファイル",
css = "ウェブファイル",
js = "JavaScript ファイル",
java = "Java ソースコード",
py = "Python ソースコード",
cpp = "C/C++ ソースコード", c = "C/C++ ソースコード"
],
category = Record.FieldOrDefault(mapping, ext, "その他")
in
category
in
GetCategoryByExtension
使い方は
- 空のクエリの追加
- 詳細エディターに上記の関数を貼り付け
こちらにて使用することができます。
関数で実行していることは、マッピングを使用してファイル種別を取得しています。
mapping = []を調整すれば分類を変えることが可能。
- どこに何のファイルがあるのか
- どういったファイルが多いのか
- どこのフォルダに格納されているのか
一瞬で可視化できることが魅力ですね。
ここからPowerShellやVBAでファイルのリネームなど、良い具合に整理することも魅力的です✨
2. SharePoint フォルダを調査する
SharePoint フォルダを調べるするのは、Excelの[データ]タブ > [データの取得] > [ファイルから] > [SharePoint フォルダーから]。
① サイトパスを指定
② Microsoft アカウントでサインインしてから接続します。
結果は1. ファイルサーバーを調査すると同様に結果が出力されます！
ファイルパスがURLで可視化できるので、Power Automateのパスを使用してファイル コンテンツを取得やパスを使用してファイル メタデータを取得で活用できます。
ファイルだけではなく、フォルダの構成も一発で可視化することができるため、非常にお勧めです。
3. SharePoint Lists
フォルダの中のファイルを調べるには、Excelの[データ]タブ > [データの取得] > [オンライン サービスから] > [SharePoint Online リストから]と選択します。
① サイト URLを指定します
- 実装の 2.0を選択すると詳細が選択できます。ビューによる表示する列の設定が可能。
② 読み込むリストを指定
- ナビゲーターにて読み込むリストを指定します
SharePointのドキュメントライブラリも指定することができます。
ドキュメントライブラリはフォルダでは🧐と思いますが、こちらから指定することによって、ライブラリに追加された列が可視化されます。
実装 2.0
SharePoint Listsのビューに沿って表示されることがポイントです。
| 列名 | 説明 |
|---|---|
| タイトル | 項目のタイトル |
| 色タグ | 項目に関連付けられた色タグ |
| コンプライアンス資産ID | コンプライアンスのための資産識別ID |
| ID | 項目の一意識別子 |
| コンテンツタイプ | 項目のコンテンツタイプ |
| 更新日時 | 項目が最後に更新された日時 |
| 登録日時 | 項目が登録された日時 |
| 登録者 | 項目を登録したユーザー |
| 更新者 | 項目を最後に更新したユーザー |
| バージョン | 項目のバージョン情報 |
| 添付ファイル | 項目に添付ファイルが存在するかどうか |
| 編集 | 編集するためのリンク |
| 種類 | 項目の種類 |
| 子アイテムの数 | 項目に含まれる子アイテムの数 |
| 子フォルダーの数 | 項目に含まれる子フォルダーの数 |
| ラベルの設定 | 項目に設定されているラベル |
| 保持ラベル | 項目に適用されている保持ラベル |
| 保持ラベルを適用済み | 保持ラベルが適用されているかどうか |
| ラベルの適用者 | ラベルを適用したユーザー |
| レコードとして登録されているアイテム | アイテムがレコードとして登録されているかどうか |
| アプリの作成者 | アプリを作成したユーザー |
| アプリの変更者 | アプリを最後に変更したユーザー |
実装 1.0
出力される列数が多いです。
| 列名 | 説明 |
|---|---|
| FileSystemObjectType | ファイルシステムオブジェクトのタイプ（ファイル、フォルダなど） |
| Id | ID |
| ServerRedirectedEmbedUri | サーバーがリダイレクトした組み込みURI |
| ServerRedirectedEmbedUrl | サーバーがリダイレクトした組み込みURL |
| ContentTypeId | コンテンツタイプID |
| Title | タイトル |
| OData__ColorTag | 項目に関連付けられた色タグ |
| ComplianceAssetId | コンプライアンス資産ID |
| ID.1 | 項目の別のID（二重登録などの場合） |
| Modified | 更新日時 |
| Created | 登録日時 |
| AuthorId | 登録者ID |
| EditorId | 項目を最後に編集したユーザーのID |
| OData__UIVersionString | 項目のUIバージョン文字列 |
| Attachments | 項目に添付ファイルがあるかどうか |
| GUID | グローバル一意識別子 |
| FirstUniqueAncestorSecurableObject | 最初の一意な最上位のセキュリティオブジェクト |
| RoleAssignments | ロール割り当て アクセス許可レベル情報 |
| AttachmentFiles | 添付ファイルのリスト |
| ContentType | コンテンツタイプ情報 |
| GetDlpPolicyTip | データ損失防止ポリシーヒントを取得するための関数 |
| FieldValuesAsHtml | HTML形式フィールド値 |
| FieldValuesAsText | テキスト形式フィールド値 |
| FieldValuesForEdit | 編集のためのフィールド値 |
| File | ファイル関連（存在する場合） |
| Folder | フォルダー関連（存在する場合） |
| LikedByInformation | 「いいね！」 |
| ParentList | 親リスト |
| Properties | プロパティ |
| Versions | バージョン |
| Author | 登録者 |
| Editor | 更新者 |
アクセス許可レベルも出るとは凄い情報量・・・！
抽出時にクエリの列の評価は内部名で表示されるため、どの列に該当するのか、見易さに劣ります。
この方法について・・・！
ID列を可視化できるのは、とっても嬉しい!
そのままPower Automateの項目の更新アクションで使えるので、非常に便利です！
ほかにも項目の操作のIDとして利用できます。
ユーザー列や選択肢は、レコードとしてPower Query内で表現されるので、Power Query内でドリルダウンしていく列になります。
想像以上に沢山のデータが、こんなに手軽に取得できるのか！！と驚きを隠せない操作です！
おわりに
今回の紹介はPower Queryになりました。
この操作、技術的な仕事をしている人だけではなく、コンサルタントやバックオフィスの方々にも広く知っていただきたいと思います。
Power Queryはホントにハンパないです！
Salesforceの中身ぶち抜いたりとかもできたりもできます😀
使いこなしてみてください！