別ブックのシートを列可変で取り込む
「パワク1本目」
Power Query(M言語)の問題と解答・解説
ツイッターで問題を出して、それに解答と解説をしていく形でPower QueryとM言語の理解を深めていきます。
パワク1本目

別ブックの特定シートをパワクで取り込んでください。
ブックのパスとシート名は任意(定数値指定でよい)
取り込むシートの列数や列タイトルは可変。
つまり、ブック・シートだけ指定されている内容不明の表を取り込むという事。
#パワク #PowerQuery
「詳細エディター」のキャプチャ画像で回答してください。
画像「ALT」にテキスト貼っていただけるとよりありがたいです。
※パス等で出したくない部分があれば消してください。
解答コード

let
ソース = Excel.Workbook(File.Contents("D:\パワク練習\マスタ\商品マスタ.xlsx"), true, true),
シート = ソース{[Item="Sheet1",Kind="Sheet"]}[Data]
in
シート
パワークエリーのGUI操作
リボンの「データ」→「データの取得」→「ファイルから」→「Excel ブックから」



このまま「読み込み」をクリックすれば取り込まれますが、
今回はクエリを編集したいので、「データの変換」で先に進みます。

一行ずつ確かめるのは面倒なので、全てのステップまとめてみてみます。
左上のアイコン「詳細エディタ」をクリックします。

このようになっています。
「適用したステップ」で×で削除すればそのステップは削除されます。
let式
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
変更された型
今回は列を可変にするので、どうもこれが不要のようです。
とりあえず、この列定義を消してみましょう。

ここはいったんキャンセルして、「適用したステップ」の「変更された型」を×で消してみましょう。



目的のテータがシートに出力されました。

出力されたテーブルをクリックした時に、右側に出る「クエリと接続」のクエリをダブルクリックすることで開くことができます。
また、

最近のアップデータで、ショートカットが割り当てられました。
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
昇格されたヘッダー数
テキストに変換できない値については、既定の列名が使用されます。
「適用したステップ」の「昇格されたヘッダー数」削除してみましょう。

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
昇格されたヘッダー数
, null, true)
これはなんでしょうか?
delayTypesには、null、返される各テーブルの列を型指定しないようにするかどうかを指定する論理値 (true または false) を指定できます。 既定値は false です。
既定値はfalse、つまり、nullの指定はfalseということなのか・・・
では、これにtrueを指定すれば、「昇格されたヘッダー数」のステップも不要なのでは・・・
let
ソース = Excel.Workbook(File.Contents("D:\パワク練習\マスタ\商品マスタ.xlsx"), true, true),
Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data]
in
Sheet1_Sheet
名前を少し分かりやすくして、最初の解答コードができました。
{[Item="Sheet1",Kind="Sheet"]}
このKindも無くても良い事になります。
同じテーマ「PowerQuery(M言語)入門」の記事
別ブックのシートを列可変で取り込む
セル値でパス・ブック・シート名を指定
別ブックの最終シートの取り込み
列数不定のCSVの取り込み
CSVのA列が日付の行だけを取り込む
A列のヘッダー名を変更する
「売上」が数値の行のみ取り込む
2つのテーブルのマージ
グルーブ内の最小・最大
グルーブ内の最小・最大
有効な最新単価の取得
新着記事NEW ・・・新着記事一覧を見る
列全体を指定する時のRangeとColumnsの違い|ツイッター出題回答 (2023-09-24)
シートのActiveXチェックボックスの指定方法|ツイッター出題回答 (2023-09-24)
ByRef引数の型が一致しません。|ツイッター出題回答 (2023-09-22)
シートコピー後のアクティブシートは何か|ツイッター出題回答 (2023-09-19)
Excel関数の引数を省略した場合について|ツイッター出題回答 (2023-09-14)
セル個数を返すRange.CountLargeプロパティとは|VBA技術解説(2023-09-08)
記号を繰り返してグラフ作成(10単位で折り返す)|ツイッター出題回答 (2023-08-28)
シートを削除:不定数のシート名に対応|VBAサンプル集(2023-08-24)
ランクによりボイントを付ける(同順位はポイントを分割)|ツイッター出題回答 (2023-08-22)
OneDrive使用時のThisWorkbook.Pathの扱い方|VBA技術解説(2023-07-26)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.マクロとは?VBAとは?VBAでできること|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.並べ替え(Sort)|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.条件分岐(IF)|VBA入門
- ホーム
- その他
- PowerQuery(M言語)入門
- 別ブックのシートを列可変で取り込む
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。