【Power Query M式言語】Excelワークシートのデータを取得する【Excel.Workbook関数】

Power Query M式言語でExcelワークシートのデータを取得する方法の解説です。

Power Queryを使えば、VBAを使うことなく、簡単に他ブックのExcelワークシートのデータを取得することができます。

M式の記述方法を知っていれば、Excelワークシート間の連携も簡単に記述することができます。

環境

Windows10
Excel 2019

前提

取得するExcelワークシートのデータは、下記のように最初の行がヘッダーとなっていることが前提です。

Excel.Workbook関数の構文

M式言語のExcel.Workbook関数を使います。

Excel.Workbook関数の構文は次の通りです。

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

第一引数のworkbookは対象のExcelファイルのバイナリ(生データ)になります。これを得るためには、File.Contents関数という別の関数を使うことで取得することができます。

第二引数のuseHeadersは対象のワークシートの最初の行をヘッダーとみなすかどうかを真偽値で指定します。既定ではfalse、つまり最初の行をヘッダーと見なさないようになっています。optionalが示す通り、この引数は指定してもしなくても大丈夫です。最初の行をヘッダーと見なす場合は必ず指定します。

第三引数のdelayTypesは型を自動判定しないようにするかどうかの指定で、やはり真偽値で指定します。既定ではfalse、つまり自動判定するようになっています。この引数もオプションです。

【余談です】第二引数のuseHeadersについて

完全に余談になります。一応触れておきますが、読み飛ばし推奨です。

useHeadersは真偽値と書いてありますが、なんとレコードを指定することもでます。

レコードについては→【Power Query M式言語】レコードについての解説 - 適材適所

その場合はのレコードはUseHeaders、DelayTypesに加えて、InferSheetDimensionsという真偽値を設定できるようですが、通常のExcelファイルでは使用できないようです。Excelワークシートを取得するときは、このことについてはあまり気にする必要はなさそうです。Ｍ式では通常の引数とは別にレコードを指定して挙動を少し変えるという不思議な仕組みがあるようです。下記にリンクを貼っておきますが、仕様を読んでいると、このように引数にレコードを指定して挙動を少し変えるパターンが散見されます。

参考:

Excel.Workbook - PowerQuery M | Microsoft Docs

Excel.Workbook関数を使ってExcelワークシートのデータを取得する

さて、では実際にワークシートのデータを取得する手順になります。

Excel.Workbook関数とFile.Contents関数を組み合わせることで、Excelワークシートのデータを取得することができます。

下記に例を示します。

File.Contents関数の引数であるファイルのパス("C:\〇〇.xlsx"のところ)は絶対パスで指定します。また、取得したいシートを"シート名"のところに記載します。

 

Excel.Workbook(File.Contents("C:\〇〇.xlsx"),true){[Item="シート名"]}[Data]

結果

ファイルのパスさえあっていれば、自ブックでも他のブックでも取得することができます。

多くの場合、これで取得したデータに対して様々な加工を加え、１つのクエリを作り上げることと思います。

その場合は下記のようにlet式を使って読み込んだ結果を１つのステップとして変数に入れてあげましょう。

 

let
   Excelの読込み= Excel.Workbook(File.Contents("C:\〇〇.xlsx"),true){[Item="シート名"]}[Data]
in
   Excelの読込み

let式については↓

www.tekizai.net

【おまけ】useHeadersにレコードを書く場合

通常、Excelワークシートからデータを取得するときは使いませんが、第二引数にレコードを書く書き方も載せておきます。

 

Excel.Workbook(File.Contents("C:\〇〇.xlsx"),[UseHeaders=true,DelayTypes=false]){[Item="シート名"]}[Data]

終わりに

Power Query M数式言語のExcel.Workbook関数を使ってExcelワークシートのデータを取得する方法について解説しました。

これは汎用性が高そうな処理なのでぜひ覚えて帰ってくださいね～。

今までVBAでやっていたことをM式に置き換えられるかも・・・？

というわけでここまでお読みいただき、ありがとうございました。