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

Power Query(パワークエリ)とM言語を練習問題を通して学んでいきます
公開日:2023-02-08 最終更新日:2023-02-09

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


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


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

パワク3本目

エクセル Excel Power Query M言語

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


解答コード

エクセル Excel Power Query M言語
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 Power Query M言語

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

エクセル Excel Power Query M言語

シート数ではなく、最終のシート名を取得しても良いですね。
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は、シート・テーブル・名前定義の一覧になりますので、ここからシートに絞り込みが必要です。

エクセル Excel Power Query M言語
let
    ソース = Excel.Workbook(File.Contents("D:\パワク練習\マスタ\商品マスタ.xlsx"), true, true),
    フィルター = Table.SelectRows(ソース, each ([Kind] = "Sheet")),
    シート数 = Table.RowCount(フィルター),
    シート = フィルター{シート数-1}[Data]
in
    シート
eachについては、今後の出題回で説明していきます。


GUI操作での作成

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

エクセル Excel Power Query M言語

エクセル Excel Power Query M言語

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

エクセル Excel Power Query M言語

エクセル Excel Power Query M言語

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

エクセル Excel Power Query M言語

左右に開いたアイコンエクセル Excel サンプル画像をクリックします。

エクセル Excel Power Query M言語

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

エクセル Excel Power Query M言語

こうしてできたクエリが以下になります。
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言語)入門」の記事

別ブックのシートを列可変で取り込む

1本目 別ブックの特定シートをパワクで取り込んでください。ブックのパスとシート名は任意(定数値指定でよい) 取り込むシートの列数や列タイトルは可変。つまり、ブック・シートだけ指定されている内容不明の表を取り込むという事。
セル値でパス・ブック・シート名を指定
2本目 別ブックの特定シートをパワクで取り込んでください。テーブル"tbl設定"に、パス・ブック名・シート名が入っています。この"tbl設定"の情報を元に取り込んでください。
別ブックの最終シートの取り込み
列数不定のCSVの取り込み
4本目 列数不定のCSVをパワクで取り込んでください。1行目をヘッダーして使用してください。※バス・ファイル.名の指定は任意 ※文字コードはUTF8(BOMなし) ※テストデータはメモ帳等で作成してください。
CSVのA列が日付の行だけを取り込む
5本目 列数不定のCSVのA列が日付の行だけをパワクで取り込んでください。4本目の続きです。4本目にA列日付で抽出するステッブを追加してください。A列は日付型で取り込んでください。A列の列名は任意。#PowerQuery#M言語 解答コード let ソース=Csv.Document(File.Contents(&qu…
A列のヘッダー名を変更する
6本目 CSVをパワクで取り込みます。5本目の続きです。A列のヘッダーは本来は「日付」が正しいのですが、たまに「日付け」となっている場合もあるようです。※さらに他表記の場合もある。A列のヘッダを「日付」に統一して出力するようにしてください。
「売上」が数値の行のみ取り込む
7本目 CSVをパワクで取り込みます。列「売上」が非数値および空の行を削除して、「売上」が数値の行のみ取り込んでください。#PowerQuery#M言語 解答コード let ソース=Csv.Document(File.Contents("D:\パワク練習\CSV\test2.csv"),
2つのテーブルのマージ
8本目 ブック内に、「tbl個人」「tbl都道府県」があります。この2つのテーブルを結合して、添付サンブルのように出力してください。※「tbl個人」の都道府県には「県」の文字が入っていません。出力は「県」の入った県名で。
グルーブ内の最小・最大
9本目 ブック内に日付と商品コードを持つテーブル「tbl伝票」があります。商品コード毎の日付の最小値と最大値(最古日付と最新日付)を出力してください。※サンブルデータはALT #PowerQuery#M言語 解答コード let ソース=Excel.CurrentWorkbook(){[Name="tbl伝票…
グルーブ内の最小・最大
9本目 ブック内に日付と商品コードを持つテーブル「tbl伝票」があります。商品コード毎の日付の最小値と最大値(最古日付と最新日付)を出力してください。※サンブルデータはALT #PowerQuery#M言語 解答コード let ソース=Excel.CurrentWorkbook(){[Name="tbl伝票…
有効な最新単価の取得
10本目 「tbl伝票」(日付,コード) 「tblマスタ」(コード,日付,単価) マスタの日付は(これ以降有効となる)適用開始日です。「tbl伝票」に単価を付して出力してください。※例.2/5のA01はマスタの2/1が適用されます。


新着記事NEW ・・・新着記事一覧を見る

エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.メッセージボックス(MsgBox関数)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.ひらがな⇔カタカナの変換|エクセル基本操作




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


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


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