別ブックの最終シートの取り込み｜Power Query（M言語）入門

最終更新日：2023-02-09

別ブックの最終シートの取り込み

「パワク3本目」
Power Query（M言語）の問題と解答・解説

本シリーズは、ツイッターと連動企画になります。
ツイッターで問題を出して、それに解答と解説をしていく形でPower QueryとM言語の理解を深めていきます。

ページ内目次

パワク3本目
解答コード
GUI操作での作成
M言語の演算子

パワク3本目

#パワク3本目
別ブックの最終シート（一番右の最後のシート）をパワクで取り込んでください。
バス・ブック名の指定は任意
シート数が変化しても、とにかく一番最後のシートを取り込むという事です。
#パワク #PowerQuery

解答コード

let
    ソース = Excel.Workbook(File.Contents("D:\パワク練習\マスタ\商品マスタ.xlsx"), true, true),
    シート数 = Table.RowCount(ソース),
    シート = ソース{シート数-1}[Data]
in
    シート

シート数が分かれば、インデックスで取得できます。
RowCountは数値で返されますので、普通に四則演算ができます。
（※演算子についてはページ最後にまとめて記載しました。）

シート数は、Excel.Workbookで取得されたテーブルの件数ですね。

let
    ソース = Excel.Workbook(File.Contents("D:\パワク練習\マスタ\商品マスタ.xlsx"), true, true)
in
    ソース

上記の結果の通り、Excel.Workbookで取得した「ソース」はテーブルになります。
テーブルに関する関数は、
Table.○○○
という関数が多数あります。

シート数ではなく、最終のシート名を取得しても良いですね。

let
    ソース = Excel.Workbook(File.Contents("D:\パワク練習\マスタ\商品マスタ.xlsx"), true, true),
    シート名 = Table.Last(ソース)[Item],
    シート = ソース{[Item=シート名]}[Data]
in
    シート

TableをListにしてから最終を取得してもよいです。

let
    ソース = Excel.Workbook(File.Contents("D:\パワク練習\マスタ\商品マスタ.xlsx"), true, true),
    シート名 = List.Last(ソース[Item]),
    シート = ソース{[Item=シート名]}[Data]
in
    シート

「ソース」はExcel.Workbookで取得したものなのでテーブルです。
Table.Last(ソース)[Item]
Table.Last(ソース)これで最終レコードになり、その[Item]列です。

List.Last(ソース[Item])
テーブル「ソース」の列[Item]を指定しているので、これはリストです。
List.Last関数でリストの最後を取得しています。

上記2つの違いは順番の違いだけですので、書きやすい方を使えば良いと思います。

追記

上記およびこれ以降のコードでは、ブックにシートのみ存在する場合限定になっています。
テーブルや名前定義がある場合は、Excel.Workbookで取得したテーブルからシートだけに絞り込む必要があります。
Excel.Workbookは、シート・テーブル・名前定義の一覧になりますので、ここからシートに絞り込みが必要です。

let
    ソース = Excel.Workbook(File.Contents("D:\パワク練習\マスタ\商品マスタ.xlsx"), true, true),
    フィルター = Table.SelectRows(ソース, each ([Kind] = "Sheet")),
    シート数 = Table.RowCount(フィルター),
    シート = フィルター{シート数-1}[Data]
in
    シート

eachについては、今後の出題回で説明していきます。

GUI操作での作成

以下のGUI操作は一例です。
筆者がM言語直書きとの対比用に即興でやってみた手順になります。
操作・手順はいろいろなやり方があると思います。

今回は決まったシートではないので、ブック名のところを選択して「データの変換」

「Data」列を選択し、「他の列を削除」

左右に開いたアイコン

をクリックします。

1行目をヘッダーとして使用します。

こうしてできたクエリが以下になります。

let
    ソース = Excel.Workbook(File.Contents("D:\パワク練習\マスタ\商品マスタ.xlsx"), null, true),
    保存された最後の行 = Table.LastN(ソース, 1),
    削除された他の列 = Table.SelectColumns(保存された最後の行,{"Data"}),
    #"展開された Data" = Table.ExpandTableColumn(削除された他の列, "Data", {"Column1", "Column2", "Column3"}, {"Data.Column1", "Data.Column2", "Data.Column3"}),
    昇格されたヘッダー数 = Table.PromoteHeaders(#"展開された Data", [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"商品ID3", type text}, {"商品名称3", type text}, {"商品単価3", Int64.Type}})
in
    変更された型

これを元にいろいろ加工してもよいのですが、、、
多少M言語を理解すれば、最初に掲載した通り短いクエリで実現できるようになります。

M言語の演算子

※Learn 演算子
上記は少し見ずらいので特に必要な部分を以下に簡単にまとめました。

算術演算子

演算子	説明
+	加算
-	減算
*	掛け算
/	割り算
+x	単項プラス
-X	単項マイナス

比較演算子

演算子	説明
>	より大きい
>=	以上
<	より小さい
<=	以下
=	等しい
<>	等しくない

論理演算子

演算子	説明
or	論理和
and	論理積
not	論理否定

複合演算子(&)

例	説明
"A" & "BC"	テキストの連結: "ABC"
{1} & {2, 3}	リストの連結: {1, 2, 3}
[ a = 1 ] & [ b = 2 ]	レコードのマージ: [ a = 1, b = 2 ]

レコードの検索演算子

演算子	説明
[ ]	名前でレコードのフィールドにアクセスします。

リストインデクサー演算子

演算子	説明
{ }	0から始まる数値インデックスによってリスト内の項目にアクセスします。

型の互換性とアサーション演算子

演算子	説明
is	式 x is y は、 x の型が y と互換性がある場合は、true を返します。 x の型が y と互換性がない場合は、false を返します。
as	式 x as y は、 is 演算子のように、値 x が y と互換性があることをアサートします。

同じテーマ「PowerQuery（M言語）入門」の記事

別ブックのシートを列可変で取り込む
 セル値でパス・ブック・シート名を指定
別ブックの最終シートの取り込み
列数不定のCSVの取り込み
 CSVのA列が日付の行だけを取り込む
 A列のヘッダー名を変更する
 「売上」が数値の行のみ取り込む
 2つのテーブルのマージ
 グルーブ内の最小・最大
 グルーブ内の最小・最大
 有効な最新単価の取得

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
6.マクロって何？VBAって何？｜VBA入門
7.並べ替え（Sort）｜VBA入門
8.エクセルVBAでのシート指定方法｜VBA技術解説
9.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
10.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門

ホーム
その他
PowerQuery（M言語）入門
別ブックの最終シートの取り込み

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

Power Query（M言語）入門
別ブックの最終シートの取り込み