複数列の区切り文字で行分割しつつ、テーブルにする案｜Power Query

区切り文字によるセル分割は列の分割メニューなら、簡単にできます。
ですが、そういうセルが複数列にわたる場合、列の分割操作では、上手くテーブルになりません。そこで、自分なりの解答を書き残しておきます。

※この問題は[Ken Pulsさんが出題されたもの](https://www.excelguru.ca/blog/2019/07/31/power-query-challenge-6/)です。
Ken Pulsさんは「[M Is for (Data) Monkey]
(https://www.amazon.co.jp/Data-Monkey-Guide-Language-English-ebook/dp/B017QL64A0/ref=tmm_kin_swatch_0?_encoding=UTF8&qid=1565535176&sr=8-1)」の著者のお一人です。

###設問
こんな具合で、左から右に変換したい。以下、区切り文字は改行文字だけである場合で解説します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/ecebd2d9-f04b-88a9-f314-7862885aa322.png)
※下記コードを詳細エディタに貼ると、青い方のテーブルと同じものができます。このクエリを設問という名前にしてあるものとします。

```pq:設問
let
    Source = "i65W8swry89MTvV0UbIyNDI20VHyLEnNBfGUDA0MDWNKDQwMEg0NjKAsIwMjIyUdpcDSxLySzJJKkCqoBIQyBkoGFAHNA8pY6lmawvTDmeZAllKtDrq9pljtxbQNao0hVmtwmG2GYrYB3E8G2H1igmS2oQnccFPshpsjDAeZjWygIdwcsDGYei1Q9Bph12sG0hoLAA==",
    Custom1 = Binary.Decompress(Binary.FromText(Source),Compression.Deflate),
    #"Imported JSON" = Table.FromRecords(Json.Document(Custom1,932))
in
    #"Imported JSON"
```


###解答1～ハードコーディング
設問のテーブルと同じ列数、列名、順序でないと、動かないです。
でも、まずはここから始まるということでいいと思うんです。

####コード

```pq:解答例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の箇所
区切り文字があるところもないところも、それぞれ、結果がリストになっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/8ddac05d-23c8-75bf-b5f7-99954116c668.png)

#####EachRowToTableの箇所
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/f84e5f54-7ac1-17ef-8bf8-a36ccf466ab5.png)

####関数について
・[Splitter.SplitTextByDelimiter関数](https://docs.microsoft.com/en-us/powerquery-m/splitter-splittextbydelimiter)
Splitter関数群のひとつで、1種の区切り文字を複数回使う場合に使用します。
他にも、複数の区切り文字を何回も使う場合はSplitter.SplitTextByAnyDelimiterが、
1回しか使わない場合はSplitter.SplitTextByEachDelimiterがあります。
・[Table.TransformRows関数](https://docs.microsoft.com/en-us/powerquery-m/table-transformrows)
操作感はTable.AddColumnに近いですね。
・[Table.FromColumns関数](https://docs.microsoft.com/en-us/powerquery-m/table-fromcolumns)
各列の内容をリストで表し、それをリストにまとめたものが引数になります。


###解答2～データ部分の列名・列数の変動に対応
####前提
- 1列目は区切り文字による分割が不要であること。
- 2列目以降に区切り文字による分割対象であること。
- 2列目以降に入っている区切り文字の個数は、行で見た場合に各列で同じであること。

####コード
関数にしてみました。

```pq:fx_複数列分割
(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とし、それで実行してみます。

```pq:設問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"
```
```pq:実行その2
= fx_複数列分割(設問2, "InvoiceID", "/")
```

このように、無事変換されています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/1b72209c-2287-e04b-1e4b-1b54a538fc8e.png)
####補足：fx_paring
ただのカスタム関数です。
例えば、type textを引数にして実行すると、こうなります。（分かりやすいよう、テーブルに変換しました）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/2c4fad7b-b129-7c76-1795-be58b22ed215.png)