Power Queryで1レコード複数行・複数列のExcel表をリスト形式に変換する
2023-04-26
azblob://2023/04/25/eyecatch/2023-04-26-power-query-good-tool-goodbye-macro-000.jpg

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




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





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





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

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

 

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

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





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

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





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





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





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





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





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





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





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





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





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





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




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





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