テーブルをバイナリ(JSON)化したり、テーブルに戻したり


Power Query記事を書く際のサンプルデータをコード化するのによく使っているやり方です。単純にJSON文字列が欲しいときにも、役立つかもしれません。

テーブルからバイナリ文字列に変換する

全工程をまとめたコードの様子

let
    //ブック内のシートの取り込み
    Source = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),

    //以下、変換作業
    Custom1 = Json.FromValue(#"Changed Type"),
    Custom2 = Binary.Compress(Custom1,Compression.Deflate),
    Custom3 = Binary.ToText(Custom2)
in
    Custom3

解説

1.出発点

こういうExcelテーブルがあるとします。

2.Power Queryで取り込む

ここまではお馴染みのはず。不明であれば公式の記事を見てください。

3.JSON化する

数式バー左端の「fx」ボタンを押してから、次のようなコードに書きかえます。
※「#"Changed Type"」は前のステップ名。適宜変えてください。
※JSONに変換する、っていうメニューをどこかのバージョンで見たような気がするのですが、手元の365ではないみたいです。

= Json.FromValue(#"Changed Type")

↓fxボタンを押した後

↓コード打った後の画面の様子(これでもう、binaryになってます。)

4.binaryを圧縮する

今回の例程度のデータなら圧縮不要ですけど、一般には長くなるので、圧縮すべきです。
同じく、「fx」ボタンから次のようなコードを入力。
※Custom1は前のステップ名です。
※第二引数は詳しくないので、解説できません。僕はいつもこちらの種類で圧縮します。

= Binary.Compress(Custom1,Compression.Deflate)

5.文字列で取り出す

binaryは文字列か整数のリストにできます。ここは文字列にします。

= Binary.ToText(Custom2)

↓こんな具合に文字列が出る。

バイナリ文字列からテーブルへ

1.文字列を貼る

空のクエリを作って、数式バーに貼ります。

2.binaryに戻す

バイナリ文字列とbinary型データは行ったり来たりできます。(整数のリストでも同様)
またまた「fx」ボタンを押して、コードを入れます。

= Binary.FromText(Source)

3.圧縮を戻す

同様に数式バーに入力します。第二引数は圧縮操作したときのものとそろえないといけません。

= Binary.Decompress(Custom1,Compression.Deflate)

ここまでくると、エディタ側が変換しうるデータとして認識してくれ、JSON解釈するメニューが出てくれます。(下図は右クリックした状態)

4.テーブル化

残念ながらUI一発ではテーブルにならないので、テーブル化するよう、数式バーの中身を書きかえます。
※UI操作でも手数をかければ、同様の結果となります。

= Table.FromRecords(Json.Document(Custom2,932))

↓JSON解釈をかけただけの状態

↓コードを書き換えた後

おまけ:JSON文字列を得る

「テーブルからバイナリ文字列に変換する」の工程3のところから、下記のような具合でJSON文字列を得ることもできます。

1.右クリックして、テキストにする


↓実行後

2.ドリルダウン

↓データに対して右クリックした様子

↓結果