LoginSignup
14
14

More than 1 year has passed since last update.

@yugoes1021(清水 優吾)

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

Last updated at Posted at 2021-08-27

今回は JSON 編

前回 は、「Power Query で動的に変わる列名に対応するには？ - 01」ということで、列数が動的に変わる場合に列名を定義したとおりに付けるということを Power Query にて対応しました。今回はその続きで、データに JSON が含まれていて、JSON の中のデータが動的に変わる場合に、同じように列名を定義した通りに付けてみます。

用意するデータ - ダウンロード可能です

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

今回のデータは用意するのがちょっと面倒だと思いますので、 ここから ダウンロードできるようにしてあります。
クリックすると、ブラウザで Excel が開くので [ファイル] - [名前を付けて保存] - [コピーのダウンロード] にてダウンロードしてください。

Data シート

image.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

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
回答 クエリを見ると、Excelで定義された通りに読み込めていることがわかりますね。このままだと Answer 列に定義の異なる JSON が含まれているので、FormId を指定して、ひとつのアンケートの回答に絞りましょう。単純に絞り込むと後で変更するのが面倒なので、パラメータにしておきましょう。

パラメータを作成

パラメータは [クエリ] 欄の何もないところで右クリックすると、コンテキストメニューから作成が可能です。「列定義」クエリの下あたりを右クリックしてください。
image.png

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

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

image.png
そしてついでに Excel のファイルパスもパラメータにしておくと、後で便利かもしれません。例えば PC を複数台持っている場合、作業するマシンが変わるとファイルパスが変わることが多いので、パラメータにしておくとここを作業用マシンのファイルパスに変えるだけで、作業の続きが可能です。

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

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

image.png

[FormId]パラメータを指定して絞り込む

image.png
[FormId] 列の右側で▼をクリックして [数値フィルター] - [指定の値に等しい] をクリックします。
image.png
[行のフィルター]画面で矢印を順に押して、[パラメータ] をクリック - [FormId] をクリックします。
image.png
↑ 数式バーがこうなれば、パラメータ [FormId] を設定できています。

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

image.png
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
image.png

「列定義」クエリ

列定義 クエリも同様に [FormId] パラメータで絞り込んでおきましょう。
image.png
ここからは前回の内容と同様にリスト化します。
以下のクエリの [変更された型] 以降を張り付けてください。

列定義クエリ
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 関数ですので、説明が必要な方は 前回 を読んでください。

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

列定義_型クエリ
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 の式) として評価をしてくれます。

今回はデータとして [Type] 列に "type text" という値を持たせているので、これを Expression.Evaluate 関数に渡すことで、Type 型として認識させています。
image.png

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

再び「回答」クエリ

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

[Answer] 列を JSON として解析

[Answer] 列を選択して、[変換] - [解析] - [JSON] をクリックすることで、値を JSON として扱います。
image.png
[Answer] 列が Record として返還されるので、列名の右側の展開ボタンをクリックして、展開します。
image.png
[元の列名をプレフィックスとして使用します] のチェックは外しておいてください。
image.png
OK をクリックするとこうなります。
image.png

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

Table.ExpandRecordColumn 関数

数式バーを見ると、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
↑ のようになっているはずなので、6行目と7行目の間に以下の2行を追加します。
さらに Table.ExpandRecordColumn 関数の第3引数と第4引数を変更します。

image.png

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

image.png

動的に変わる列に対する型変換も対応する

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

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

image.png
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
image.png
この状態で [詳細エディター] を見ると、
image.png
Table.TransformColumnTypes にて列名で型が指定されてます。ただ、これだと直に名称を指定していますので、動的に列名が変わると対応できません。これも Power Query を編集しましょう。

image.png

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

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

回答クエリの出来上がり

ここまで変更してきた回答クエリを整形するとこうなります。

回答クエリ
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
この状態で回答クエリを見てみると
image.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/

14
14
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
14
14