[Power BI Tips] Power Query で動的に変わる列名に対応するには？ - 02 JSON 編

# 今回は JSON 編
[前回](https://qiita.com/yugoes1021/items/56cda44d1913e57d742c) は、**「Power Query で動的に変わる列名に対応するには？ - 01」**ということで、列数が動的に変わる場合に列名を定義したとおりに付けるということを **Power Query** にて対応しました。今回はその続きで、データに JSON が含まれていて、JSON の中のデータが動的に変わる場合に、同じように列名を定義した通りに付けてみます。

# 用意するデータ - ダウンロード可能です
前回同様、説明に使うデータは **Excel** で用意します。ひとつの **Excel** ファイルに2つのシートを用意して、それぞれのシートに以下の様なテーブルをひとつずつ用意します。

今回のデータは用意するのがちょっと面倒だと思いますので、 [ここから](https://shimizumvp-my.sharepoint.com/:x:/g/personal/yugo_yugoes1021_com/EdRwc5JRu5tCvTLg2Ez2VuoBN2JwUpnetMczwoPHPKRfoA?e=deTBLs) ダウンロードできるようにしてあります。
クリックすると、ブラウザで **Excel** が開くので **[ファイル] - [名前を付けて保存] - [コピーのダウンロード]** にてダウンロードしてください。

## Data シート
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/40b2d7ec-4353-4023-cb85-1ca9843e0f7a.png)
**Data** シートに **回答** というテーブル名で4列のテーブルを作りました。注目すべきは4列目です。データが **JSON** になっています。この **JSON** データが動的に変わる、ということです。

このようなデータは自由に列定義を設計できる **SaaS** の **API** が返してくることがあります。有名なサービスだと **kintone** はこのイメージに近いですね。アプリを作ると裏でテーブルが作成されて、その列定義はアプリの項目（フィールドやオブジェクト）に依存するサービスです。通常は一度アプリを作成し使い始めたら、なかなか大きく変更はしないものですが、それでも既存項目を変更した場合、列名が変わってしまうことはあり得ます。

他にもアンケートが取れるサービスもこのようなデータを返してきます。アンケートというのは、アンケートごとに項目の定義が変わります。アンケートをひとつだけずーっと使い続けるのであれば、列定義は変わらないので、問題ないのですが、複数のアンケートを公開して、回答データはひとつの DB に保存する場合、アンケートごとに DB を分ければ問題ないのですが、なかなかそうもいきません。では、アンケートごとにテーブルを分けるか？というと、これまた面倒ですよね。アンケートを増やす度にテーブルを増やさなければならないというのは、なかなかに面倒な設計でしょう。

こういった列定義が動的に変わってしまう場合に、**JSON** データのまま、一つの列に保存してしまうことがあります。あるいはストレージに **JSON** のまま保存することもあるでしょう。**Azure** で言えば、**Blob Storage**、**AWS** で言えば、**S3** ということになります。いずれにせよ、取得したら **JSON** の形式で、その定義はどのアンケートの回答か？によって、変わるわけです。

今回のデータのイメージは **アンケートの回答** です。**アンケートデータ** を分析したいというのはよく聞く話です。ひとつの **DB** に複数のアンケートの回答が入っていても、分析する場合はアンケートごとに分析するのが普通ですので、今回は **1アンケート＝1レポート** の前提で行きます。

## Definition シート
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/86647b99-d0c5-b86d-9ca5-609508c9f834.png)

**Definition** シートに **列定義** というテーブル名で、元の列名と置き換えたい列名を定義しました。一番右の **Type** 列は **Power Query** で型を指定するために持たせてあります。**FormId** がアンケートを表しています。**FormId** が同じであれば、ひとつのアンケートであることを意味します。**Original** は元の列名、**NewCol** は置換した後の列名ですが、今回は設問そのものを表しています。

**Original** の値を見ると、**FomrId = 1** が **Col1 - 3**、**FomrId = 2** が **Col1 - 4** までを保持しています。**Data** シートの **Answer** 列の **JSON** を見ると、**Col1 - 4** が並んでいるのが確認できるでしょう。

さて、前提はイメージできたでしょうか？

# Power BI Desktop で読み込む
データが準備出来たら、**Power BI Desktop** で読み込みましょう。(**Power BI Desktop** の細かい操作は記載しません。あしからず)
用意した **Excel** ファイルを読み込みます。

2つのテーブルを読み込むと、クエリが2つできてるはずです。

## 「回答」クエリ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/44f81580-411a-b8f7-4ef3-f3d74882ab3d.png)
**回答** クエリを見ると、**Excel**で定義された通りに読み込めていることがわかりますね。このままだと **Answer** 列に定義の異なる **JSON** が含まれているので、**FormId** を指定して、ひとつのアンケートの回答に絞りましょう。単純に絞り込むと後で変更するのが面倒なので、パラメータにしておきましょう。

### パラメータを作成
パラメータは **[クエリ]** 欄の何もないところで右クリックすると、コンテキストメニューから作成が可能です。**「列定義」**クエリの下あたりを右クリックしてください。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/498b62a0-11f3-17c2-205e-6c89addad1ca.png)

**[パラメータの管理]**画面が開くので、以下のように入力してください

| 項目 | 設定値 |
|:-----|:------|
| 名前 | FormId |
| 種類 | 10進数 |
| 現在の値 | 1  |

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/a72ab94f-af5c-d0f6-4d9a-d0676da5f315.png)
そしてついでに **Excel** のファイルパスもパラメータにしておくと、後で便利かもしれません。例えば PC を複数台持っている場合、作業するマシンが変わるとファイルパスが変わることが多いので、パラメータにしておくとここを作業用マシンのファイルパスに変えるだけで、作業の続きが可能です。

Excelファイル用のパラメータも示しておきます。

| 項目 | 設定値 |
|:-----|:------|
| 名前 | ExcelFile |
| 種類 | テキスト |
| 現在の値 | (自身の環境に合わせたExcelファイルのパス)  |

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/87175e0d-8b7c-704d-415e-1cac279f6d69.png)

### [FormId]パラメータを指定して絞り込む
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/dcfd0904-5bc3-bc0d-7e58-84872133d7b6.png)
**[FormId]** 列の右側で▼をクリックして **[数値フィルター] - [指定の値に等しい]** をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/73a1f03a-3051-58fb-29c9-6203141a5d74.png)
**[行のフィルター]**画面で矢印を順に押して、**[パラメータ]** をクリック - **[FormId]** をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/8705d16a-46d1-b78d-df14-cc5606291dfd.png)
↑ 数式バーがこうなれば、パラメータ **[FormId]** を設定できています。

ついでに追加した **Excel** ファイルのパラメータも使ってみましょう。
**[適用したステップ]** の **[ソース]** の右側の歯車マークをクリック - 開いた画面の **[ファイルパス]** 左側のドロップダウンで **[パラメータ]** をクリックすると設定できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/194b9a49-0861-a9f4-4bd1-a473fd33b879.png)
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/91d96e68-7cb9-f2d5-9c5d-7d8f7e13be4c.png)

## 「列定義」クエリ
**列定義** クエリも同様に **[FormId]** パラメータで絞り込んでおきましょう。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/d962ee82-9872-10ae-397d-58309ff786ae.png)
ここからは前回の内容と同様にリスト化します。
以下のクエリの **[変更された型]** 以降を張り付けてください。

```PoqwerQuery:列定義クエリ
let
    ソース = Excel.Workbook(File.Contents(ExcelFile), null, true),
    列定義_Table = ソース{[Item="列定義",Kind="Table"]}[Data],
    フィルターされた行 = Table.SelectRows(列定義_Table, each ([FormId] = FormId)),
    変更された型 = 
        Table.TransformColumnTypes(
            フィルターされた行,
            {
                { "FormId", Int64.Type }, 
                { "Original", type text }, 
                { "NewCol", type text },
                { "Type", type text }
            }
        ),
    削除された他の列 = Table.SelectColumns(変更された型,{"Original", "NewCol"}),
    リスト作成 = Table.ToRows( 削除された他の列 )
in
    リスト作成
```
ポイントは Table.ToRows 関数ですので、説明が必要な方は [前回](https://qiita.com/yugoes1021/items/56cda44d1913e57d742c) を読んでください。

ここから前回と異なる部分です。Excelの **列定義** テーブルに **Type** 列を定義していましたが、その値を使用して、列の型も適用します。その為に元の列名 **Original** と **Tyep** のペアを持つもう一つ別のリストを作成する必要があります。以下のクエリを新しいクエリとして追加してください。

```PowerQuery:列定義_型クエリ
let
    ソース = Excel.Workbook( File.Contents( ExcelFile ), null, true ),
    列定義_Table = ソース{ [ Item = "列定義", Kind = "Table" ] }[ Data ],
    フィルターされた行 = Table.SelectRows( 列定義_Table, each ( [FormId] = FormId ) ),
    変更された型 = 
        Table.TransformColumnTypes(
            フィルターされた行,
            {
                { "FormId", Int64.Type }, 
                { "Original", type text }, 
                { "NewCol", type text },
                { "Type", type text }
            }
        ),
    削除された他の列 = Table.SelectColumns( 変更された型, { "NewCol", "Type" } ),
    型適用 = 
        Table.TransformColumns(
            削除された他の列,
            {
                { "Type", Expression.Evaluate }
            }
        ),
    リスト作成 = Table.ToRows( 型適用 )
in
    リスト作成
```
ポイントは **Expression.Evaluate** 関数です。この関数は **Power Query** を文字列として引数に渡すと、**M** 式 (**Power Query** の式) として評価をしてくれます。

https://docs.microsoft.com/ja-jp/powerquery-m/expression-evaluate

今回はデータとして **[Type]** 列に **"type text"** という値を持たせているので、これを Expression.Evaluate 関数に渡すことで、Type 型として認識させています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/5e2c3084-9989-447b-1c97-8b18304566a5.png)

これで **[列定義_型]** クエリは完成です。

## 再び「回答」クエリ
再度 **回答** クエリに戻って、**列定義** クエリと **列定義_型** クエリで作成したリストを使って、変換していきましょう。

### [Answer] 列を **JSON** として解析
**[Answer]** 列を選択して、**[変換]** - **[解析]** - **[JSON]** をクリックすることで、値を **JSON** として扱います。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/d2caf386-ab51-6079-aa67-07850e21dc99.png)
**[Answer]** 列が **Record** として返還されるので、列名の右側の展開ボタンをクリックして、展開します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/8106a0a4-2cb6-524b-c566-30d63b167f0b.png)
**[元の列名をプレフィックスとして使用します]** のチェックは外しておいてください。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/e005f4ca-2a89-c467-e774-ee9d32bd8486.png)
OK をクリックするとこうなります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/8b3f4bc0-18ca-67e4-b9a1-2b05f968a7cf.png)

```
= Table.ExpandRecordColumn(#"解析された JSON", "Answer", {"Col1", "Col2", "Col3"}, {"Col1", "Col2", "Col3"})
```

### Table.ExpandRecordColumn 関数
数式バーを見ると、**Table.ExpandRecordColumn** 関数が追加されたことがわかります。定義は次の通りです。

https://docs.microsoft.com/ja-jp/powerquery-m/table-expandrecordcolumn

```
Table.ExpandRecordColumn(table as table, column as text, fieldNames as list, optional newColumnNames as nullable list) as table
```

第1引数はテーブル、第2引数は第1引数のどの列を開くのか列名を指定します。
第3引数はレコードのフィールド名をリストで指定します。第4引数は新しい列名でこれもリストで指定します。第3引数と第4引数のリストは順序を一致させることで、その順序で列名が置換されます。この仕様がわかれば、それに合わせて、リストを作ればいいのです。

### Power Query を直に編集
次は詳細エディターで直に **Power Query** を記述します。
現状は
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/3bd4ca9f-5a7e-1da5-0661-21a140459b2d.png)
↑ のようになっているはずなので、6行目と7行目の間に以下の2行を追加します。
さらに **Table.ExpandRecordColumn** 関数の第3引数と第4引数を変更します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/f151a9b8-fdd8-65ec-9bfa-c6a7cd28ff9b.png)

ここまで上手くいっていれば、このようになっているはずです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/07fd31b1-4362-7cb0-03dc-07b8404903ce.png)

### 動的に変わる列に対する型変換も対応する
列名が意図したとおりに置換できていることがわかると思います。列名を置き換えた列を見ると、**型が指定されていない (= type any**) になっていることがわかります。通常であれば、マウスで型を順に指定していきます。試しにやってみましょう。

右の図のように3つの列をまとめて選択します。**[今日の内容は？]** 列をクリックして、**Shift** を押しながら、**[次回も参加しますか？]** 列をクリックすることでまとめて選択できます。この状態でリボンで **[データ型]** を **テキスト** にします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/2c42561e-c2b7-3ade-4b4b-c4ddf2da7239.png)
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/bea2f302-451e-9b23-f4d4-c105592ccfbc.png)
この状態で **[詳細エディター]** を見ると、
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/134e2937-a528-a242-89a1-ee119a3c4b57.png)
**Table.TransformColumnTypes** にて列名で型が指定されてます。ただ、これだと直に名称を指定していますので、動的に列名が変わると対応できません。これも **Power Query** を編集しましょう。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/2d879da6-214a-b284-795b-9ff529bc4fd2.png)

```
変更された型1 = Table.TransformColumnTypes(#"展開された Answer", 列定義_型)
```

事前に用意した **型定義_型** に変更することで、動的に列が変わることに対応できます。

### 回答クエリの出来上がり
ここまで変更してきた**回答**クエリを整形するとこうなります。

```PowerQuery:回答クエリ
let
    ソース = Excel.Workbook( File.Contents( ExcelFile ), null, true ),
    回答_Table = ソース{ [ Item = "回答", Kind = "Table" ] }[ Data ],
    変更された型 = 
        Table.TransformColumnTypes(
            回答_Table, 
            { 
                { "ID", Int64.Type }, 
                { "FormId", Int64.Type }, 
                { "Date", type date }, 
                { "Answer", type text } 
            } 
        ),
    フィルターされた行 = Table.SelectRows( 変更された型, each ( [FormId] = FormId ) ),
    #"解析された JSON" = 
        Table.TransformColumns(
            フィルターされた行, 
            { 
                {"Answer", Json.Document } 
            } 
        ),
    OriginalColNames = Record.FieldNames( List.First( #"解析された JSON"[Answer] ) ),
    NewColsList = List.ReplaceMatchingItems( OriginalColNames, 列定義 ),
    #"展開された Answer" = 
        Table.ExpandRecordColumn(
            #"解析された JSON", 
            "Answer", 
            OriginalColNames, 
            NewColsList
        ),
    ConvertColumnTypes = Table.TransformColumnTypes(
        #"展開された Answer",
        列定義_型
    )
in
    ConvertColumnTypes
```

ここまででいったん出来上がりなのですが、前提でひとつの **FormId** がひとつのアンケートを表していると言いましたので、**FormId** を変更して、動的な変更に対応しているか、確認してみましょう。

## FormId を変更してみる
**FormId** はパラメータにしてあるので、パラメータの値を変更することで、各クエリの絞り込みで指定される **FormId** を変更することができます。**FormId** パラメータを選択して、値を **2** に変えてください。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/79767328-b421-7d57-0802-f58a12a06ac5.png)
この状態で**回答**クエリを見てみると
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/152946/25bb70d3-2cb4-4ec9-13b4-d14cd3e8bfaf.png)
いかがでしょうか？ちゃんと **FormId = 2** のデータが表示されて、なおかつ定義された列定義に置き換わったはずです。

# おわり
いかがでしたでしょうか？
こんな感じにデータに **JSON** が含まれていても **Power Query** でちょっと工夫をすることで動的な変化に対応することが可能です。

昨今では、データに **JSON** が含まれていることも珍しくありません。ITエンジニアではない方にとっては、データが **JSON** だと、見た瞬間に絶望感を感じてしまう方もいるようですが、怖がる必要はありません。よぉーく見ると、単なるテキストです。**CSV** と違って、テーブル形式にデータが並んでいないため、複雑に見えるかもしれませんが、**Power Query** エディターは優秀なので、マウス操作のみでテーブル形式に変換することは可能です。

大事なのは、その時にどんな **Power Query** が使われているか、よく観察することです。観察することで「あれ？もしかしてこうやったら、動的な変化にも対応できる？」と思えたら、第一段階クリアです。試してみましょう。

最後まで読んでいただきありがとうございました。次回はまたなにかネタを思いついたら、書きます。
もしぜひ取り扱ってほしい！ということがあれば、お気軽に言ってください。面白いな！って思ったら、書かせていただきます。
皆様からのリクエスト、お待ちしております。

リクエストは、以下までー🤗
Twitter: https://twitter.com/yugoes1021
Facebook: https://www.facebook.com/yugoes1021
LinkedIn: https://www.linkedin.com/in/yugoes1021/