スキーマ情報とにらめっこ。準備段取りが大事。Json フォーマットで出力されるデータソースってことはスキーマ情報が用意されていると思うんだよね。

OData.Feed

OData サービスをデータソースにするとき、OData.Feed を使うことが多い。クエリフォールディングが可能だから。またスキーマ情報も取得するからデータタイプは適切なものになる。

OData.Feed

let
    Northwind_Products = OData.Feed(
        "https://services.odata.org/V4/Northwind/Northwind.svc",
        null,
        [Implementation="2.0"]
    ),
    Products_table = Northwind_Products{[Name="Products",Signature="table"]}[Data],
    KeptFirstRows = Table.FirstN(Products_table, 5),
    ExpandedOrder_Details = Table.ExpandTableColumn(
        KeptFirstRows,
        "Order_Details", {"OrderID", "Quantity"}
    ),
    RemovedOtherColumns = Table.SelectColumns(
        ExpandedOrder_Details,
        {"ProductID", "ProductName", "UnitPrice", "OrderID", "Quantity"}
    )
in
    RemovedOtherColumns

metadata を取得し利用するのはコネクタの機能
https://services.odata.org/V4/Northwind/Northwind.svc/$metadata
得られたスキーマ情報から適切な行や列の展開も

Type.TableSchema( Value.Type( KeptFirstRows ) )

Name	Position	TypeName	Kind	IsNullable
ProductID	0	Int32.Type	number	FALSE
ProductName	1	Text.Type	text	FALSE
SupplierID	2	Int32.Type	number	TRUE
CategoryID	3	Int32.Type	number	TRUE
QuantityPerUnit	4	Text.Type	text	TRUE
UnitPrice	5	Decimal.Type	number	TRUE
UnitsInStock	6	Int16.Type	number	TRUE
UnitsOnOrder	7	Int16.Type	number	TRUE
ReorderLevel	8	Int16.Type	number	TRUE
Discontinued	9	Logical.Type	logical	FALSE
Category	10	Record.Type	record	TRUE
Order_Details	11	Table.Type	table	FALSE
Supplier	12	Record.Type	record	TRUE

Type.TableSchema( Value.Type( RemovedOtherColumns ) )

Name	Position	TypeName	Kind	IsNullable
ProductID	0	Int32.Type	number	FALSE
ProductName	1	Text.Type	text	FALSE
UnitPrice	2	Decimal.Type	number	TRUE
OrderID	3	Int32.Type	number	TRUE
Quantity	4	Int16.Type	number	TRUE

Web.Contents

OData.Feed を使用しない場合、Web.Contents と Json.Document

metadata の取得のような特別な機能はない。Web API から JSON フォーマットのデータを取得しても思いのほかデータタイプの設定や行や列の整理や展開に手間取る。であれば、JSON フォーマットをテーブルに変換されるときにスキーマ情報を追加してあげればいい。

Web.Contents

let
    Northwind_Products = Web.Contents(
        "https://services.odata.org/V4/Northwind/Northwind.svc/",
        [
            RelativePath="Products",
            Query = [
                #"$select" = "ProductID,ProductName,UnitPrice",
                #"$expand" = "Order_Details($select=OrderID,Quantity)",
                #"$top" = "5"
            ]
        ]
    ),
    JsonContent = Json.Document( Northwind_Products ),
    TableFromRecords = Table.FromRecords(
        JsonContent[value],
        type table [
            ProductID     = Int64.Type,
            ProductName   = Text.Type,
            UnitPrice     = nullable Decimal.Type,
            Order_Details = table [
                OrderID  = nullable Int64.Type,
                Quantity = nullable Int16.Type
            ]
        ]
    ),
    ExpandedOrder_Details = Table.ExpandTableColumn(
        TableFromRecords,
        "Order_Details",
        {"OrderID", "Quantity"}
    )
in
    ExpandedOrder_Details

File.Contents

JSON フォーマットのファイルをデータソースにするとき、File.Contents を使用する。

Case 1 ウォームアップ

Case 1 Source

[
    { "Id" : 1, "Field1": 10 },
    { "Id" : 2, "Field1": 20 },
    { "Id" : 3, "Field1": 30 }
]

Case 1 Solution

let
    Source = Json.Document( File.Contents( FileFullName ) ),
    ConvertToTable = Table.FromRecords(
        Source,
        type table [
            Id = Int64.Type,
            Field1 = Number.Type
        ]
    )
in
    ConvertToTable

Case 2 あったりなかったり

いきあたりばったりじゃなくて、スキーマ情報をよく読む。

Case 2 Source 1

[
    { "Id" : 1, "Field1": 10   },
    { "Id" : 2, "Field1": null },
    { "Id" : 3 }
]

Case 2 Source 2

[
    { "Id" : 1, "Field1": 10, "Field_x": 10 },
    { "Id" : 2, "Field1": 20, "Field_y": 10  },
    { "Id" : 3, "Field1": 30, "Field_z": 10  }
]

Case 2 Source 3

[
    { "Id" : 1, "Field1": 10   },
    { "Id" : 2, "Field1": null },
    { "Id" : 3, "Field_z": 10  }
]

Case 2 Solution 1

let
    Source = Json.Document( File.Contents( FileFullName ) ),
    ConvertedToTable = Table.FromList(
        Source, Splitter.SplitByNothing(),
        type table [
            Records = [
                Id = Int64.Type,
                Field1 = Number.Type
            ]
        ],
        null, ExtraValues.Error
    ),
    // CovertToTable = Table.FromColumns(
    //     { Source },
    //     type table [
    //         Rows = [
    //             Id = Int64.Type,
    //             Field1 = Number.Type
    //         ]
    //     ]
    // ),
    ExpandedRows = Table.ExpandRecordColumn(
        ConvertedToTable,
        "Records", {"Id", "Field1"}
    )
in
    ExpandedRows

定義を別に用意する方がよいはず。

Case 2 Solution 2

let
    DecisionTable = Table.Sort(
        Table.FromRecords(
            {
                [
                    Position = 0, Name = "Id",
                    NewName = "ID", NewType = Int64.Type
                ],
                [
                    Position = 1, Name = "Field1",
                    NewName = "列1", NewType = Number.Type
                ]
            }
        ), "Position"
    ),
    Source = Json.Document( File.Contents( FileFullName ) ),
    ConvertedToTable = Table.FromList(
        Source, Splitter.SplitByNothing(),
        type table [ Records = Record.Type ],
        null, ExtraValues.Error
    ),
    ExpandedRecords = Table.ExpandRecordColumn(
        ConvertedToTable, "Records",
        DecisionTable[Name], DecisionTable[NewName]
    ),
    ChangedToNewType = Table.TransformColumnTypes(
        ExpandedRecords,
        Table.ToRows( DecisionTable[[NewName], [NewType]] )
    )
in
    ChangedToNewType

MissingField.UseNull(MissingField.Type) を使用する変換では nullable

[
    Reason = "Expression.Error",
    Message = "The columns of the specified table type must be nullable.",
    Detail = null
]

Case 2 Solution 3

let
    DecisionTable = Table.Sort(
        Table.FromRecords(
            {
                [
                    Position = 0, Name = "Id",
                    Type = type nullable Int64.Type,
                    NewName = "ID"
                ],
                [
                    Position = 1, Name = "Field1",
                    Type = type nullable number,
                    NewName = "列1"
                ]
            }
        ), "Position"
    ),
    RowType = Type.ForRecord(
        Record.Combine(
            List.Transform(
                Table.ToRecords(
                    DecisionTable[[Name], [Type]]
                ),
                each Record.AddField(
                    [],
                    [Name],
                    [Type = [Type], Optional = false]
                )
            )
        ),
        false
    ),
    Source = Json.Document( File.Contents( FileFullName ) ),
    ConvertedToTable = Table.FromRecords(
        Source,
        type table RowType,
        MissingField.UseNull
    ),
    RenamedColumns = Table.RenameColumns(
        ConvertedToTable,
        Table.ToRows( DecisionTable[[Name], [NewName]] )
    )
in
    RenamedColumns

Case 3 すこし複雑、でも普通にある

Case 3 Source

[
    {
        "Id": 1
    },
    {
        "Id": 2,
        "Field1": null,
        "Field2": null
    },
    {
        "Id": 3,
        "Field1": [ "a", "b", "c" ],
        "Field2": {
            "Field21": 10,
            "Field22": "🐗"
        }
    },
    {
        "Id": 4,
        "Field1": [],
        "Field2": {}
    },
    {
        "Id": 5,
        "Field2": {
            "Field22": "🐗",
            "Field23": "🤔"
        }
    }
]

Case 3 Solution

let
    DecisionTable = Table.FromRecords(
        {
            [
                Position = 0, Name = "Id",
                Type = type nullable Int64.Type
            ],
            [
                Position = 1, Name = "Field1",
                Type = type nullable {text}
            ],
            [
                Position = 2, Name = "Field2",
                Type = type nullable [
                    Field21 = Int64.Type,
                    Field22 = Text.Type
                ]
            ]
        }
    ),
    RowType = Type.ForRecord(
        Record.Combine(
            List.Transform(
                Table.ToRecords(
                    DecisionTable[[Name], [Type]]
                ),
                each Record.AddField(
                    [],
                    [Name],
                    [Type = [Type], Optional = false]
                )
            )
        ),
        false
    ),
    Source = Json.Document( File.Contents( FileFullName ) ),
    ConvertedTable = Table.FromRecords(
        Source,
        type table RowType, MissingField.UseNull
    ),
    ExpandedField1 = Table.ExpandListColumn(
        ConvertedTable, "Field1"
    ),
    ExpandedField2 = Table.ExpandRecordColumn(
        ExpandedField1,
        "Field2", {"Field21", "Field22"}
    )
    // ExpandedField2 = Table.ExpandRecordColumn(
    //     ExpandedField1,
    //     "Field2",
    //     Record.FieldNames(
    //         Type.RecordFields(
    //             DecisionTable{[Name = "Field2"]}[Type]
    //         )
    //     )
    // )

    // ExpandedField2 = (
    //     (source as table, fieldName as text)=>
    //         Table.ExpandRecordColumn(
    //             source,
    //             fieldName,
    //             Record.FieldNames(
    //                 Type.RecordFields(
    //                     DecisionTable{[Name = fieldName]}[Type]
    //                 )
    //             )
    //         )
    // )( ExpandedField1, "Field2" )
in
    ExpandedField2

Name	Position	TypeName	Kind	IsNullable
Id	0	Int64.Type	number	TRUE
Field1	1	Text.Type	text	TRUE
Field21	2	Int64.Type	number	TRUE
Field22	3	Text.Type	text	TRUE
"Field23" は未定義なので無視される

その他