Power Query で Excel の最後のシートを取得する

はじめに

この記事は、Microsoft Power BI Advent Calendar 2024 の 20 日目の記事です。

Power BI 勉強会の「Data Preparation workout」で、皆さんにアドバイスをいただき学んだことをベースに、Power Queryで Excel の最後のシートを取得するふたつの方法について書きとめておきたいと思います。

シナリオ

毎月の売上データを管理している Excel ブックがあります。
「9月」「10月」「11月」…と毎月、新しいシートが右側に追加されていきます。

Power BI の Power Query を使って、Excel のデータを取得します。
取得する対象は、最新の月、つまり 最後のシート のデータです。
毎回シート名が異なるため、その度にシート名を修正するのは手間がかかります。そこで、自動的に最後のシートのデータを取得できるようにしたいと思います。

※本稿では話をシンプルにするため、非表示のシートは存在していないものとします。

準備

Power Queryで、最後のシートであるシート「11月」を選択してデータを取得します。

ステップ「ソース」のテーブルにはシートのみの 3 行が表示されています。

［詳細エディター］を確認すると次のようなコードが記載されています。

let
    ソース = Excel.Workbook(File.Contents("C:\売上データ\売上データ.xlsx"), null, true),
    #"11月_Sheet" = ソース{[Item="11月",Kind="Sheet"]}[Data],
    昇格されたヘッダー数 = Table.PromoteHeaders(#"11月_Sheet", [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"日付", type date}, {"金額", Int64.Type}})
in
    変更された型

方法 1 ：インデックスでシートを指定する方法

まず、シートをインデックスで指定する方法です。

今回のケースで考えると、最後のシートは 3 枚目のシートになります。
Power Query では、シートの番号は「0」、「1」、「2」…とインデックスが振られるので、3 枚目のシートのインデックスは 2 です。

ステップ「ナビゲーション」のコードを以下のように修正すると最後のシートが指定できます。

= ソース{2}[Data]

自動的に最後のシートを指定するためには、最後のシートのインデックスが分かればよいので、シート数をカウントし、カウントしたシート数から 1 をマイナスした数をインデックスとして指定します。

シート数を数えるには Table.RowCount 関数を使います。

［詳細エディター］で直接コードを編集します。

let
    ソース = Excel.Workbook(File.Contents("C:\売上データ\売上データ.xlsx"), null, true),
    ターゲット = Table.SelectRows(ソース,each([Kind]="Sheet")),
    シート数 = Table.RowCount(ターゲット),  //シート数をカウント
    取得データ = ターゲット{シート数-1}[Data],  //インデックスでシートを指定
    昇格されたヘッダー数 = Table.PromoteHeaders(取得データ, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"日付", type date}, {"金額", Int64.Type}})
in
    変更された型

ちょっと手間はかかりますが…
上手くいきました♪

方法 2：Table.LastN 関数を使う方法

次は、Table.LastN 関数を使って最後のシートを選択する方法です。

Power Query エディターのステップ「ソース」のテーブルでは、必ず上から Excel シートの順番通りにシートの行が表示されます。最後のシートは最後の行になるため、テーブルの最後の行を返す Table.LastN 関数を使い、最後のシートを選択します。

Table.LastN 関数を使うといっても、前述の方法 1 のように詳細エディターのコードを編集する必要はなく、UIの操作ボタンぽちぽちだけでOKです。

ステップ「ソース」以外のステップを削除してから、［ホーム］タブの［行の削減］グループの［行の保持］から［下位の行の保持］を選択し、保持する行数を「 1 」と入力し［OK］ボタンを押下します。

こちらの方法も、方法 1 と同様に上手くいきます♪
こちらの方が、簡単ですね

おわりに

どちらの方法がよいということではなく、どちらの方法も使えるように勉強し、より適切な方法を選ぶことが大事だと思います。
また、Power Query だけでなく、Excel などデータソースに関する勉強も欠かせないと、あらためて感じました。

マイペースな亀の歩み…ではありますが、何とか諦めずに学習を続けることができているのは、Power BI勉強会 の皆さんのおかげです。

今年は、名古屋や広島での勉強会にも参加し、皆さんと一緒に楽しい時間を過ごさせていただきました。
本当にいつもありがとうございます