ファイルサーバーやSharePointサイトの現状把握？とりまExcelのPower Query使お！

はじめに

ファイルの全量の現状把握やSharePointの運用管理といったシーン、まずはどのようなファイルが保存されているのか、といったことが必要になります。

このような事態は、プログラミングスキルがなくても、ExcelのPower Queryで実は解決できてしまうんですよね。

今回は、データソースから集計の自動化に役立つPower Queryを、

1. ファイルサーバーのファイル コンテンツ・パスの把握
2. SharePoint フォルダのファイル コンテンツ・パスの把握
3. 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を想定
とありますが、
ExcelからSharePointを参照するUIが表示されるのは Microsoft 365 Apps for Enterpriseの場合で、
Microsoft 365 Apps for businessの場合はUIが表示されないです。Power Query Editorでコーディングすればアクセスできるのですが...

— ちゅん🐤 (@KotorinChunChun) April 21, 2024

Microsoft365 Apps for businessとEnterpriseの比較
やっぱり表示されるメニューが違う。

ちなみに無料のPowerBI Desktopなら、Enterprise持ってない人でも、GUI操作によってSharePointの取得クエリ書ける。それをExcelにコピーするのもありかも。 pic.twitter.com/M8T534KNsq

— ちゅん🐤 (@KotorinChunChun) February 27, 2022

バージョンについては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でファイルを分類することも可能です。
下記のカスタム関数を用いて、ざっくりとファイル種別を判別することができます。

カスタム関数 GetCategoryByExtension

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

使い方は

1. 空のクエリの追加
2. 詳細エディターに上記の関数を貼り付け

こちらにて使用することができます。
関数で実行していることは、マッピングを使用してファイル種別を取得しています。
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の中身ぶち抜いたりとかもできたりもできます😀
使いこなしてみてください！