【奇怪な2行1組のデータもパワークエリの技術の組み合わせでテーブル形式にできます】
稀にですが、見出しだけでなくデータ行も下の画像のように2行になっている表を見ることがあります
セル結合により行列ともに2段になっている表に関しては以前、記事を書いてはいます⇒詳細
今回の場合には、全く違うアプローチを使います
最大のヒントは割り算の余りです
1,2,3それぞれの数字を２で割ると余りは１、０、１というように規則性が出てきます
この割り算の余りをフル活用します
目次
ポイント
余りの計算
こちらの余りを計算する場合は２つの技術を使います
インデックス列
列の追加タブにある「インデックス列」を使うと、連番を作成することができます
この連番を次に紹介する技術で割り、余りを算出します
剰余
剰余とは割り算で割る値を指定し、余りを算出する処理です
こちらの「剰余処理」で先ほどのインデックス列から余りを算出します
ステップ間の参照及び結合
上記の通り算出したら、一度、余りが０のものと１のものに分けます
そして再度、各グループ間でインデックスを上記の画像のようにつけます（インデックス２）
更にインデックス２を基にして同じ番号同士を結合する形になります
この余りが０と１の者同士を分けれるようにする為に、ステップの内容を分けます
参照
通常、パワークエリのステップは1つ前のステップを参照しています
ここでの「参照」はこの自動的に作成される「参照」を意図的に変更して、1つ前以外のステップを参照する意味になります
ちなみに、上記の画像のステップ：DATAとステップ：DATA２は全く同じ内容です
DATAステップは余りが１のものと０のものも両方揃っており、インデックスが付けられている状態です
DATA2ステップでは、複数前のステップの「DATAステップ」を呼び出して参照しています
このようにDATAステップを呼び出して参照し、余り１や余り２でフィルターすることで余り１（2行目）のステップと余り０（1行目）のステップを１つのクエリ内で作成します
参照の方法は簡単で、関数ボタンを押してステップを新規に追加し、参照ステップを入力するだけです
結合
結合というと普通はクエリ間の結合をイメージされると思います
実は同じクエリ内の複数ステップを結合することができます
上の画像を見て「全く同じものを結合してどうするの？」と思った方もいらっしゃると思います
こちらについては、次のステップでMコードを直接修正する形で調整します
それでは、主なポイントを解説したので、詳細な内容の解説に移りたいと思います
データの取得
まずはデータを取得してPower Queryエディタを開きます
見出しを下す
この時点で見出しになっているものは、データの1行目に降ろします
これでデータ行が2行で１つのセットになります
余りの算出
ポイントで前述したように、インデックス列を作成した後に「剰余」を選択します
そうすると、割る値を入力する画面が出るので「２」を入力します
こうして、余りが算出されます
ステップ名の変更
この余りが出た状態のステップを後で参照しやすいようにステップ名を変更します
2行目をフィルター及び再インデックス
ここからはフィルターボタンで2行目（余り０）への絞りこみを行います
そして再度インデックス列を追加します
この再度追加したインデックス列を後で使用して、1行目の内容と結合することになります
ちなみに作成したステップは後で分かりやすいものにしておきましょう
前ステップの参照
それでは余り１の1行目のステップを作成するために「DATA」ステップを参照したいと思います
まず、数式バーの関数マーク（fx）をクリックしてください
この関数マークをクリックすることで新たなステップが追加されます（下の画像の赤字の箇所です）
数式バーを見ると前行を参照しているのがよくわかると思います（黄色の箇所）
この参照を「DATA」ステップに変更します
これで余り１の行1のステップを作成する準備ができました
この後は余り０の時と同じように剰余列を作成し、再度インデックス列を作成します
２つのステップの結合
この段階では、最終ステップと最終ステップから3つ前のステップは次のようになっているはずです
ここからは２つのステップ内で再度作成したインデックス列を基にして、同じ行にするための結合処理を行います
結合は複数クエリの場合と同じようにクエリのマージを使用します
ただ1点違うのは、こちらの画面はあくまでダミーということです
2つ目のクエリに相当するところは「～（現在）」を選択します
但し、キー列はきちんと再度インデックスした列を選択します
この後、数式バーを見ると次の画像のようになっています
Table.NestedJoin関数内で同じステップ名を結合するようになっています
ですので、こちらは3つ目の引数を結合するステップ名に変更します
これで上のステップと結合されるようになりますので展開します
これで完成が見えてきました
次は変換タブから1行目をヘッダーに上げる処理を行います
後は無駄な列を削除したり、列の位置を並び替えしたりしましょう！
＜まとめ＞
今回は「連番」＋「余り」列を利用して2行１つの組み合わせの表を整備しました
余りを利用することに気づけば、意外と簡単にできます
この「意外な」簡単さがパワークエリの魅力ですね
尚、記事内で自クエリ内で結合する方法を解説しました
こちらはMコードを触るので「少し意味が分かりにくい」と感じた方もいらっしゃるかもしれません
その場合は直接、Table.NestedJoinの引数を確認して頂くと理解が早くなると思います
=Table.NestedJoin(テーブル名1,キー,テーブル名2,キー,新しい列名,省略可|結合タイプ)
最後に今回使用したサンプルファイルを添付します
コメントを残す