こんにちは、あおいです。
最近、趣味を食に全振りすぎてエンゲル係数がバグっているのでやばい、、、


さて、Power BIでExcelのデータを可視化したいけど、以下のような１レコード複数行、複数列のExcel表で扱いづらい…なんて経験ありませんか？

当然、このままでは非常に扱いづらいので、以下のようにリスト形式のデータテーブルに変換する必要があります。

でも、なんかめっちゃ難しそうですよね……

ご安心ください。Power Queryを使うことで、わずか数クリックのマウス操作で、綺麗なリスト形式のデータテーブルに変換できます。マクロやVBAで小難しいプログラムを組み必要は一切ないです。

 

そこで、今回はPower Queryで１レコード複数行、複数列のExcel表をリスト形式に変換する方法を紹介したいと思います。

［データを取得］から対象のExcelを選択して、［データの変換］をクリックします。

しばらくすると、Power Queryエディターが起動します。

画面右側の適用したステップに［変更された型］と［昇格されたヘッダー］が表示されていますが、この時点では不要なので両方削除します。

また、テーブルに不要な空白行もあるので、［行の削除］=>［下位の行の削除］を選択して削除します。

インデックス列を追加し、追加したインデックス列を選択した状態で［標準］⇒［除算（整数）］をクリックし、値に２を入力します。今回の例のExcel表は１レコード２行なので、２で割る必要があります。もし、１レコード３行の場合は３で割ります。

再度、追加したインデックス列を選択した状態で［標準］⇒［剰余］をクリックし、値に２を入力します。

このようにすることで、整数除算がレコード番号、剰余が１レコード中の1行目,2行目,,,という行番号、を表現することができます。列名は分かりやすいように［レコードNo］［行No］と変更します。

「伝票一覧」のクエリを右クリックで［参照］を選択して、新規クエリを作成します。

［行No］列が０のデータを抽出したいのでフィルターで絞り込み、［１行目をヘッダーとして使用］を選択します。

「行No：1」のクエリに関しても同様の手順で抽出します。

伝票一覧のクエリを右クリックで［参照］を選択して、レコードNo以外の列を削除します。

［重複の削除］を選択し、フィルターで不要なレコードNoを非表示にします。今回は０を非表示にします。

最後に、［クエリのマージ］で紐づけるキーとなる［レコードNo］列を選択し、［行No：0］と［行No：1］のクエリを結合します。

あとは展開して表示したい列の項目を選択すれば完了です。

今回はPower Queryで１レコード複数行、複数列のExcel表をリスト形式に変換する方法を紹介させていただきました。汚れたExcelデータのクレンジングは面倒臭いなぁ…と感じる作業だからこそ、短時間で終わらせるスキルがあるとめっちゃ楽です。なんだかんだ日本企業ではExcelが重宝されているので、Power Queryのデータ加工機能は必ず役立ちます。