Power Query - テーブル化せずにシートからデータを読み込む方法

概要

ワークシートのセル範囲をクエリ(PowerQuery)にてデータを取得する場合、取得するセル範囲を選択した状態で、データメニューから「データの取得と変換 - テーブルまたは範囲から」を実行すると、セル範囲をテーブル化してからクエリに取得されます。

この記事では、元のセル範囲をテーブル化せずにデータ取得する方法を考えます。
手順としては、セル範囲に対して名前を定義してから、その名前を使ってクエリにてデータを取得させる方法となります。

名前の管理

セル範囲を参照する名前を作成するため、数式メニューから「名前の管理」を開きます。

「新規作成」ボタンを押してクエリでインポートしたい範囲を選択して名前を作成します。

データの取得

ワークシート左上の「名前ボックス」に、上記で作成した名前「Sheet1_A_C」を入力してEnterキーを押すと、参照範囲が選択された状態になります。
この状態で、データメニューから「データの取得と変換 - テーブルまたは範囲から」を実行します。

Power Query エディタが立ち上がってクエリが自動作成されますが、選択範囲に名前がついている場合は、元のセル範囲はテーブル化せずに読み込むことができます。

Sheet1_A_C

let
    ソース = Excel.CurrentWorkbook(){[Name="Sheet1_A_C"]}[Content],
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}})
in
    変更された型

データ範囲を抽出するクエリ

名前の参照範囲を列全体に設定したので、データ入力の無い行が大量に読み込まれてしまいます。
そこでデータが入力された最後の行位置を求めて、その行までを保持するクエリを作成します。

Sheet1_Trim

let
    ソース = Sheet1_A_C,
    EmptyRowCheck = Table.AddColumn(ソース, "EmptyRow", each List.NonNullCount(Record.FieldValues(_)) = 0),
    追加されたインデックス = Table.AddIndexColumn(EmptyRowCheck, "インデックス", 1, 1, Int64.Type),
    NonEmptyRows = Table.SelectRows(追加されたインデックス, each [EmptyRow] = false),
    MaxNonEmptyIndex = List.Max(NonEmptyRows[インデックス]),
    保存された先頭行 = Table.FirstN(ソース, MaxNonEmptyIndex)
in
    保存された先頭行

EmptyRow 列を追加して、同じ行の他の列の値がすべて null かそうでないかを演算します。

インデックス列を追加したあと、EmptyRow でフィルターして、インデックスの最大値として MaxNonEmptyIndex を求めます。

テーブルの先頭から MaxNonEmptyIndex までの行を保持します。

データ範囲を抽出する数式

名前付き範囲の数式にて、セル範囲を求める事もできます。
TAKE関数の第一引数には入力する列範囲、第二引数には最大で読み込む行数を処理負荷や入力データ数と相談して決定します。

Sheet2_A_C

=LET(A, TAKE(Sheet2!$A:$C, 65535), height, AGGREGATE(14,6,ROW(A)/(A<>""),1), OFFSET(A,,, height))

この方法で作成した数式の場合「名前ボックス」に名前を入力してもセル範囲を取得できないので、最初からPower Queryエディタを使って直接クエリを指定する必要があります。
とりあえず今回はSheet1_A_Cクエリを複製して、下記の通り作成しました。

Sheet2_A_C

let
    ソース = Excel.CurrentWorkbook(){[Name="Sheet2_A_C"]}[Content],
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}})
in
    変更された型

MAX関数を使った方法

AGGREGATE関数の代わりにMAX関数使って書くと下記の数式で抽出することもできます。

=LET(A, TAKE(Sheet2!$A:$C, 65535), height, MAX(ROW(A)*(A<>""),1), OFFSET(A,,, height))

ただしMAX関数を使った数式だと、入力範囲にエラー値が紛れ込むと数式全体がエラーとなってしまいます。
一方AGGREGATE関数を使った数式では、一箇所でも有効な値が入力されていればエラーも含めて抽出することができます。ただし末尾の行にエラーしか存在しない場合は除外されます。

スピル範囲からデータの取得

スピルを使ってデータが入力されてる場合は、その範囲を選択状態にした状態で「データの取得と変換 - テーブルまたは範囲から」を実行することでクエリを作成することができます。

FormArray_1

let
    ソース = Excel.CurrentWorkbook(){[Name="FromArray_1"]}[Content],
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}})
in
    変更された型

スピル範囲を選択した場合は、セル範囲をテーブル化せずに、名前「FormArray_1」が自動で登録されます。