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

公開日：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.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

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

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

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

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