Markdownの表に変換｜Power Query

6602 ワード
PowerBI Excel PowerQuery Excel テキストリンク

Excel上にある表、もしくはPower Queryのエディタにある表上のデータに使えます。
完成品はqiitaでしか動作確認してません。

2019/10/12追記：関数化したコードも載せました。

お題の表

空のクエリに貼るとテーブルになります。改行も半角縦棒(|)も入っています。
※置換作業をする都合上、いずれの列もテキスト型に変換にしておく必要がある。

サンプル
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZDRSgJBFIbfZa69UXub1VfowquCLjpzxF0lykpbwRAUiWjZRQihZOth+psdvfIVPO3EChVrMAfO/PzwfRzPUyYIq6qiTmROZYz/YPsd834BSuyyn03uVbPiqaq5frTjRArQS/AzOARH4A50DP0KDqR/9vkSr1eRlLIpm/nThi9l36ZB8f3wb8ADcAoml9i3JLsaO15255s4FKFt2s2ptYL6zZfSH4IiRT3QCNQGDaFvHTXfu6AI5/Sj8OVBPRusGq3GcalCvVCo/UNhAdKlCvuCU3DJQYujwqJebiGX/k2WMD/8TF45qrkD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, 列1 = _t, 列2 = _t, 列3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"列1", type text}, {"x", type text}, {"y", type text}, {"処理後の結果", type text}})
in
    #"Changed Type"

↓貼るとこうなる。

コード

最低限、セル内改行と縦棒の置換はしました。他に必要な置換があれば、「置換文字リスト」のところに書き足せばOK。（{{置換文字列、置換後},・・・}という構造）
表の中での横方向位置は、完成後の文字列に自分でコロンを足しましょう。

※複数文字列の置換については、下記の記事のパターン2コード(新)にて解説してます。
複数の語句をまとめて置換する - Qiita

q_Markdown用の表生成
let
    Source = サンプル,
    置換文字リスト = {{"#(cr,lf)","<br>"},{"#(lf)","<br>"},{"|","&#124;"}},
    置換用関数 =(文字列のリスト as list)=>
                    List.Transform(文字列のリスト,each 
                        List.Accumulate(
                            置換文字リスト,_,(x,y)=>Text.Replace(x,y{0},y{1})
                        )
                    ),
    タイトル = "|" & Text.Combine( 置換用関数(Table.ColumnNames(Source)),"|" ) &"|",
    タイトル下 = "|---"& Text.Repeat( "|---",Table.ColumnCount(Source)-1 ) &"|",

    中身 = Text.Combine( List.Transform(
                            Table.ToRows(Source),
                            each "|" & Text.Combine(置換用関数(_),"|") & "|"
                         ),"#(lf)"
           ),
    合成 = Text.Combine({タイトル,タイトル下,中身},"#(lf)")
in
    合成

出来上がりの文字列

これをqiitaに貼ると下記の通り。

列1 x y 処理後の結果
1周目 サンプルテキストの|中身 桃太郎 （桃太郎⇒ピーチ太郎置換後の文字列）
2周目 1周目の処理後の結果 おじいさん （さらに、おじいさん⇒お爺
置換後の文字列）
3周目 2周目の処理後の結果 おばあさん （さらに、おばあさん⇒おばあ
置換後の文字列）
4周目 3周目の処理後の結果 桃 （さらに、桃⇒モモ
置換後の文字列）

テスト環境

Windows7、Office365のExcel（32ビット.バージョン1908）
Power Queryのバージョン：2.72.5556.441

補足：改行について

入力方法により、下記のような違いがありました。

1.Power BIにあるテーブル入力メニューにExcelの表をそのまま貼りつけ
⇒セル内改行は#(cr,lf)になる。

2.同メニューに手入力で、shift+enterで改行。
⇒改行は#(lf)になる。

3.ExcelテーブルからPower Queryにロード
⇒セル内改行は#(lf)になる。

そのため、上記コードの「置換文字リスト」では両方を置換するようにしています。
置換の順番も大事です。なぜなら、#(cr,lf) で入っている改行について、#(lf)だけ置換されるということが起きるからです。
つまり、

//trueになる
= Text.Replace("a#(cr,lf)bc","#(lf)","") ="a#(cr)bc"　

となる。
では、左辺の実施した中身はどうなるかというと、

と一見、改行が取れた風に見える。でも、これをメモ帳に貼ると、

という具合で、#(cr)が残っていることがわかる。
ここまで気づくのに、悩んでしまいました。

公式情報ではPower Query M language specificationの2.6.1 Character Escape Sequencesを読むとよいです。（英語）

関数化したコード

自分で使ってみて、データ型変換がないのは不便に思ったので、それも入れて関数化しておきました。

(tbl as table)=>
let
    データ型をテキストに変換 = Table.TransformColumnTypes(tbl,
                           List.Transform( Table.ColumnNames(tbl),each {_,type text} )
                         ),
    置換文字リスト = {{"#(cr,lf)","<br>"},{"#(lf)","<br>"},{"|","&#124;"}},
    置換用関数 =(文字列のリスト as list)=>
                    List.Transform(文字列のリスト,each 
                        List.Accumulate(
                            置換文字リスト,_,(x,y)=>Text.Replace(x,y{0},y{1})
                        )
                    ),
    タイトル = "|" & Text.Combine( 置換用関数(Table.ColumnNames(データ型をテキストに変換)),"|" ) &"|",
    タイトル下 = "|---"& Text.Repeat( "|---",Table.ColumnCount(データ型をテキストに変換)-1 ) &"|",

    中身 = Text.Combine( List.Transform(
                            Table.ToRows(データ型をテキストに変換),
                            each "|" & Text.Combine(置換用関数(_),"|") & "|"
                         ),"#(lf)"
           ),
    合成 = Text.Combine({タイトル,タイトル下,中身},"#(lf)")
in
    合成