目次
はじめに
kintoneに限った話ではないですが、kintoneのテーブルデータのように1つのIDに対して複数の情報を含むデータベースを扱うことがあると思います。
例えば、購買行動で考えると、以下のようなイメージでしょうか。
- Aさんは、1回の買い物で、みかん3個、リンゴ4個を購入しました。
- Bさんは、1回の買い物で、ブドウ2房、スイカ1玉、イチゴ1パック購入しました。
- Cさんは、1回の買い物で、パイナップル1個、メロン1玉、バナナ1房、キウイ3個購入しました。
上記をたとえばkintone表現すると以下のようなデータベースになると思います。
これをダウンロードすると、以下のような形になってしまいます。
今回は、このように1レコードが複数行にわたってしまう状態を1レコードに戻すという方法をpowerQueryを使って紹介したいと思います。
以前に、以下の記事を上げておりますが、今回は、テーブルの行にシーケンスが入っていない、または、テーブルの行が異なる行数の場合でも1行にできる方法を紹介したいと思います。
方法
動画でもご確認いただけます。
- STEP
テーブルデータの整形
まずは、テーブルデータとそのままのデータを下図のように分割しておきます。
左のクエリのデータを右クリックして、参照を左クリックします。
続いて、上記でできた新しいクエリに対して、下図のようにテーブルのグループ化と行数のカウントをしておきます。
ホーム＞変換＞グループ化をクリックし、上のラジオボタンは詳細設定を選択します。
グループ化のキーはレコード番号（これはユニーク値であればなんでもいいです）で、新しく作る列の操作は、すべての行と行数のカウントとします。新しい列名は好きな名前を設定してください。
続いて、これが最大のポイントなのですが、下図のように列の追加をします。
列の追加＞全般＞カスタム列で、以下の関数を入力します。
Table.AddIndexColumn([カウント], "index", 1, 1)簡単に説明しますと、カウントというテーブルデータに1からプラス1ずつインデックス番号を付与します。
関数に関して詳しい説明を知りたい方は、以下を参照ください。
続いて、テーブル内の情報を下図のように展開します。
カスタムという名前の列の右側にあるボタンを左クリックして、テーブル内の情報だけに☑を入れ、OKを左クリックします。
すると、下図のようになります。
続いて、商品列群を下図のように1列にまとめます。
商品列群を選択した状態で、変換＞テキストの列＞列のマージを左クリックし、区切り記号を選択して、新しい列名を入力し、OKをクリックします。
続いて、ここもポイントなのですが、商品の購入数量群の新しい列を下図のように作成します。
列の追加＞全般＞カスタム列を左クリックし、以下の関数を入力します。
"商品購入数量" & Text.From([index], "ja-JP")上記の関数の意味は、indexという数値を文字列として扱うということです。
続いて、ピボット化するためにテーブルになっている列を削除します。
Tableになっているカウント列を選択した状態で、ホーム＞列の管理＞列の削除を左クリックします。
さて、また重要なポイントなのですが、次に、下図のように、列のピボット化をします。
商品購入数量のカスタム列を選択した状態で、変換＞任意の列＞列のピボットを左クリックします。
値列は結合済みを選択し、詳細設定オプションを展開し、値の集計関数は集計しないにして、OKをクリックします。
すると、下図のように、商品購入数量が列ごとに分解されました。
あともう一息です。
ここで、またもやポイントなのですが、下図のように、行の並べ替えをします。
行数の右側にある▼ボタンを左クリックして、降順で並べ替えを左クリックします。（ここでは要するに、行数が多い順に並べています。）
続いて、同じステップで、下図のように、レコード番号（これはユニーク値であればそれで構いません）の右側にある▼ボタンを左クリックして、昇順で並べ替え（降順で並べ替えでも可）を左クリックします。
続いて、同じステップで、下図のように、indexの右側にある▼ボタンを左クリックして、昇順で並べ替えを左クリックします。
上記を行うと、下図のようになります。
次に、それぞれのユニークレコードへデータをコピーするために、下図のようにします。
ここも重要なポイントなのですが、商品購入数量の2番目以降を選択した状態で、変換＞任意の列＞フィルの右の▼ボタンから上を左クリックします。
続いて、ユニークレコードだけを下図のように抽出します。
indexの右側の▼ボタンを左クリックして、1だけに☑をいれた状態で、OKをクリックします。
すると、下図のようにようやく購入した人ごとに横並びでデータが抽出できます。
このまま終わってもいいですが、最後に、商品購入数量の列を下図のように分解していきます。
商品購入数量の列を選択した状態で、ホーム＞変換＞列の分割の▼ボタンをクリックし、区切り記号による列の分割を選択します。
区切り記号をコロン、分割を区切り記号の出現ごとにしてOKをクリックします。
これを、あと3回繰り返すと、下図のようになります。
お疲れ様でした。これでテーブルを1行にまとめることができました。
- STEP
元のデータと結合
STEP1が鬼のように長かったですが、残りは非常に簡単です。
下図のように、ホーム＞結合＞クエリのマージの右の▼ボタンを左クリックして、新規としてクエリをマージを左クリックします。
下図のように、上のデータを元のデータに設定して、下のデータをSTEP1で整形したデータを設定します。照合するキーをレコード番号（ユニーク値）とし、OKをクリックします。
続いて、下図のように、不要な列（商品テーブルの列）を削除します。
削除したい列を選択した状態で、ホーム＞列の管理＞列の削除を左クリックします。
続いて、下図のように、テーブルのデータを展開します。
テーブルの列の右のボタンを左クリックして、不要な列のチェックを外し、OKをクリックします。
最後に、下図のように、行の絞込をします。
レコードの開始行の右の▼ボタンを左クリックして、＊に☑を入れてOKをクリックします。
これを閉じて読み込むと、下図のように、1行のレコードにまとめることができました。
さいごに
いかがだったでしょうか。
正直、ここまでやる意味があるのかという声が聞こえてきそうですが、上の作業において、人間のコピペというもっとも危ない手作業がない状態が作れます。
上記はたかだか購入商品を1行にしているだけですが、例えばこれが世帯主がユーザーの固有のレコードで、扶養家族がテーブルになっているケースなどの個人情報を扱うような場合、手作業でコピペして個人情報を誤って違う人へ流出させた場合、その責任は非常に重大なものになるでしょう。
このように、何をやっているかわからない複雑な関数、あるいは、誰も変更できないマクロなどを使わないでも、上記のステップで、だれでも簡単に同じ操作ができるというのがこのpowerQueryのメリットだと思いますので、これからもこういった情報をシェアできればと思います。