区切り文字によるセル分割は列の分割メニューなら、簡単にできます。
ですが、そういうセルが複数列にわたる場合、列の分割操作では、上手くテーブルになりません。そこで、自分なりの解答を書き残しておきます。
※この問題はKen Pulsさんが出題されたものです。
Ken Pulsさんは「M Is for (Data) Monkey」の著者のお一人です。
設問
こんな具合で、左から右に変換したい。以下、区切り文字は改行文字だけである場合で解説します。
※下記コードを詳細エディタに貼ると、青い方のテーブルと同じものができます。このクエリを設問という名前にしてあるものとします。
let
Source = "i65W8swry89MTvV0UbIyNDI20VHyLEnNBfGUDA0MDWNKDQwMEg0NjKAsIwMjIyUdpcDSxLySzJJKkCqoBIQyBkoGFAHNA8pY6lmawvTDmeZAllKtDrq9pljtxbQNao0hVmtwmG2GYrYB3E8G2H1igmS2oQnccFPshpsjDAeZjWygIdwcsDGYei1Q9Bph12sG0hoLAA==",
Custom1 = Binary.Decompress(Binary.FromText(Source),Compression.Deflate),
#"Imported JSON" = Table.FromRecords(Json.Document(Custom1,932))
in
#"Imported JSON"
解答1～ハードコーディング
設問のテーブルと同じ列数、列名、順序でないと、動かないです。
でも、まずはここから始まるということでいいと思うんです。
コード
let
Source = 設問,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceID", Int64.Type}, {"ItemID", type text}, {"Quantity", type text}, {"Price", type text}}),
ColumnNames =Table.ColumnNames(Source),
SplitByDelimiter = Table.TransformColumns(#"Changed Type",{
{"ItemID",each Splitter.SplitTextByDelimiter("#(lf)")(_)},
{"Quantity",each Splitter.SplitTextByDelimiter("#(lf)")(_)},
{"Price",each Splitter.SplitTextByDelimiter("#(lf)")(_)}
}
),
EachRowToTable =Table.TransformRows(SplitByDelimiter,each
Table.FromColumns({{[InvoiceID]},[ItemID],[Quantity],[Price]},ColumnNames)
),
CombineTables = Table.Combine(EachRowToTable),
#"Filled Down" = Table.FillDown(CombineTables,{"InvoiceID"})
in
#"Filled Down"
処理の途中経過
SplitByDelimiterの箇所
区切り文字があるところもないところも、それぞれ、結果がリストになっています。
EachRowToTableの箇所
関数について
・Splitter.SplitTextByDelimiter関数
Splitter関数群のひとつで、1種の区切り文字を複数回使う場合に使用します。
他にも、複数の区切り文字を何回も使う場合はSplitter.SplitTextByAnyDelimiterが、
1回しか使わない場合はSplitter.SplitTextByEachDelimiterがあります。
・Table.TransformRows関数
操作感はTable.AddColumnに近いですね。
・Table.FromColumns関数
各列の内容をリストで表し、それをリストにまとめたものが引数になります。
解答2～データ部分の列名・列数の変動に対応
前提
- 1列目は区切り文字による分割が不要であること。
- 2列目以降に区切り文字による分割対象であること。
- 2列目以降に入っている区切り文字の個数は、行で見た場合に各列で同じであること。
コード
関数にしてみました。
(TBL as table,TitleColumn as text,delimiter as text)=>
let
ColumnNames = Table.ColumnNames(TBL),
NumOfColumns =Table.ColumnCount(TBL),
//データ列をまとめて処理するための関数を用意する。各列名と相手方をペアにしたリストができる。
fx_paring =(OtherParty)=>List.Zip({List.Skip(ColumnNames,1),
List.Repeat({OtherParty},NumOfColumns-1)}
),
//テキスト型を保証しないと、Split関数が機能しないため。
#"Changed Type" = Table.TransformColumnTypes( TBL,fx_paring(type text) ),
Custom1 = Table.TransformColumns(#"Changed Type",
//Split関数をペアにしたリスト
fx_paring((x)=>Splitter.SplitTextByDelimiter(delimiter)(x))
),
Custom2 = Table.Combine(
//テーブルの各行ごとに処理をし、その結果のリストが出来上がる。
Table.TransformRows(Custom1,each
//1行目は中身がリストでないので、リストに入れてやる。
Table.FromColumns({{Record.ToList(_){0}}} & List.Skip(Record.ToList(_),1),
ColumnNames
)
)
),
#"Filled Down" = Table.FillDown(Custom2,{TitleColumn})
in
#"Filled Down"
実行例
こんな具合で詳細エディタの数式バーに入れてもらえれば、実行できます。
= fx_複数列分割(設問, "InvoiceID", "#(lf)")
実行例2
改行じゃなく、列が増えた例を設問2とし、それで実行してみます。
let
Source = "jY+xCsIwEIbf5eZicmnSWjerSzZdXIxDkQ6FmoIkgojv7iWDto2Cyx33H3wf//EB2t6G7tzqLaxQ5DID7dpLuAA5omHIBU3BhYAM9r6xrnP38KXQsJzCQ9P7xnWDpXRtWG3YhtLdlaiUVItKBUpcJU14ZnOrSqypj2Q4k5GpTkw/FMVEwWMxnlaSaZ9xGZTRob47yo8jKMZsnGCt7/s3NDJT2HICE3/CisA6vQA=",
Custom1 = Binary.Decompress(Binary.FromText(Source),Compression.Deflate),
#"Imported JSON" = Table.FromRecords(Json.Document(Custom1,932))
in
#"Imported JSON"
= fx_複数列分割(設問2, "InvoiceID", "/")
このように、無事変換されています。
補足：fx_paring
ただのカスタム関数です。
例えば、type textを引数にして実行すると、こうなります。（分かりやすいよう、テーブルに変換しました）
