(2019/6/22)Excelファイルの「Alt+Enter」による改行をPower Queryで置換する(Table.ReplaceValue関数)

データにある改行コードを置換する際、Advanced options内の2つのオプションそれぞれの意味について、認識と違う部分があったので整理します。

# この記事で指している改行コードは何か？
Excelで「Alt+Enter」を押した際に挿入される改行コードのラインフィード（LF）です。Excelでは「検索と置換」から検索する文字列に「Ctrl+j」を挿入することで検索または置換ができます。


# 文字列の置換に関する２つのオプション
Power BIではGUIからも簡単に文字列を置換できます。その際にAdvanced optionsを開くと、２つのチェックボックスがあります。１つ目は"Match entire cell contents"、２つ目は"Replace using special characters"です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/60387/625c41fb-7e36-3965-4b14-9b940ee385f1.png)

この表示上の右下にあるOKを押した後にPower Queryとして挿入されるのは[Table.ReplaceValue](https://docs.microsoft.com/en-us/powerquery-m/table-replacevalue)関数です。


# なにが認識と違ったのか
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/60387/0d73001c-fd9c-6f79-1721-2347e58adf98.png)

２つ目のチェックボックス"Replace using special characters"は改行コードなど特殊文字を置換するオプションです。単に#(lf)を置換する場合は、2つ目のチェックを入れた状態で"Insert special character"を押し、開かれたドロップダウンメニューの中から"Line Feed"を選択すれば#(lf)が置換対象として設定されます。

私の認識が違っていたのは1つ目のチェックボックスでした。

1つ目のオプションの意味
誤：繰り返しを全て置換する
**正：完全一致のみを置換する**

なので両方にチェックを入れても、改行のみが存在するフィールド以外置換されません（当たり前ですが）。またオプションをつけない場合、繰り返し登場する改行コードは基本的に全て置換されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/60387/9b4cda35-36d2-d309-f212-5d07659945ca.png)

私はこの勘違いで２ヶ月間も溶かしてしまい、反省を込めて記事にしました。

# 全ての改行を置換したく無い場合はどうしたらいいのか？
基本的に繰り返しは全て置換されるので、部分的に置換したい時のパターンを考えたいと思います。Table.ReplaceValue関数には登場する順番を考慮して置換するオプションは無いため、Text関数をいくつか使って追加カラムを作成する方法を考えました。

### １つ目の改行だけを置換したい場合は？（追加カラムとして）
```TextReplace_for_1st_lf.M
Text.Start(
   [元データ],
   Text.PositionOf([元データ], "#(lf)")
)
& Text.End(
   [元データ],
   Text.Length([元データ]) - Text.PositionOf([元データ], "#(lf)") - 1
)

```


### 最後の改行だけを置換したい場合は？（追加カラムとして）
```TextReplace_for_1st_lf.M
Text.Start(
　　　[元データ],
　　　Text.PositionOf([元データ], "#(lf)", Occurrence.Last)
)
& Text.End(
　　　[元データ],
　　　Text.Length([元データ]) - Text.PositionOf([元データ], "#(lf)", Occurrence.Last) - 1
)
```

※任意の位置で置換する方法についても考えています。
※本記事が初投稿なので、書き方のアドバイスなど頂けましたら幸いです。


## ※追記（2019/07/17）
コメントでいただいた@tanuki_phoenixさんのアイデアです。

### 任意の位置で改行を除去したい場合は？

```任意位置除去.M
(Source as text,cnt as number)=>
let
    分割後 = Splitter.SplitTextByDelimiter("#(lf)")(Source),
    文字列生成=List.Accumulate(List.Positions(分割後),
                              "",
                             (x,y)=> x & (if y=cnt or y=0 then "" else "#(lf)" ) & 分割後{y}
              )
in
    文字列生成
```
New SourceからBlank Queryを選択し、
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/60387/707ea53b-b2f2-0685-95aa-fc7459bad632.png)

このpower queryをコピペするとカスタム関数を作成できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/60387/fffadc16-8cd2-48e2-15c5-dafed18cae71.png)

Add Columnタブから"Invoke Custom Function"を選択して、
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/60387/7e0ed5dc-4781-3529-c035-a44d5e464b07.png)

上から順に、追加する列名、使用するカスタム関数の名前、置換したい元データの列、置換したい任意の位置(数値)を入力しOKを押します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/60387/f25dc5c6-efb3-203d-6ce0-5255d012aa4f.png)

「任意の位置で除去」の列のように2番目の位置で改行を除去できました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/60387/87769cac-f101-0223-a3cd-51b4583348dd.png)



任意位置の改行除去以外にも2つのアイデアをいただきましたので、同じくカスタム関数として使用できる形で下記にまとめています。

### 最初の位置で改行を除去したい場合は？

```最初だけ除去.M
(Source as text)=>
let
    文字列生成=Text.Combine(Splitter.SplitTextByEachDelimiter({"#(lf)"})(Source))
in
    文字列生成
```

[Splitter.SplitTextByEachDelimiter関数](https://docs.microsoft.com/en-us/powerquery-m/splitter-splittextbyeachdelimiter "Microsoft Docs")にオプションを付けない場合は、1つ目のDelimiterで分割することになるのでクエリが簡潔になりますね。


### 最後の位置で改行を除去したい場合は？

```最後だけ除去.M
(Source as text)=>
let
    //改行で全部ばらす．改行の数+1個のリストになる．
    Source = Splitter.SplitTextByDelimiter("#(lf)")(Source),
    //リストに入っている個数だけ，頭から適用される．なので，改行数を1個減らす．
    Custom1 = Combiner.CombineTextByEachDelimiter(
                List.Repeat({"#(lf)"},List.Count(Source)-2)
              )(Source)
in
    Custom1
```
オプションで分割の位置指定ができるので、一度全ての改行位置で文字列を分割したリストを作り、それを再び最後の位置以外で結合させるという方法。
未検証ですがListで処理をした方が早い気がするので、大規模データにはこちらを使用した方が良いかもしれません。

@tanuki_phoenixさん、ありがとうございました。