PowerShellとPowerBIを使ってファイル一覧を作ってみた

分析屋

2023年11月15日 20:25

分析屋の角です。

前職ではエクセルで資料作りをよくしていたのですが、何階層もあるフォルダの中から特定のファイルを探したり、要らないファイルが分からなくなってしまい容量が大きくなる一方で大変な思いをしました…

そこでこの半年間学んできたPowerBIとPowerShellを駆使して、ファイルを検索できるツールをBIで作ってみました！

完成形

今回は私のダウンロードフォルダを例に作成します。

＜フォルダ一覧＞
フォルダ一覧からは、ダウンロードフォルダに格納されているフォルダ直下のファイルサイズの合計を見ることができます。

↓ドリルスルーすると...

＜ファイル一覧＞
フォルダ直下に格納されているファイルを一覧で見ることができます。
さらに更新日をスライサーで絞って、使用していないファイルを発見することもできます。

BI化までの作業概要

前半・後半に分けて説明します。

<前半>
ファイル名、ファイルのフルパス、更新日などの情報を抜き取り、ファイル一覧をエクセルで作成します。この作業はPowerShellにやってもらいます。

↓

<後半>
作ったエクセルはPowerBIに読み込ませ、計算列・メジャーを追加。スライサーなどをつけて検索しやすいように視覚化します。

前半：PowerSellを使ってファイル一覧(エクセル化)を作成。

1）PowerSell ISEを開いて、スクリプトを新規作成。
※PowerShellの編集方法は他にもありますが、ISEはコードの種類によって色分けしてくれるので私はよく使っています。

2）コードを入力してスクリプトを保存。（コードは下記にあります。）
保存後、実行(ショートカット:F5)します。

変数の$FullPathと$CheckPathには任意のパスを設定してください。

$FullPathはファイル一覧のエクセルのフルパスを入れておきます。PowerShellがエクセルブックを新規作成（もしくは上書き）するので、前もってExcelを作っておく必要はありません。
$CheckPathはチェックするパスを入れます。ここで設定したパスの配下を一覧化することができます。

※上記の例ではダウンロードの配下を調べたいので、ダウンロードフォルダのパスを入れています。

# ファイル名を定義（書き換えてください！）
$FullPath = "C:\Users\sample\sample\FileList.xlsx"
$CheckPath = "C:\Users\〇\downloads\"

#Excelを開き、book、sheetを用意
$datas = Get-ChildItem $CheckPath -Recurse
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$book = $excel.Workbooks.Add()
$book.Sheets(1).Name = "ファイルリスト"
$sheet = $book.Sheets("ファイルリスト")

#ヘッダ作成
$RowIndex = 1
$sheet.Cells.Item($RowIndex,1) = "DirectoryName"
$sheet.Cells.Item($RowIndex,2) = "Name"
$sheet.Cells.Item($RowIndex,3) = "CreationTime"
$sheet.Cells.Item($RowIndex,4) = "LastAccessTime"
$sheet.Cells.Item($RowIndex,5) = "LastWriteTime"
$sheet.Cells.Item($RowIndex,6) = "Length"

$RowIndex = 2

#データ書き込み（２列目から）
foreach ($data in $datas) {
    $sheet.Cells.Item($RowIndex, 1) = $data.DirectoryName
    $sheet.Cells.Item($RowIndex, 2) = $data.Name
    $sheet.Cells.Item($RowIndex, 3) = $data.CreationTime
    $sheet.Cells.Item($RowIndex, 4) = $data.LastAccessTime
    $sheet.Cells.Item($RowIndex, 5) = $data.LastWriteTime
    $sheet.Cells.Item($RowIndex, 6) = $data.Length    
    $RowIndex++
}

#新規保存、もしくは上書き保存する
$excel.DisplayAlerts = $FALSE
$book.SaveAs($FullPath)
$excel.Quit()
$excel = $null

3）Excelの中身を確認しておきます。

DirectoryNameはファイルが置いてあるディレクトリパス。（空白になっているレコードはフォルダになります。）
Nameはファイルまたはフォルダの名前
Lengthはファイルサイズです。フォルダ自体にサイズはないようなので、フォルダサイズは1になっているようです。単位はBで読みにくいため、あとでPowerBIを使ってKBに変換します。

後半：PowerBIで視覚化

1）PowerBIで作成したファイル一覧を読み込みます。

2）テーブルビューでちゃんと入っているか確認します。

3）DAX関数を使って計算列などを追加。

空白（フォルダを表示）のレコードを除いたテーブルを作成 …①

DirectoryNameを一意にしたテーブルを作成...②

①と②をリレーションで繋ぎます。

②のファイルサイズの合計を出すメジャーを作成します。単位はバイトになっているのでKB表示になるよう÷1000します。

4）powerBIにデータは入ったのであとは視覚化すれば、完成です！

あとがき

PowerShellはまだ新米レベルですが、ファイル操作は割と簡単にコードをかけたので事務作業の簡略化につなげやすいなと感じました。

また、応用としてPowerBIを使ってファイル一覧を作り、ファイルをより検索しやすくしました。

前半の工程で作成したエクセルのフィルター機能を使ってもファイル検索などはできますが、BIを使ってツール化することで他の人と共有できたり、なにより使いやすいUIがBI化するメリットだと感じました！

PowerShellやPowerBIをより身近に感じていただけると嬉しいです(^^)

参考

PowerShellコードは下記サイトを参考にしました。

ここまでお読みいただき、ありがとうございました！
この記事が少しでも参考になりましたら「スキ」を押していただけると幸いです！

株式会社分析屋について

弊社が作成を行いました分析レポートを、鎌倉市観光協会様HPに掲載いただきました。

ホームページはこちら。

noteでの会社紹介記事はこちら。

【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！
【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。
【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。
【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。
【SES】
SESサービスも行っております。

この記事が参加している募集

やってみた

35,673件