最近のExcelに標準で含まれているPower Queryを使用し、Excelで、JSON形式のデータから値抽出する方法です。JSON形式の場合も、【Power Query】エクセルで複数のXML形式のデータをインポートして整理と同様に抽出可能です。以下、具体的な手順の事例です。
使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」(バージョン2311)です。
内容:
事例の前提
次のように、一つのフォルダに「複数のJSON形式のファイル」が保存されている。
事例のデータは以下で公開されているサンプルデータです。
ダウンロードファイル内にある「\02_Web-API機能\03_取得期間指定\03_JSON」)を使用しています。
手順
基本の手順は、以下と同じです。
上記で説明を参考に
- 「フォルダーから」をクリック
- 複数のJSONファイルが保存されているフォルダを選択
- 「データの結合と変換」をクリック
- データ状況に応じて「読み込み」か「データの変換」をクリック
- 必要なデータを抽出する
を実施します。
以下は、「5. 必要なデータを抽出する」以降の実例です。
抽出したいデータ内容に応じて、列のピボットや、Listの展開などをします。
必要なデータの抽出前の状態
「4. データ状況に応じて「読み込み」か「データの変換」をクリック」で、「データの変換」をクリックすると、次のように縦方向のデータが抽出される。
列「Name」を選択後、列のピボット
列「Name」のデータで列を作成するため、列「Name」を選択後に「列のピボット」をクリックする。
値列を”Value”、値の集計関数”集計しない”に設定する。
Listを新しい行に展開
列のピボット後、Listを新しい行に展開する。
Recordを展開する
抽出したいデータがRecord内であったので、Recordを展開し、必要な項目を選択する。
以上で、次のようにデータ抽出できた。
補足説明
今回の事例に使用したJSONファイルの中身(一例)は、次のようなデータです(中略しています)。
{
"lastUpdateDate" : "2023-11-13",
"count" : "6",
"divideNumber" : "1",
"divideSize" : "1",
"announcement" : [ {
"sequenceNumber" : "1",
"registratedNumber" : "T0000000000001",
"process" : "01",
"correct" : "0",
"kind" : "2",
"country" : "1",
"latest" : "1",
"registrationDate" : "2023-10-05",
"updateDate" : "2023-09-29",
"disposalDate" : "",
"expireDate" : "",
"address" : "北海道札幌市中央区北一条西1丁目1番1号",
"addressPrefectureCode" : "01",
"addressCityCode" : "101",
"addressRequest" : "",
"addressRequestPrefectureCode" : "",
"addressRequestCityCode" : "",
"kana" : "",
"name" : "株式会社インボイス1",
"addressInside" : "",
"addressInsidePrefectureCode" : "",
"addressInsideCityCode" : "",
"tradeName" : "",
"popularName_previousName" : ""
}, {※中略
} ]
}
以上、PowerQuery(パワークエリ)を活用し、エクセルで複数のJSON形式のデータをインポートして整理する事例でした。