[Power Query] １列に積み重なったデータを表形式にするベストプラクティスは？ (1) Table.AddIndexColumn → Table.Pivot

※この記事は 2023/7/22 に作成しました。
※オプションで、「地域の設定」の「クエリステップ」は、「常に英語」に設定されています。日本語で使用している場合は、各自環境で作成されるコードに読み替えてください。

# Index
- [(1) Table.AddIndexColumn → Table.Pivot](https://qiita.com/spumoni/items/f949f370aea5a831aeea)
- [(2) Table.Split → Table.Transpose → Table.Combine](https://qiita.com/spumoni/items/0de5b66f3d71b69572bd)
- [(3) List.Split → Table.FromRows](https://qiita.com/spumoni/items/0bcf50c7720f93804fcb)
- [(4) Table.AlternateRows → Table.FromColumns](https://qiita.com/spumoni/items/679150a28de7a9a30f1d)
- [(5) List.Alternate → Table.FromColumns](https://qiita.com/spumoni/items/4aac2d40984ba4c244ce)
- [(6) まとめ](https://qiita.com/spumoni/items/3503970baa038e349a79)


# 課題: 1列に積み重なったデータの整形

　様々な整形されていないデータを扱っていると、以下のような形式のデータを扱うことがあります。

```
鬼滅の刃 10
[著]吾峠呼世晴
2018/03/01
鬼滅の刃 11
[著]吾峠呼世晴
2018/06/01
鬼滅の刃 12
[著]吾峠呼世晴
2018/08/01
鬼滅の刃 13
[著]吾峠呼世晴
2018/11/01
```

　上記の例では、書名、著者名、出版日の3つの項目が1列に積み重なっています。これを、以下の表形式にする方法を紹介し、どれが一番いいのか比較してみます。

書名|著者名|出版日
|:--|:--|:--|
鬼滅の刃 10|[著]吾峠呼世晴|2018/03/01
鬼滅の刃 11|[著]吾峠呼世晴|2018/06/01
鬼滅の刃 12|[著]吾峠呼世晴|2018/08/01
鬼滅の刃 13|[著]吾峠呼世晴|2018/11/01

- 参考とした記事: @PowerBIxyz(Takeshi Kagata)さんの「Power Query workout - Table.AlternateRows / List.Alternate」
かなり深い話なので、この記事だけで何の話か理解できた人は、かなりの強者です。私も20回回ったくらいでやっと何の話か分かった気がします。

https://qiita.com/PowerBIxyz/items/ce155d960f0f6779ec64

- 今回使用するデータ: メディア芸術データベース・ラボの「データセットダウンロード」からjson形式のデータを変換して使用させていただきました。

https://mediag.bunka.go.jp/madb_lab/lod/download/

# 解法1: ピボットを使用する

　Power Queryのピボット機能を使って行うことができます。この方法は、ブログやYoutubeで紹介されることが多く、とても人気のある方法です。

<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/48c21f59-965e-48b4-3c72-2d35b21192fe.png"/>

## Table.AddIndexColumn
　元のデータは、値値となります。ここに行見出しと列見出しを「列の追加」で作成していきます。

　まず、「列の追加」タブで「全般」グループの「インデックス列（０から）」を選択します。

<img width="300" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/8ec3140b-34e7-5f5b-9c45-a5cc5442af81.png"/>

## Table.AddColumn
　次に、追加された「インデックス」列を選択して、「列の追加」タブで「数値から」グループの「標準」→「剰余」を選択し、値に項目数の3を入れると、０～２を繰り返す値が設定され、これが**列見出し**になります。

<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/d324b9cc-3ec6-a0dc-3b2d-f9e7b3f16bae.png"/>
<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/33bfd5a1-d400-126c-7264-fd879a686a5d.png"/>

## Table.TransformColumns
　行見出しは、先に作成した「インデックス」列を変換します。
　「変換」タブで「数値の列」グループの「整数」→「整数除算」を選択し、値に列数の3を入れます。

<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/de4cfc2f-9cdc-7915-c92f-d134de3cdf9e.png"/>

　変換を実行すると、以下のようになります。

<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/a9241f01-92e4-51c9-4349-5e99721d4cce.png"/>

## Table.Pivot
　列見出しになる「剰余」列を選択し、「変換」タブで「任意の列」グループの「列のピボット」を選択します。「列のピボット」のダイアログが表示されますので、値列となる Column1 と、値の集計関数に「集計しない」を選択してOKを押します。

<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/02a9c683-1df3-6714-5bda-e29120b22410.png"/>

　以下のようなテーブルが出来上がりますので、最後に「インデックス」列を削除すれば完成です。

<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/f469dfe8-309f-ffca-1391-5f5fa1eaa57b.png"/>

## Table.Pivot関数解説
　Table.Pivot関数の構文は以下の通りです。

```text:構文
Table.Pivot(
    table as table, 
    pivotValues as list, 
    attributeColumn as text, 
    valueColumn as text, 
    optional aggregationFunction as nullable function
) as table
```

https://learn.microsoft.com/ja-jp/powerquery-m/table-pivot

　**pivotValues** には、列見出しとなる項目のリスト、 **attributeColumn** は列見出しとなる項目の列名を設定します。そして、 **valueColumn** は、値列の列名を設定します。また、 **optional** には、集計関数を設定します。

|集計関数名|関数|
|--|--|
カウント(すべて)|List.Count
カウント(空白なし)|List.NonNullCount
最小値|List.Min
最大値|List.Max
中央|List.Median

　集計しない場合は、optional は設定されません。

　今回の処理で作成されたコードは、以下のようになっていました。（適宜改行＆インデントしています）

```text:Table.Pivot
PivotedColumn = Table.Pivot(
    Table.TransformColumnTypes(
        IntegerDividedColumn, 
        {
            {
                "剰余", 
                type text
            }
        }, 
        "ja-JP"
    ),
    List.Distinct(
        Table.TransformColumnTypes(IntegerDividedColumn, 
        {
            {
                "剰余", 
                type text
            }
        }, 
        "ja-JP"
    )[剰余]),
    "剰余",
    "Column1"
),
```

　Table.Pivotの第一引数と第二引数の中で **Table.TransformColumnTypes** が行われています。これは、列見出しとなる項目が10進数型になっていたため、ピボットするためにテキスト型に変換する必要があるからです。事前にテキスト型に変換するステップ「Changedtype」を挿入しておけば、Table.Pivotの引数は以下のようにすっきりした形になります。

```text
PivotedColumn = Table.Pivot(
    ChangedType, 
    List.Distinct(
        ChangedType[剰余]
    ), 
    "剰余", 
    "Column1"
)
```

 Table.Pivotの第二引数は、 **ChangedType[剰余]** として値のリストを抽出した後に **List.Distinct** で重複を削除しています。

## ソースコード
　今回のコードの全体は、以下のようになっています。

```text
let
    // ファイルの読み込み
    Source = Table.FromColumns(
        {
            Lines.FromBinary(
                File.Contents( "** File Name **" ),
                null,
                null,
                932  // シフトJIS
            )
        }
    ),
    // インデックス列の追加(0から始まる)
    AddedIndex = 
        Table.AddIndexColumn(
            Source, 
            "インデックス", 
            0, 
            1, 
            Int64.Type
        ),
    // 列見出しを作成
    InsertedModulo = 
        Table.AddColumn(
            AddedIndex, 
            "剰余", 
            each Number.Mod([インデックス], 3), 
            type number
        ),
    // 行見出しを作成
    IntegerDividedColumn = 
        Table.TransformColumns(
            InsertedModulo, 
            {
                {
                    "インデックス", 
                    each Number.IntegerDivide(_, 3), 
                    Int64.Type
                }
            }
    ),
    // ピボット前に型変換を行う
    ChangedType = 
        Table.TransformColumnTypes(
            IntegerDividedColumn, 
            {
                {"剰余", type text}
            }
        ),
    // 剰余列に対してピボットを行う
    PivotedColumn = 
        Table.Pivot(
            ChangedType,
            List.Distinct(ChangedType[剰余]),
            "剰余",
            "Column1"
        ),
    // インデックス列削除
    RemovedColumns = Table.RemoveColumns(PivotedColumn, {"インデックス"}),
    // 項目名変更
    RenamedColumns = 
        Table.RenameColumns(
            RemovedColumns, 
            {
                {"0", "書名"}, 
                {"1", "著者名"}, 
                {"2", "出版日"}
            }
        )
in
    RenamedColumns
```