FIXER Tech Blog - Power Platform

FIXER cloud.config Tech Blog

Power Queryで1レコード複数行・複数列のExcel表をリスト形式に変換する

　本記事はFIXERが提供する「cloud.config Tech Blog」に掲載された「Power Queryで1レコード複数行・複数列のExcel表をリスト形式に変換する」を再編集したものです。

　こんにちは、あおいです。

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

　さて、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］列が０のデータを抽出したいのでフィルターで絞り込み、［1行目をヘッダーとして使用］を選択します。

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

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

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

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

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

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

あおい／FIXER
「初心者の方にも分かりやすく」をモットーにブログ執筆。
執筆書籍『Microsoft Power Platformローコード開発[活用]入門 - 現場で使える業務アプリのレシピ集』

■関連サイト

• cloud.config Tech Blogのエントリー
• cloud.config Tech Blog
• FIXER