Power Query workout - JSON format

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

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

https://docs.microsoft.com/en-us/powerquery-m/odata-feed

```text: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

https://docs.microsoft.com/en-us/powerquery-m/web-contents

https://docs.microsoft.com/en-us/powerquery-m/json-document

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

```text: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 ウォームアップ

```json:Case&#x20;1&#x20;Source 
[
    { "Id" : 1, "Field1": 10 },
    { "Id" : 2, "Field1": 20 },
    { "Id" : 3, "Field1": 30 }
]
```
```text:Case&#x20;1&#x20;Solution
let
    Source = Json.Document( File.Contents( FileFullName ) ),
    ConvertToTable = Table.FromRecords(
        Source,
        type table [
            Id = Int64.Type,
            Field1 = Number.Type
        ]
    )
in
    ConvertToTable
```

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

```json:Case&#x20;2&#x20;Source&#x20;1 
[
    { "Id" : 1, "Field1": 10   },
    { "Id" : 2, "Field1": null },
    { "Id" : 3 }
]
```
```json:Case&#x20;2&#x20;Source&#x20;2 
[
    { "Id" : 1, "Field1": 10, "Field_x": 10 },
    { "Id" : 2, "Field1": 20, "Field_y": 10  },
    { "Id" : 3, "Field1": 30, "Field_z": 10  }
]
```
```json:Case&#x20;2&#x20;Source&#x20;3
[
    { "Id" : 1, "Field1": 10   },
    { "Id" : 2, "Field1": null },
    { "Id" : 3, "Field_z": 10  }
]
```

```text:Case&#x20;2&#x20;Solution&#x20;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
```

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

```text:Case&#x20;2&#x20;Solution&#x20;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*

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

```text:Case&#x20;2&#x20;Solution&#x20;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 すこし複雑、でも普通にある

```json:Case&#x20;3&#x20;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": "🤔"
        }
    }
]
```

```texttext:Case&#x20;3&#x20;Solution&#x20
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" は未定義なので無視される

# その他
https://powerbi.connpass.com/

- [Power BI 勉強会 - connpass](https://powerbi.connpass.com/)
- [Power BI Desktop を使い始めるには](http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981)
- [Excel で Power Query を使い始めるには](http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9)