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

公開日：2023-02-06　最終更新日：2023-02-08

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

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

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

ページ内目次

パワク1本目
解答コード
パワークエリーのGUI操作
let式
Table.PromoteHeaders
Excel.Workbook

パワク1本目

#パワク1本目
別ブックの特定シートをパワクで取り込んでください。
ブックのパスとシート名は任意（定数値指定でよい）
取り込むシートの列数や列タイトルは可変。
つまり、ブック・シートだけ指定されている内容不明の表を取り込むという事。
#パワク #PowerQuery

できれば回答は引用RTでお願いします。
「詳細エディター」のキャプチャ画像で回答してください。
画像「ALT」にテキスト貼っていただけるとよりありがたいです。
※パス等で出したくない部分があれば消してください。

解答コード

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

パワークエリーのGUI操作

PowerQueryでExcelブックからの取り込み手順
リボンの「データ」→「データの取得」→「ファイルから」→「Excel ブックから」

ファイルを選択して「インポート」

左側でシートを選択すると、右側にプレビューが表示されます。
このまま「読み込み」をクリックすれば取り込まれますが、
今回はクエリを編集したいので、「データの変換」で先に進みます。

右下の「適用したステップ」て選択した行の内容が上部の数式バーに表示されます。
一行ずつ確かめるのは面倒なので、全てのステップまとめてみてみます。
左上のアイコン「詳細エディタ」をクリックします。

ソース

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

ナビゲーション

Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],

昇格されたヘッダー数

昇格されたヘッダー数 = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),

変更された型

変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"商品ID", type text}, {"商品名称", type text}, {"商品単価", Int64.Type}})

このようになっています。
「適用したステップ」で×で削除すればそのステップは削除されます。

let式

Power QueryのM言語の式は評価 (計算) されて、値を生成することができます。
let式には、計算され名前を割り当てられます。
let内の各式は,（カンマ）でつなげて記述します。
inステートメントに続く後の式で結果が返されます。

let
    名前1 = 式1,
    名前2 = 式2（名前1を使用できる）
in
    名前2

では、最初に作成されたクエリを見てみましょう。

let
    ソース = Excel.Workbook(File.Contents("D:\パワク練習\マスタ\商品マスタ.xlsx"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    昇格されたヘッダー数 = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"商品ID", type text}, {"商品名称", type text}, {"商品単価", Int64.Type}})
in
    変更された型

「変更された型」のステップで列名と型を決めています。
今回は列を可変にするので、どうもこれが不要のようです。
とりあえず、この列定義を消してみましょう。

これはこれで良さそうですが、「変更された型」のステップがそもそも不要と言う事ですので、
ここはいったんキャンセルして、「適用したステップ」の「変更された型」を×で消してみましょう。

「変更された型」消してから「詳細エディター」を見てみると、

これで良さそうですね。「完了」

「閉じて読み込む」
目的のテータがシートに出力されました。

作成済みのクエリを見るには、
出力されたテーブルをクリックした時に、右側に出る「クエリと接続」のクエリをダブルクリックすることで開くことができます。
また、
PowerQuery M言語参考画像

この「Power Query エデッィターの起動」から起動できます。
最近のアップデータで、ショートカットが割り当てられました。
Alt + F12

クエリ名は、クエリの「設定」→「プロパティ」
ここの名前で変更できます。

Table.PromoteHeaders

再度クエリを見てみましょう。

let
    ソース = Excel.Workbook(File.Contents("D:\パワク練習\マスタ\商品マスタ.xlsx"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    昇格されたヘッダー数 = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
    昇格されたヘッダー数

Table.PromoteHeaders、これは先頭行を見出しとして使う為の物のようです。

構文

Table.PromoteHeaders(table as table, optional options as nullable record) as table

※Lean

PromoteAllScalars: true に設定されている場合は、Culture が指定されていればそれを使用して (または現在のドキュメントのロケール)、最初の行のすべてのスカラー値がヘッダーに昇格されます。
テキストに変換できない値については、既定の列名が使用されます。

先頭行を見出しとして使わないのなら、このステップは不要のようです。
「適用したステップ」の「昇格されたヘッダー数」削除してみましょう。

なるほど、ヘッダーとして、
Column1,Column2,・・・
となるようです。
やはりこの「昇格されたヘッダー数」はあったほうが良さそうですね。
（もちろん、時々で不要な場合もあります。）

Excel.Workbook

もう一度クエリを見直してみましょう。

let
    ソース = Excel.Workbook(File.Contents("D:\パワク練習\マスタ\商品マスタ.xlsx"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    昇格されたヘッダー数 = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
    昇格されたヘッダー数

そもそも最初の、Excel.Workbook、これはどのような文法なのでしょうか。
, null, true)
これはなんでしょうか?

構文

Excel.Workbook(workbook as binary, optional useHeaders as any, optional delayTypes as nullable logical) as table

※Lean

useHeadersには、null、返される各テーブルの最初の行をヘッダーとして扱うかどうかを指定する論理値 (true または false)、オプションレコードを指定できます。既定値はfalse です。
delayTypesには、null、返される各テーブルの列を型指定しないようにするかどうかを指定する論理値 (true または false) を指定できます。既定値は false です。

useHeadersの説明が・・・
既定値はfalse、つまり、nullの指定はfalseということなのか・・・
では、これにtrueを指定すれば、「昇格されたヘッダー数」のステップも不要なのでは・・・

let
    ソース = Excel.Workbook(File.Contents("D:\パワク練習\マスタ\商品マスタ.xlsx"), true, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data]
in
    Sheet1_Sheet

これだけで良さそうですね。
名前を少し分かりやすくして、最初の解答コードができました。

さらに、"Sheet1"という名前が1つしかない場合は、
{[Item="Sheet1",Kind="Sheet"]}
このKindも無くても良い事になります。

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

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

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

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

ホーム
その他
PowerQuery（M言語）入門
別ブックのシートを列可変で取り込む

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

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

Power Query（M言語）入門
別ブックのシートを列可変で取り込む