やっと見つけました。
複数行のデータを分析しやすい1行データに変換する方法を。
今回はそれについて解説していきます。
Contents
この記事でやりたいこと
2行データと、変換後のデータサンプル
例えばこんなデータを見たことはないですか？
このように、セルの結合までされてる2行のデータを、下図のような1行のデータにしようというのが今回の趣旨です。
そう、こんな形に変換出来ればここからの加工や分析がしやすいですよね。
パワークエリでの作業プロセス
やりたいことは2つで、1行目と2行目のテーブルに分割し、それを横付けで合体する。
この２つをパワークエリで処理するためのキモはこうです。
- 1行目と2行目のテーブルに分割するために、行数ごとのインデックスを付番すること。
- 1行目データテーブルと2行目データテーブルを結合（マージ）するためのキー列を決めること。
1行目と2行目のデータに分割する
データの取り込み
まずはデータタブの「テーブルまたは範囲から」をクリックします。
そして取り込む範囲を指定し、OKを押し、
パワークエリの編集画面が開いて取込ができればOKです。
行数ごとのインデックス列の追加
次に、データごとに1行目には１を、2行目には2となる条件列を追加します。
列の追加タブの「例からの列」を選択します。
この機能では列のサンプルを入力することで必要な関数を作ってくれます。
例えば、1行目に１、2行目に２と入力してENTERキーを押すと、他の列も埋まります。
今回の例では1行目がnullってなるので、これは修正しないといけません。
ちなみに、表示タブの「数式バー」にチェックを入れると数式バーを表示できます。
そして数式バーの数式を、このように修正します。
'変更前
= Table.AddColumn(変更された型, "カスタム", each if [列3] = "借方科目" then 1 else if [列3] = null then 2 else null, type number)
'変更後
＝ Table.AddColumn(変更された型, "行数", each if [列2] = null then 2 else 1 , type number)
参考までに、この数式の意味はこうです。
前ステップ（変更された型）に対して、「行数」列を追加し、追加した列には、「列2」（伝票No）が空欄なら2を、それ以外は1をセットする。
（参考）
Table.AddColumn:列の追加
if else (1) Then (2) else (3) :
（１）の条件を満たすときは（２）を、満たさないときは（３）をセットする。
数式を修正すると、すべての行に行数インデックスを付番できました。
行数インデックス付番のコツは、1行目と2行目の違いを見つけ、それに応じて行数をインプットするように「if else ~ Then~else~ 」を設定することです。
伝票Noをフィルで下方向にコピー
ここでは、伝票Noを下方向にコピーし、すべてのデータに伝票Noが入るようにします。
列2を選択し（又は列2のどこかを選択）、変換タブのフィルボタンを押し、下方向を選択します。
下方向にフィルされ、空欄（null）がすべて埋まりました。
一見意味のなさそうなプロセスですが、この伝票Noが1行目テーブルと2行目テーブルを結合するキーとなるため、実は重要なプロセスなのです。
1行目テーブルと2行目テーブルの作成
ここまでできればいよいよテーブルを1行目テーブルと2行目テーブルに分けていきます。
手順は簡単です。まず、このマル印「＞」をクリックし、クエリフィールドを開きます。
次に、テーブル2を右クリックし、「複製」を選択します。
ついでに名前も変えちゃいましょう。
同じように、右クリックで「名前の変更」を選択して変えます。
次に、1行目テーブルを選択して行数列を１でフィルタします。2行目テーブルも同じように、行数列を2でフィルタします。
これで、1行目テーブル、2行目テーブルが完成です。
分割したテーブルの結合
結合用クエリの作成
では、前段で作った1行目データと2行目データをくっつけていきましょう。
まずクエリフィールドで右クリックし、「新しいクエリ」「結合」「新規としてクエリをマージ」と進んでいきます。
マージの画面が開くので、テーブルには1行目・2行目を、照合列には「列2」（伝票No）を選択します。
これでOKすると・・・
1行目テーブルに2行目テーブルがくっつきました。
結合データの整形
次に、くっつけてテーブルを展開・整形していきましょう。
まずは展開する項目をチェックします。
今回の場合ですと、「借方/貸方消費税」、「摘要」です。
これでOKを押します。
このように2行目テーブルの項目が横に展開できました。
あとは、1行目をヘッダーに変換したり、
好きな並びに並び替えたりして整形し、読み込んだら完成です。
読み込むとこんな感じになりました。
ちなみに読込む際には、すべてのテーブルを「接続のみ」にし、データモデルに追加しておくことをお勧めします。
そうしておけばデータ容量節約しつつピボットテーブル等で分析できますので。
設定はExcelシートのクエリと接続フィールドからクエリを選択、右クリックして「読み込み先」を選択し、「接続の作成のみ」を選択して「このデータをデータモデルに追加する」にチェックを入れます。
そうすれば、シートにデータを貯めることなくピボットテーブル等で分析に使えます。
まとめ
いかがだったでしょうか。
複数行データ（2行データ）を1行データのデータベース形式に変換する方法をまとめました。
パワークエリを使うと、今まで分析に適さなかったデータも分析可能なデータベースに変換することができるので、これからもいろいろな事例を使ってやっていこうと思います。
では！
データ整形にオススメ本はこちら！
変換したデータを分析したい場合はこちらの本がオススメ！
