【Power Query】JSONの型ゆれ｜文字列と数値の混在を吸収する

外部APIのJSONを読み込むと、同じ項目が "12"（文字列）と 12（数値）の両方で返ってくることがあります。こうした型ゆれは、見た目が同じ数字でも、Power Queryでは別の型として扱われます。そのまま集計すると、エラーになります。

この記事では、混在した値を数値にそろえる書き方と、変換できない値が混ざったときの挙動を、実測した結果で紹介します。サンプルのJSONは自作したもので、実際のデータではありません。

想定読者：Power Queryで外部APIやJSONを取り込み、集計や並べ替えをしている人です。

JSONの型ゆれで何が起きるか
解決コード：try Number.Fromで数値にそろえる
4つの書き方を比べた結果
変換できない値が混ざったとき
よくある失敗
応用例
まとめ

JSONの型ゆれで何が起きるか

次のJSONを例にします。Qty と Price で、行ごとに文字列と数値が混ざっています。

[
  {"OrderId":"A001","Qty":"12","Price":1200},
  {"OrderId":"A002","Qty":5,"Price":"800"},
  {"OrderId":"A003","Qty":"7","Price":950.5}
]

Json.Document で読み込むと、Qty は1行目が文字列、2行目が数値になりました。Price も同じように混ざります。この列を List.Sum で合計すると、エラーになりました。

混在したまま昇順に並べ替えると、5, 12, 7 の順になりました。今回の1例だけの結果で、この並びになる理由は調べていません。

解決コード：try Number.Fromで数値にそろえる

try Number.From(_) otherwise null を、列ごとの変換に使います。

let
    Raw = "[{""OrderId"":""A001"",""Qty"":""12"",""Price"":1200},{""OrderId"":""A002"",""Qty"":5,""Price"":""800""},{""OrderId"":""A003"",""Qty"":""7"",""Price"":950.5}]",
    Src = Table.FromRecords(Json.Document(Text.ToBinary(Raw))),
    Fixed = Table.TransformColumns(Src, {
        {"Qty", each try Number.From(_) otherwise null, type nullable number},
        {"Price", each try Number.From(_) otherwise null, type nullable number}
    })
in
    Fixed

Raw の部分を、実際のAPIの取得結果に置き換えて使います。Web.Contents で取得した場合は、今回は検証していません。変換後の合計は、Qty が24、Price が2950.5になりました。

Power Queryで文字列と数値が混在した値をNumber.Fromとtry Number.Fromで変換した比較表と、変換後のQty列・Price列 — 再現用の最小コードの実行結果。左は変換の比較、右は解決コードで変換した結果

4つの書き方を比べた結果

同じ値を、4つの書き方で変換して比べました。「型を判定して変換」は、Value.Is で型を調べて分岐する書き方です。

ToNum = (v as any) as nullable number =>
    if v = null then null
    else if Value.Is(v, type number) then v
    else if Value.Is(v, type text) then
        (let t = Text.Trim(Text.Replace(v, ",", "")) in
         if t = "" then null else try Number.FromText(t, "en-US") otherwise null)
    else null

値	Number.From	型を判定して変換	try Number.From	Text.From経由
“12”	12	12	12	12
5	5	5	5	5
” 7 “	7	7	7	7
“1,200”	1200	1200	1200	1200
“”（空文字）	null	null	null	null
null	null	null	null	null
“abc”	エラー	null	null	エラー

「Text.From経由」は、Number.FromText(Text.From(v, "en-US"), "en-US") の形です。差が出たのは "abc" だけでした。空白やカンマ付きの文字列も、Number.From でそのまま数値になりました。型を判定する書き方は、try Number.From と、表の7つの値に950.5を加えた8つの値で、同じ結果でした。短い1行で足ります。

この結果は、日本語環境のExcel（Windows版、バージョン16.0）のものです。"1,200" の読み方は地域設定で変わる可能性があり、他の環境は未検証です。引数や戻り値の仕様は、Number.Fromの公式リファレンスを参照してください。