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

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

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


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


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

パワク1本目

Power Query M言語

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


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


解答コード

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


パワークエリーのGUI操作

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

PowerQuery M言語 参考画像

PowerQuery M言語 参考画像

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

PowerQuery M言語 参考画像

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

PowerQuery M言語 参考画像

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


PowerQuery M言語 参考画像

ソース
ソース = 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言語 参考画像

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

PowerQuery M言語 参考画像

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

PowerQuery M言語 参考画像

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

PowerQuery M言語 参考画像

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

PowerQuery M言語 参考画像


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

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

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

PowerQuery M言語 参考画像


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

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

PowerQuery M言語 参考画像

なるほど、ヘッダーとして、
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

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つのテーブルのマージ
グルーブ内の最小・最大
グルーブ内の最小・最大
有効な最新単価の取得


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

テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)
イータ縮小ラムダ(eta reduced lambda)|エクセル入門(2023-11-20)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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