列数が少なくて行数が多いデータの場合、そのまま印刷すると余白だらけでページ数がかさむので、適当な行数で横に表示したい場合の話。
INDEX/SEQUENCEだったり、MAKEARRAYだったり、古いバージョンでもINDEX/ROW/COLUMN/MOD/INT辺りを使えば数式でもできる。というか WRAPROWS関数が使えるようになれば、一列なら一瞬で解決するようになる。そもそもでいうなら Wordで レイアウトの段組みを指定した文書を作成して、そこにデータを流し込んだっていいと思う。そっちのほうがページごとに段組みされるので見やすいし。
こんなのをいちいちクエリでやる必要はないんだろうけど、あえてやってみる。元のデータが「列1」「列2」の2列で構成された縦長のデータだとして、それを 10行ごとに区切って横並びにする。
方法1
let
行数 = 10,
元データ =
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
型の変更 = Table.TransformColumnTypes(ソース,
{{"列1", type text}, {"列2", Int64.Type}}
)
in
型の変更,
剰余列の追加 = Table.AddIndexColumn(元データ, "剰余", 0, 1),
剰余を計算 = Table.TransformColumns(
剰余列の追加, {"剰余", each Number.Mod(_, 行数)}
),
行のグループ化 = Table.Group(剰余を計算, {"剰余"}, {"リスト", each
List.Combine(Table.ToRows(Table.SelectColumns(_,{"列1","列2"})))}
),
テーブルの作成 = Table.AddColumn(行のグループ化, "テーブル", each
Table.AddIndexColumn(Table.FromValue([リスト]),"剰余",0,1)
),
列を選択 = Table.SelectColumns(テーブルの作成, {"テーブル"}),
連番列の追加 = Table.AddIndexColumn(列を選択, "連番", 0, 1),
テーブルの展開 = Table.ExpandTableColumn(
連番列の追加, "テーブル", {"Value", "剰余"}
),
型の変換 = Table.TransformColumnTypes(
テーブルの展開,{{"剰余", type text}}
),
列のピボット = Table.Pivot(
型の変換, List.Distinct(型の変換[剰余]), "剰余", "Value"
),
連番列の削除 = Table.RemoveColumns(列のピボット,{"連番"})
in
連番列の削除連番を行数で割った剰余を使って行のグループ化を実行。リスト作成→テーブル変換し、連番列を付けてからテーブルの剰余列を展開。最後に列のピボット。だらだらしてはいるものの、ステップを追って確認しやすいようには思う。
次は、「結合→列の分割」でやった場合。
方法2
let
行数 = 10,
元データ =
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
型の変更 = Table.TransformColumnTypes(ソース,
{{"列1", type text}, {"列2", Int64.Type}}
)
in
型の変更,
連番列の追加 = Table.AddIndexColumn(元データ, "連番", 0, 1),
剰余を計算 = Table.TransformColumns(
連番列の追加, {"連番", each Number.Mod(_, 行数)}
),
行のグループ化 = Table.Group(剰余を計算, {"連番"}, {"列", each
List.Combine(Table.ToRows(Table.SelectColumns(_,{"列1","列2"})))}
),
値の抽出 = Table.TransformColumns(行のグループ化, {"列", each
Text.Combine(List.Transform(_, Text.From), ","), type text}
),
列の分割 = Table.SplitColumn(
値の抽出, "列", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)
),
連番列の削除 = Table.RemoveColumns(列の分割,{"連番"})
in
連番列の削除この後に列ごとのタイプの指定は必要。列が全部文字列型ならこれでもいいのかもしれないけど……あんまりお勧めできない。
更に次。リスト生成でテーブルを作ってみる手もあると思う。
方法3
let
行数 = 10,
元データ =
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
型の変更 = Table.TransformColumnTypes(ソース,
{{"列1", type text}, {"列2", Int64.Type}}
)
in
型の変更,
連番列の追加 = Table.AddIndexColumn(元データ, "連番", 0, 1),
剰余を計算 = Table.TransformColumns(
連番列の追加, {"連番", each Number.Mod(_, 行数)}
),
テーブル分割 = Table.Split(剰余を計算, 行数),
リスト生成 = List.Generate(
()=>[i=0, tbl=Table.FirstN(テーブル分割{0}, 0)],
each [i]<=List.Count(テーブル分割),
each [
i=[i]+1,
tbl = [tbl] & Table.RenameColumns(テーブル分割{i-1},
{
{"列1", Number.ToText(i*2-1, "列0")},
{"列2", Number.ToText(i*2, "列0")}
}
)
],
each [tbl]
),
行のグループ化 = Table.Group(List.Reverse(リスト生成){0}, {"連番"},
{"グループ", each Table.FillUp(_,Record.FieldNames(_{0})){0}}
),
連番列を削除1 = Table.RemoveColumns(行のグループ化,{"連番"}),
グループの展開 = Table.ExpandRecordColumn(連番列を削除1, "グループ",
Record.FieldNames(連番列を削除1[グループ]{0})
),
連番列を削除2 = Table.RemoveColumns(グループの展開,{"連番"})
in
連番列を削除2うーん、これはいかん。せっかく List.Generateを使うならもっと簡略化できるはず。
方法4
let
行単位 = 10,
ブック = Excel.CurrentWorkbook(),
タイプ = type table [列1=text, 列2=number],
元データ = [
ソース = ブック{[Name="テーブル1"]}[Content],
型の変更 = Table.TransformColumnTypes(ソース,
{{"列1", type text}, {"列2", Int64.Type}}
)
][型の変更],
テーブル分割 = Table.Split(元データ, 10),
リスト生成 = List.Generate(
()=>[i=0, x={}],
each [i]<=List.Count(テーブル分割),
each [i=[i]+1, x=[x] & Table.ToColumns(テーブル分割{i-1})],
each [x]
),
見出し = List.Transform(
{1..List.Count(テーブル分割)*2}, each Number.ToText(_, "列0")
),
テーブル変換 = Table.FromColumns(List.Reverse(リスト生成){0}, 見出し)
in
テーブル変換最初からこうしてればよかった……。
