FIXER Tech Blog - Power Platform

FIXER cloud.config Tech Blog

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

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

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

　Power Query、まじで目からウロコなので、Excel使う人は是非活用してほしい。データの整形がめっちゃ楽。

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

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

　実は過去の記事でも紹介しましたが、今回はちょっとした応用編となっています。

Power Queryの「列のピボット」で1レコード複数行のExcel表をリスト形式に変換する | cloud.config Tech Blog

　応用編と言っても、そんな難しいことをする訳じゃないので気軽に読んでみてください。もし、難しく感じても「へぇ～、こういう方法もあるんだ～」程度の理解で大丈夫です。記憶の片隅にとどめていただければ。

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

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

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

　今回は「市町村」「感染者数」のそれぞれの列に対して、1レコード1行のデータテーブルに変換するクエリを作成して［クエリのマージ］で結合します。

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

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

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

　「市町村別コロナ感染者数」のクエリを右クリックで［複製］を選択して、新規クエリを作成します。

　クエリ名は分かりやすい名前に変更します。

　「市町村」「感染者数」のクエリを1レコード1行のデータテーブルに変換する作業をします。

　まず、「市町村」クエリで［レコードNo］［行No］［市町村］以外の列は削除します。

　こちらの記事で紹介しているように、［行No］を選択した状態で「列のピボット」をします。

　1レコード1行のデータテーブルに変換されています。

　「感染者数」クエリも［レコードNo］［行No］［感染者数］以外の列は削除します。

　感染者数がNullの行は不要のため、［行No］のフィルターで絞り込みます。

　1レコード1行のデータテーブルに変換されています。

　最後に、［クエリのマージ］で紐づけるキーとなる［レコードNo］列を選択し、［市町村］と［感染者数］のクエリを結合します。

　あとは列名やデータ型をよしなに変更すれば完了です。

　今回はこちらの記事の応用編という形で紹介させていただきました。個人によっては難しいと感じる方もいらっしゃるかもしれませんが、「へぇ～こういう方法もあるんだ～」程度の理解で問題ないです。”その時”がきたら、本記事の方法は必ず役立ちます。

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

■関連サイト

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