Power QueryでのJSON型の動的な展開

概要

PowerQueryでJSONの展開をしているときに躓いたので記録。
組成がJSON列で表現された5000行のCSV(JSON列compのキーは1000種類あります)があったとして、これをPowerQueryで展開し、クロス表にしようとしていました。

PowerQuery エディタでJSON列を解析すると出てくる[Record]列をさらに展開することでクロス表が得られますが、詳細エディターでコードをよくみると、展開するためのキーがハードコーディングされています。このままだと、キーの組み合わせが変わったときに対応しきれませんでした。

power query UIで展開した場合のコード

let
    ソース = Csv.Document(File.Contents("csvのパス.csv"),[Delimiter=",", Columns=4, Encoding=932, QuoteStyle=QuoteStyle.None]),
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    #"解析された JSON" = Table.TransformColumns(昇格されたヘッダー数,{{"comp", Json.Document}}),
    #"展開された comp" = Table.ExpandRecordColumn(#"解析された JSON", "comp", {"391", "727", "28", "828", "53", "234", "838", "268", "997", "965"}, {"comp.391", "comp.727", "comp.28", "comp.828", "comp.53", "comp.234", "comp.838", "comp.268", "comp.997", "comp.965"})
in
    #"展開された comp"

解決法

[Record]列を展開するためのリストAllKeysListを「JSON列のキーの、一意な値のリスト」で定義します。

let
    Source = Csv.Document(File.Contents("csvのパス.csv"), [Delimiter=",", Columns=4, Encoding=932, QuoteStyle=QuoteStyle.None]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    ParsedJSON = Table.TransformColumns(PromotedHeaders, {{"comp", Json.Document}}),

    AllKeysList = List.Distinct(List.Combine(List.Transform(ParsedJSON[comp], Record.FieldNames))),
    
    ExpandedComp = Table.ExpandRecordColumn(ParsedJSON, "comp", AllKeysList, AllKeysList)
in
    ExpandedComp

これで動的に展開されるようになりました。めでたし。
ところで、リストを結合して一意にする処理が入ったために、そこまで多くない行数にもかかわらず展開に1分くらいの時間がかかりました。当たり前ですが、ある程度行数が多いデータ or キーの種類が多いデータを扱いたいときはデータ構造を縦持ちにするなどしたほうがよいですね。

追記

行方向に展開してピボットするほうが早かったです。10秒くらいになりました。

let
    Source = Csv.Document(File.Contents("csvのパス.csv"), [Delimiter=",", Columns=4, Encoding=932, QuoteStyle=QuoteStyle.None]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    ParsedJSON = Table.TransformColumns(PromotedHeaders, {{"comp", Json.Document}}),
    
    ExpandedToRows = Table.AddColumn(ParsedJSON, "ExpandedComp", each Record.ToTable([comp])),
    
    RemovedOriginalComp = Table.RemoveColumns(ExpandedToRows, {"comp"}),
    
    FlattenedTable = Table.ExpandTableColumn(RemovedOriginalComp, "ExpandedComp", {"Name", "Value"}),

    RenamedColumns = Table.RenameColumns(FlattenedTable, {{"Name", "Key"}, {"Value", "Value"}}),
    
    PivotedTable = Table.Pivot(
        RenamedColumns, 
        List.Distinct(RenamedColumns[Key]), // ユニークなKeyのリストを列として展開
        "Key",  // ピボットの基準となる列
        "Value", // セルの値となる列
        List.Sum // 値が重複する場合は合計（必要に応じて変更）
    )
in
    PivotedTable