これもまたExcelでしか関係ない話かなと。
2019/8/6追記：解説を追加しました。
元のテーブル
テーブル1という名前にしてます。
※テーブルの生成はこのコードで可能です。
テーブル1
let
Source = "ndMxDsMgDAXQuzAnkg02mFyl6RACuUGmKncv7WpKFRaEsPRkf5nHy6yn58LrSSWLWYwFjDP4GdBMtcYotcYpxVr7XBi+7xEz1lMomyWywDXdlKggKsm7eF/itLOSCPo92abk/KGnA7wvyZF0TuLDQE/5cFr6k9OP6UiU5Gxfci3JB7spKQxAgZ0ezlE/8KYUkaKW/MBsFna9TWFAorLpvRQZiKn56wgIrucb",
Custom1 = Binary.Decompress(Binary.FromText(Source),Compression.Deflate),
Custom3 = Json.Document(Custom1),
Custom4 = Table.FromRecords(Custom3)
in
Custom4
変換後のテーブル
元のテーブルを5行ずつの明細にし、少ない場合は穴埋めをします。
コード
let
Source = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"日付", type date}, {"金額", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"日付"}, {{"tbl", each _, type table [日付=date, 金額=number]}}),
#"5行ごとで分割" = Table.TransformColumns(#"Grouped Rows",{"tbl",each
if Table.RowCount(_)<=5 then {_}
else List.Transform(
List.Split(Table.ToRows(_),5),
(x)=>Table.FromRows(x,Table.ColumnNames(_))
)}
),
#"Removed Columns" = Table.RemoveColumns(#"5行ごとで分割",{"日付"}),
ページ単位に = Table.ExpandListColumn(#"Removed Columns", "tbl"),
各ページ5行にする = Table.TransformColumns(ページ単位に,{"tbl",each
Table.FromColumns(Table.ToColumns(_)& {List.Repeat({null},5)},
Table.ColumnNames(_)&{"余り"}
)}
),
#"Added Index" = Table.AddIndexColumn(各ページ5行にする, "ページ", 1, 1),
#"Expanded {0}1" = Table.ExpandTableColumn(#"Added Index", "tbl", {"日付", "内容", "金額"}, {"日付", "内容", "金額"}),
#"Added Custom" = Table.AddColumn(#"Expanded {0}1", "総ページ", each List.Max(#"Expanded {0}1"[ページ])),
出力用テキストに変換 = Table.TransformColumns(#"Added Custom",{
{"日付",each if _=null then null else Date.ToText(_,"yyyy年M月d日"),type text},
{"金額",each if _=null then null else Number.ToText(_,"#,##0"),type text}
}
)
in
出力用テキストに変換
おまけ：テーブルをn行にする手法
上記のコードの方法
Table.FromColumns関数は、一番行数が多い列に合わせてテーブルが作られ、行数が少なかった列にはnullが入ります。
この仕組みを利用して、5個のnullが入った列をつけてやれば、強制的に5行のテーブルになるというわけです。
例えば、下記のコードを詳細エディタに貼って実行してみると、
= Table.FromColumns({{1..6},{1..5},{1..4}})
下図のように一番多い6行にあわせて、他の列にはnullが入ります。
で、これを強制的に10行にするには、Table.ToColumns関数で列にばらしてから、仕掛け列を足し、テーブルに再合成します。
6行から10行へ
let
Source = Table.FromColumns({{1..6},{1..5},{1..4}}),
Custom1 = Table.FromColumns(Table.ToColumns(Source)
&{List.Repeat({null},10)}//←ここが仕掛け列
)
in
Custom1
↓出来上がり図。余分な列ができてしまうのが弱点か。
別解：n行足し、上からn行を取る。
虎の穴にて教えていただいた手法です。
この手法で、上の「6行から10行へ」と同じことをやってみるとこう書けます。
6行から10行へ～別解
let
Source = Table.FromColumns({{1..6},{1..5},{1..4}}),
Custom1 = Table.FirstN( Table.Combine({Source,
#table({"Column1"},List.Repeat({{null}},10))}//←合体させるテーブル
),10
)
in
Custom1
合体させているテーブルの列名を既存のテーブルのどこかと揃えるのがポイントです。
↓出来上がり図。
ちなみに
#table({"Column1"},List.Repeat({{null}},10))
はテーブルを作成する記法の一つで、第1引数は列名のリスト、第2引数はテーブルの中身で、リストのリストで表現します。
なので、これだけで実行すると、下図のようになります。
Microsoft Power Query for Excel Formula Language Specification
(※PDF文書です) の4.13 tableの項には他のテーブル記法も載っています。