仕事柄、会計システムに流す仕訳のCSVが、処理の終着点ということが多いです。
個人的にはPower Queryで済ませるのが事案ごとのカスタマイズがしやすく、好きです。
##やりたいこと
下記の画像の通りです。行ごとに貸借を打たず、借方は集計値で仕訳したいというようなケースです。
※実際には、その会計システムごとの列定義があるわけですが、ここでは適当にそれっぽいものを創作しました。
##サンプルデータ
下のコードをPower Query詳細エディタにコピペして、それぞれ名前を付けて別クエリとして作成します。
上図の「何らかのデータ」と「仕訳の列定義」と同じデータができます。
let
Source = "lZRNjsMgDIXvwroj2WDzk0PMBZpZpEnYdpXVqHcfm66mBESk6Cnix3x6PHP/NfPheef5oH2LZjIWLH6BfN7cZI5zCrICIcs/Uvx+msmWCVpRFTbZNB8OAqvGRZUeqoylhOcA85FwzWYKZavjKOflBc1EDnQo4SbVUiSp5gDhdWuDpYtgfn+DVTDxE8ZhBWMp9mDQNmHwFIbEn+icl9XZrg2XKjCkCgzRdsG4CeZOwZZNdQfVdQSJayQLXaRriYorqUshZD1loQqJB64PmbtI7Sx1XXKqWypeDYCFGsz1wWw7V+chpwIWSw+qb2OBT3X3BaIuGF10bOhZ8J9gkqSL6bLtdJ2DNZ8FV+XqpPug2332aq6GnoV3iv7dX00mQ/D6+QM=",
Custom1 = Binary.FromText(Source),
Custom2 = Binary.Decompress(Custom1,Compression.Deflate),
Custom3 = Json.Document(Custom2),
Custom4 = Table.FromRecords( Custom3 ),
#"Changed Type" = Table.TransformColumnTypes(Custom4,{{"日付", type date}, {"得意先No", Int64.Type}, {"品名", type text}, {"数量", Int64.Type}, {"単価", Int64.Type}, {"金額", Int64.Type}})
in
#"Changed Type"
let
Source = "jZGxDsMgDET/hTkDDhjsrnSv1I5NhwTIHzBV/feaTkiFKMtJWHcPn/x8q6XgDF7U6qQu8jQ6UdVofqrVJENaQYZodi+ex+2uNajP1Ek7zLgUmxMNcnM/hxr2mt54KZ4TiLqc/xnX8dctgijFali5jzixBUPdgsmmPsL0ERQ3OlMkjIu0iKMiYVykRRwVCeMizqL4iAkGl5Tc6ws=",
Custom1 = Binary.FromText(Source),
Custom2 = Binary.Decompress(Custom1,Compression.Deflate),
Custom3 = Json.Document(Custom2),
Custom4 = Table.FromRecords( Custom3 ),
#"Changed Type" = Table.TransformColumnTypes(Custom4,{{"列名", type text}, {"記号", type text}})
in
#"Changed Type"
##コード
let
Source = t_売上,
仕訳日 = Date.EndOfMonth(Source[日付]{0}),
//やりやすい方法で
摘要入れ = Table.AddColumn(Source, "摘要", each Text.Format("#[品名]@#[単価]x#[数量]．",_) & Date.ToText([日付],"M/d"),Text.Type),
#"Removed Columns" = Table.RemoveColumns(摘要入れ,{"品名", "数量", "単価","日付"}),
貸方科目類入れ = Table.AddColumn(#"Removed Columns", "科目セット", each [貸方科目=500,貸方補助=0]),
#"Expanded {0}" = Table.ExpandRecordColumn(貸方科目類入れ, "科目セット", {"貸方科目", "貸方補助"}, {"貸方科目", "貸方補助"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded {0}",{{"金額", "貸方金額"}}),
得意先別まとめ = Table.Group(#"Renamed Columns", {"得意先No"}, {{"借方金額", each List.Sum([貸方金額]), type nullable number},{"tbl",each _}}),
#"Sorted Rows" = Table.Sort(得意先別まとめ,{{"得意先No", Order.Ascending}}),
借方科目入れ = Table.AddColumn(#"Sorted Rows", "借方科目", each 135),
#"Renamed Columns1" = Table.RenameColumns(借方科目入れ,{{"得意先No", "借方補助"}}),
//ポイント1
fx_接着 =(つける物 as record,BaseTbl as table)=>[
最初の行_rec = つける物 & BaseTbl{0},
最初の行_tbl = Table.FromRecords({最初の行_rec}),
その後の行 = Table.RemoveFirstN(BaseTbl,1),
result =Table.Combine({最初の行_tbl,その後の行})
],
貸借の接着 = Table.TransformRows(#"Renamed Columns1",each
fx_接着(
Record.RemoveFields(_,{"tbl"}),
[tbl]
)[result]
),
debug_貸借の接着 = Table.TransformRows(#"Renamed Columns1",each fx_接着(Record.RemoveFields(_,{"tbl"}),[tbl]) ){1},
単純合体 =Table.Combine(貸借の接着),
ヘッダの接着 = fx_接着([ヘッダ="*",日付=仕訳日],単純合体)[result],
debug_ヘッダの接着 = fx_接着([ヘッダ="*",日付=仕訳日],単純合体),
//ポイント2
できあがり確認 = Table.SelectColumns( ヘッダの接着,t_列定義[列名] ),
//数値だとCSV出力に不向きの場合があるので、よくやる。
#"Changed Type1" = Table.TransformColumnTypes(できあがり確認,{{"日付", type text}}),
//出来上がりに問題なければ最終的にはCSV用ヘッダ置換後で出力する
置換後 =Table.RenameColumns(#"Changed Type1",Table.ToRows(t_列定義)),
完成=#"Changed Type1"
in
完成
##若干の解説
###ポイント1
繰り返し同じことを書いてもいいんですが、カスタム関数を定義して処理してます。
模索しながら書く場合、record記法で書き、途中経過を覗けるようにしていくとやりやすいかなと思っています。
ここでは、全処理が終わったものを「result」と名付けています。この名称や書いた順番は処理に関係ないです。
↓関数結果がrecordとして出る様子
###ポイント2
一通り処理した（と思った）箇所で、列の並び替え処理を入れてやると、処理し忘れた列があったり、表記誤りがあったら、列の並び替えの箇所でエラーが出るので、確認することができます。
##参考
recordについて
https://docs.microsoft.com/ja-jp/powerquery-m/expressions-values-and-let-expression#record
Text.Format関数
https://docs.microsoft.com/ja-jp/powerquery-m/text-format
Table.TransformRows関数
https://docs.microsoft.com/ja-jp/powerquery-m/table-transformrows
##実施環境
Excel365（バージョン2101）
Power Query 2.88.201.0 64ビット
※特に新しい関数を使っていないので、Excel2010・2013のアドイン版でも同じことができるだろうとは思います。