スキーマ情報とにらめっこ。準備段取りが大事。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" は未定義なので無視される |
その他