[Excel]PowerQueryを使って表をデータに変換する

PowerQueryでお題があったの、でできる限りボタンポチポチで変換してみました。
（もっとあっさりできるかもしれません。）

<blockquote class="twitter-tweet" data-lang="ja"><p lang="ja" dir="ltr">1枚目の表を、2枚目の表のように整形してください。<br><br>関数以外でも、エクセルの機能はなんでも使ってOKです。<a href="https://twitter.com/hashtag/%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB%E3%82%AF%E3%82%A4%E3%82%BA?src=hash&amp;ref_src=twsrc%5Etfw">#エクセルクイズ</a> <a href="https://t.co/m1SKH9SeJS">pic.twitter.com/m1SKH9SeJS</a></p>&mdash; はけた＠Excel最高の学び方発売中 (@excelspeedup) <a href="https://twitter.com/excelspeedup/status/1129340241465135104?ref_src=twsrc%5Etfw">2019年5月17日</a></blockquote>
<script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

<blockquote class="twitter-tweet" data-lang="ja"><p lang="ja" dir="ltr">Power Queryで、あっさりと結合する方法があったら、教えてほしいです。そういう方法が、ありそうな気がするんですよね・・・。<br><br>特に「データが何列になっても」データを整形する方法があるのなら、知りたいです。マウス操作限定で、できればなお良しです。</p>&mdash; はけた＠Excel最高の学び方発売中 (@excelspeedup) <a href="https://twitter.com/excelspeedup/status/1129348244364902402?ref_src=twsrc%5Etfw">2019年5月17日</a></blockquote>
<script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

以下の表をデータに変換します。
この表は横や縦に拡張される可能性があるので、それを考慮した変形になります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/4b5450f7-ac99-061b-1384-c90ec0aaa8dd.png)





### PowerQueryに取り込みます
[データ]-[テーブルまたは範囲から]で取り込みます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/53bc5209-f1e4-ed95-e8f9-417b839b4082.png)

↓

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/96227302-3a1b-f333-fc9d-b5a6b4e33ba3.png)

### 転置します
[変換]-[入れ替え]で表を転置します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/e1939961-cc5c-21f1-d134-a2eaf0a52bb6.png)

### インデックス列を追加します
[列の追加]-[インデックス列]-[0から]でインデックスを追加します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/837079cc-66a1-c203-0116-b8aa20b768e0.png)

### ピボット解除します
インデックス列を選択して[変換]-[列のピボット解除]-[そのほかの列のピボット解除]でピボット解除します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/a58a9f23-c88b-f0fa-01f1-c35fb501ddea.png)

ここまでくるとなんとなくゴールが見えてきますね。

### インデックス+1の列を追加します
[列の追加]-[カスタム列]で`[インデックス]+1`を指定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/eb8495f6-841f-d9e4-4178-bd712d9d4b41.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/820ed369-0b6f-81ec-91de-21bae6253322.png)

### 自分自身をマージします
[ホーム]-[クエリのマージ]でカスタム列とインデックス列と属性をキーに左外部結合します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/86d59cc4-3081-df24-3fc4-a9a106399d3d.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/2b6a426a-ec69-7816-b697-2c62cc07e619.png)

### テーブルを展開し値を抽出します
見出しの展開ボタンを押して値だけ展開します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/5dc44b1a-a9c2-d2b5-c703-8962ef4c124a.png)

### インデックスが偶数の行だけに絞ります
マウス操作で偶数のみ抽出する方法が見当たらなかったので、一旦適当にフィルタします。0にしときます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/9188796c-ef5e-b67f-b4b3-bb35155bb0ee.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/4bc9dc21-f1b7-eb9d-a97c-11a184b7e466.png)

数式バーにて、`[インデックス]=0`を`Number.IsEven([インデックス])`に書き換えます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/83faa719-c899-7158-4f9b-d32ce8522ce2.png)

### 不要な列を削除し見出しを整えて完成です
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/7e369e0f-c432-182f-f5c5-3eac7c2ae754.png)

```
let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"製品番号", type text}, {"数量", Int64.Type}, {"製品番号2", type text}, {"数量3", Int64.Type}, {"製品番号4", type text}, {"数量5", Int64.Type}, {"数量6", type text}, {"数量7", Int64.Type}}),
    転置されたテーブル = Table.Transpose(変更された型),
    追加されたインデックス = Table.AddIndexColumn(転置されたテーブル, "インデックス", 0, 1),
    ピボット解除された他の列 = Table.UnpivotOtherColumns(追加されたインデックス, {"インデックス"}, "属性", "値"),
    追加されたカスタム = Table.AddColumn(ピボット解除された他の列, "カスタム", each [インデックス]+1),
    マージされたクエリ数 = Table.NestedJoin(追加されたカスタム, {"カスタム", "属性"}, 追加されたカスタム, {"インデックス", "属性"}, "追加されたカスタム", JoinKind.LeftOuter),
    #"展開された 追加されたカスタム1" = Table.ExpandTableColumn(マージされたクエリ数, "追加されたカスタム", {"値"}, {"追加されたカスタム.値"}),
    フィルターされた行 = Table.SelectRows(#"展開された 追加されたカスタム1", each (Number.IsEven([インデックス]))),
    削除された列 = Table.RemoveColumns(フィルターされた行,{"インデックス", "属性", "カスタム"}),
    #"名前が変更された列 " = Table.RenameColumns(削除された列,{{"値", "製造番号"}, {"追加されたカスタム.値", "数値"}})
in
    #"名前が変更された列 "
```

もっとあっさりできるようなできないような。
M言語をマスターすればもっとすっきりするんでしょうね！