❎

Excel の Power Query で JSON データをテーブルに起こす

2024/03/29に公開

Excel

JSON

Power Query

tech

割と簡単。

動機

JSON データを CSV にしたい時がある。JSON でやり取りするほうが都合の良いこともあるが、なんだかんだで CSV を捨てることは出来ないので、CSV 形式に JSON のデータを変換できるようにしておきたい。

JSON のサンプルデータ

ChatGPT に任せて作ってもらった。person3 と person4 は一部のフィールドが欠落していることに注意する。

{
    "person1": {
        "name": "Alice Smith",
        "country": "United States",
        "birthday": "1990-05-15"
    },
    "person2": {
        "name": "Bob Johnson",
        "country": "Canada",
        "birthday": "1985-10-20"
    },
    "person3": {
        "name": "Emily Brown",
        "country": "United Kingdom"
    },
    "person4": {
        "name": "Michael Lee",
        "birthday": "1992-12-28"
    },
    "person5": {
        "name": "Sophia Rodriguez",
        "country": "Spain",
        "birthday": "1983-07-11"
    }
}

Power Query する

データを読み込む

「データ」タブ → 「データの取得」を押下する
「ファイルから」→「JSON から」を押下し、JSON ファイルを選ぶ

テーブルに変換する

このボタン押すだけ。

フィールドを列に展開する

今回は Value 列の ←→ みたいなボタンを押す。そうすると展開するべき列が表示されるので必要なものを選択する。元の列名をプレフィックス云々は、チェックをいれると列名が案の定元の列名.フィールド名みたいな形で展開されるようになる。

今回はネストがないレコードだったのでこの作業は一回で済んだ。ネストのあるレコードの場合は複数回列の展開が必要になる。

列の展開をすると、最終的に次のような見た目になる。満足したら左上の「閉じて読み込む」ボタンを押下する。

CSV にする

普通に CSV として保存するだけ。

欠落データの取り扱い

Power Query では JSON データを読み込むとき、全レコード全体を見たうえで生成できる列すべてを生成しようとする。今回は name, country, birthday の3列が存在するが、person3 は birthday、person4 は country が欠落している。

このとき、Power Query はエラーを出すことはせず、代わりに null を値として充当する。Excel のテーブルに変換するとセルの上では空っぽになる。

動機