Excel のピボット テーブル で メジャー(DAX) を使用するには

Excel 2016 であれば Power Pivot for Excel がなくてもデータモデルにメジャーの追加や編集ができる。ピボット テーブルや ピボット グラフでより高度な集計分析作業が Excel 2016 だけでできるようになるわけです。Power BI Desktop や SSDTでメジャーを勉強したり試してみるのもよいのだけど、Excel + ピボット テーブルで確かめながらもいいのかなと思うんです。ワークシート で検算的なこともできますし。ただ、**Excel データモデルの 管理** ができないだけ。

# 目的

- Power BI や SSAS(表形式モード)の メジャー(measure)の勉強を手軽に
- ピボットテーブルでもっと高度な集計を

※ Power BI / SSAS / Excel の 分析エンジンは基本的に同じですが、バージョンが異なることで使用できるDAX 関数やリレーション機能が異なる部分が存在する。ただ、基本的なことには差異はない。

# 動作に必要な環境
Excel 2016
※ エンタープライズ向けSKUにない Excel 2016 でも動作するようです。サポートが受けられるかどうかは不明、というか調査に至らず。

## できないことがあるけれど、、
Power Pivot for Excel がない時、たとえば、

- Excel データモデルの編集
    - 列のデータ型の設定
    - 列の非表示
    - 計算列の追加
    - 階層の追加
    - 日付テーブルの追加
    - 列による並び替え

ができない。ただ、\[データの取得と変換] 機能 (Power Query)で充分に代用可能かと。フィールドの階層を設定するのは無理っぽい。フィールドの"セット"で似たようなことはできるが、スライサーなどの設定に制限が出てしまう。

# 試してみる
[サンプルデータ](https://github.com/MicrosoftLearning/Introduction-to-Data-Analysis-using-Excel/tree/master/Module2) を使わせてもらって。
取得したデータは何にしても"データモデル"に追加しないといけない。

## データを読み込む
Excel の テーブルとしてクエリエディターで読み込む。
[データ] タブ → [取得と変換] グループ → [テーブルまたは範囲から]

![テーブルまたは範囲から](https://qiita-image-store.s3.amazonaws.com/0/193509/22af2986-2786-43ee-0082-12bdc29dd80a.png)
リボン上の配置は環境により異なる。


## データを加工する
クエリ エディターで取り込んだデータを加工し、さらにデータモデルに [日付テーブル] を追加する準備をする。
クエリ Sales の 日付に関する列 "Date" から クエリ Calendar を作成。

### クエリ Sales
データの型を変換しているだけ。時刻は不要なので日付(date)だけに、金額に関するものは通貨型(currency)に

```text:Sales
let
// テーブル名で指定しているので異なる場合は修正が必要
    Source = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    ChangedType = Table.TransformColumnTypes(
        Source,
        {
              {"Date", type date}
            , {"Year", Int64.Type}
            , {"Customer ID", Int64.Type}
            , {"Customer Age", Int64.Type}
            , {"Customer Gender", type text}
            , {"Country", type text}
            , {"State", type text}
            , {"Product Category", type text}
            , {"Sub Category", type text}
            , {"Product", type text}
            , {"Order Quantity", Int64.Type}
            , {"Unit Cost", Currency.Type}
            , {"Unit Price", Currency.Type}
            , {"Cost", Currency.Type}
            , {"Revenue", Currency.Type}
        }
    )
in
    ChangedType
```

### クエリ Calendar
クエリ Sales の Date 列に含まれる日付から範囲を抽出し [日付カレンダー]を構成。Power Pivot for Excel がないので クエリ エディター(Power Query) で。

```text:Calendar
let
    Source = Sales[[Date]],
    Dates = Table.Profile(Source)[[Min], [Max]]{0},
    ListDates = List.Dates(
          Dates[Min]
        , Duration.Days(Dates[Max] - Dates[Min]) + 1
        , #duration(1, 0, 0, 0)
    ),
    ConvertedToTable = Table.FromList(
          ListDates
        , Splitter.SplitByNothing()
        , type table [Date = date]
    )
in
    ConvertedToTable
```

Table.Profile 関数に不具合があったようです。

```text:Calendar
/// Table.Profile に不具合出たとき用
let
    Source = Sales[Date],
    Dates = {List.Min(Source), List.Max(Source)},
    ListDates = List.Dates(
          Dates{0}
        , Duration.Days(Dates{1} - Dates{0}) + 1
        , #duration(1, 0, 0, 0)
    ),
    ConvertedToTable = Table.FromList(
          ListDates
        , Splitter.SplitByNothing()
        , type table [Date = date]
    )
in
    ConvertedToTable
```

## データモデルに追加
2つのクエリができたらデータモデルに読み込む。環境の設定によるが、[閉じて次に読み込む] を実行すると間違いがない。

![閉じて次に読み込む](https://qiita-image-store.s3.amazonaws.com/0/193509/d298cf7d-366b-5964-f37a-38dc1a6adae1.png)

テーブルは不要なので [接続の作成のみ] を選択
オプション：[このデータをデータ モデルに追加する]を有効
![読み込み先ダイアログ](https://qiita-image-store.s3.amazonaws.com/0/193509/4210b658-bc0b-8725-1a93-1b87e9a8a97b.png)

## ピボットテーブルを作成
読み込まれたデータモデルからピボットテーブルを作成
[このブックのデータ モデルを使用する] を選択
![ピボットテーブルの作成](https://qiita-image-store.s3.amazonaws.com/0/193509/03e9fdb6-05f5-8f45-d2d5-1c2558c6b304.png)


※ Office 365 ProPlus など エンタープライズ向け製品であれば クエリ エディターから読み込むと同時にピボットテーブル作成に遷移。
![データのインポート](https://qiita-image-store.s3.amazonaws.com/0/193509/acb38361-c446-b2bc-a3d4-a2f1c59aa0d5.png)

### リレーションを構成
テーブル Sales と テーブル Calendar で時系列に関する集計を試すのでリレーションを構成 

![リレーション](https://qiita-image-store.s3.amazonaws.com/0/193509/08d0adec-0ce1-0862-2d73-705ba94e7945.png)

## メジャーを追加する
[ピボットテーブルのフィールド] ウィンドウの テーブルを選択しコンテキストメニューから [メジャーの追加]
※ Calendar テーブルの 列 [Date] を 行 もしくは 列 に追加すると、年 / 四半期 / 月 の列が **データモデルに追加** される。これはデータモデル上テーブルに計算列(Calculated Field)状態なのだけどデータモデルの管理ができない。元に戻したい場合は、ピボットテーブルの列を選択し、[グループの解除]を実行すればよいし、再度追加したいときは[グループ化]すればよい。

![メジャーを追加](https://qiita-image-store.s3.amazonaws.com/0/193509/4d3fb893-2d7e-8e12-0f77-cd84b4d3df47.png)

### メジャーダイアログ
関数や列名、テーブル名など Intellisense が有効
![メジャーダイアログ](https://qiita-image-store.s3.amazonaws.com/0/193509/19a4f7a6-0827-2ba1-0bff-eab1134f01df.png)

メジャーは直ちに計算されるものではないので[DAX 式を確認]で問題がないか確認できる。
Ctrl キー + スクロール でフォントサイズの変更が可能。
![メジャーダイアログ](https://qiita-image-store.s3.amazonaws.com/0/193509/2f2bf0a2-1b86-a693-2366-0a243c478fa6.png)

![メジャーダイアログ](https://qiita-image-store.s3.amazonaws.com/0/193509/915bb2b2-208f-9c7a-3ceb-8b185dcd10c2.png)

## メジャーをピボットテーブルに配置
作成したメジャーを値エリアに配置することでメジャーがどのような結果を返しているのか確かめることができる。
![ピボットテーブル](https://qiita-image-store.s3.amazonaws.com/0/193509/b8d24c0f-b5c0-17c3-ba95-2058f09b3a1c.png)

# 情報
- [データのインポートと分析 - Excel](https://support.office.com/ja-jp/article/%e3%83%87%e3%83%bc%e3%82%bf%e3%81%ae%e3%82%a4%e3%83%b3%e3%83%9d%e3%83%bc%e3%83%88%e3%81%a8%e5%88%86%e6%9e%90-ccd3c4a6-272f-4c97-afbb-d3f27407fcde)
    - [Excel でデータ モデルを作成する - Excel](https://support.office.com/ja-jp/article/Excel-%E3%81%A7%E3%83%87%E3%83%BC%E3%82%BF-%E3%83%A2%E3%83%87%E3%83%AB%E3%82%92%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B-87e7a54c-87dc-488e-9410-5c75dbcb0f7b)
    - [PowerPivot のメジャー - Excel](https://support.office.com/ja-jp/article/PowerPivot-%E3%81%AE%E3%83%A1%E3%82%B8%E3%83%A3%E3%83%BC-86484821-a324-4da3-803b-82fd2e5033f4)
    - [PowerPivot における Data Analysis Expressions (DAX) - Excel](https://support.office.com/ja-jp/article/PowerPivot-%E3%81%AB%E3%81%8A%E3%81%91%E3%82%8B-Data-Analysis-Expressions-DAX-bab3fbe3-2385-485a-980b-5f64d3b0f730)
    - [Excel 2010 と Excel 2013 の PowerPivot データ モデルの間のバージョン互換性 - Excel](https://support.office.com/ja-jp/article/Excel-2010-%E3%81%A8-Excel-2013-%E3%81%AE-PowerPivot-%E3%83%87%E3%83%BC%E3%82%BF-%E3%83%A2%E3%83%87%E3%83%AB%E3%81%AE%E9%96%93%E3%81%AE%E3%83%90%E3%83%BC%E3%82%B8%E3%83%A7%E3%83%B3%E4%BA%92%E6%8F%9B%E6%80%A7-188f44fd-3cfd-4aa7-b4e6-a9402653cbf3)
- [Query and Data Modeling Languages | MSDN](https://msdn.microsoft.com/en-us/library/mt244090.aspx)


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