Power Query workout - Table type



# #table

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


```text:#table&#x20;function
// This table is empty.
#table( null, {} )
#table( type table [], {} )
#table( type table ( type [] ), {} )
```
```text:#table&#x20;function
// This table is empty.
#table( 2, {} )
#table( {"Column1", "Column2"}, {} )
#table( type table ( type [Column1, Column2] ), {} )
```
```text:#table&#x20;function
// This table is empty.
#table( type table [Column1 = Number.Type, Column2 = Int64.Type], {} )
#table( type table ( type [Column1 = Number.Type, Column2 = Int64.Type] ), {} )
```

# Table type / Row type
```text:Table&#x20;type
Type.Is(
    Value.Type(
        #table(
            type table [
                Column1 = Number.Type,
                Column2 = Int64.Type
            ],
            {} 
        )
    ),
    Table.Type
)   // true
```

```text:Row&#x20;type
Type.Is(
    Type.TableRow(
        Value.Type(
            #table(
                type table [
                    Column1 = Number.Type,
                    Column2 = Int64.Type
                ],
                {} 
            )
        )
    ),
    Record.Type
)
```
https://docs.microsoft.com/en-us/powerquery-m/type-tablerow

```text:Row&#x20;type
Type.RecordFields(
    Type.TableRow(
        Value.Type(
            #table(
                type table [
                    Column1 = Number.Type,
                    Column2 = Int64.Type
                ],
                {} 
            )
        )
    )
) =
[
    Column1 = [Type = Number.Type,  Optional = false],
    Column2 = [Type = Int64.Type,   Optional = false]
]   // true
```
https://docs.microsoft.com/en-us/powerquery-m/type-recordfields

```text:Row&#x20;type
Type.Is(
    Type.ForRecord(
        [
            Column1 = [Type = Number.Type,  Optional = false],
            Column2 = [Type = Int64.Type,   Optional = false]
        ],
        false
    ),
    Record.Type
)   // true
```
https://docs.microsoft.com/en-us/powerquery-m/type-forrecord

```text:Table&#x20;type
Type.Is(
    type table (
        Type.ForRecord(
            [
                Column1 = [Type = Number.Type,  Optional = false],
                Column2 = [Type = Int64.Type,   Optional = false]
            ],
            false
        )
    ),
    Table.Type
)   // true
```

```text:Table&#x20;type
#table(
    type table (
        Type.ForRecord(
            [
                Column1 = [Type = Number.Type,  Optional = false],
                Column2 = [Type = Int64.Type,   Optional = false]
            ],
            false
        )
    ),
    { { 1, 10 } }
) =
#table(
    type table [Column1 = Number.Type, Column2 = Int64.Type],
    { { 1, 10 } }
)   // true
```

## ライブラリ関数
### Type.TableColumn 
https://docs.microsoft.com/en-us/powerquery-m/type-tablecolumn

```text
List.Transform(
    {
        Null.Type,          // false
        type anynonnull,    // true
        Any.Type,           // true
        Number.Type,        // true
        Decimal.Type,       // true
        Currency.Type,      // true
        Int64.Type,         // true
        Percentage.Type,    // true
        DateTime.Type,      // false
        Date.Type,          // false
        Time.Type,          // false
        DateTimeZone.Type,  // false
        Duration.Type,      // false
        Text.Type,          // false
        Logical.Type,       // false
        Binary.Type         // false
    },
    each Type.Is(
        Type.TableColumn(
            type table [Column1 = Number.Type],
            "Column1"
        ),
        _
    )
)
```
```text
List.Transform(
    {
        type nullable Null.Type,            // true
        type nullable anynonnull,           // true
        type nullable Any.Type,             // true
        type nullable Number.Type,          // true
        type nullable Decimal.Type,         // true
        type nullable Currency.Type,        // true
        type nullable Int64.Type,           // true
        type nullable Percentage.Type,      // true
        type nullable DateTime.Type,        // true
        type nullable Date.Type,            // true
        type nullable Time.Type,            // true
        type nullable DateTimeZone.Type,    // true
        type nullable Duration.Type,        // true
        type nullable Text.Type,            // true
        type nullable Logical.Type,         // true
        type nullable Binary.Type           // true
    },
    each Type.Is(
        Type.TableColumn(
            type table [Column1 = Null.Type],
            "Column1"
        ),
        _
    )
)
```
### Type.AddTableKey / Type.TableKeys
https://docs.microsoft.com/en-us/powerquery-m/type-addtablekey

https://docs.microsoft.com/en-us/powerquery-m/type-tablekeys

```text
Type.TableKeys(
    Type.AddTableKey(
        type table [Column1 = Int64.Type, Column2 = Text.Type],
        { "Column1" },
        true
    )
) =
{
    [Columns = { "Column1" }, Primary = true]
}
```

### Type.TableSchema
```text
Type.RecordFields(
    Type.TableRow(
        Value.Type(
            Type.TableSchema(
                type table []
            )
        )
    )
)[
    [Name], [Position], [TypeName], [Kind]
] =
[
    Name =     [Type = Text.Type,   Optional = false],
    Position = [Type = Number.Type, Optional = false],
    TypeName = [Type = Text.Type,   Optional = false],
    Kind =     [Type = Text.Type,   Optional = false]
]   // true
```


# その他
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)