【PowerQuery】パワークエリを使ってデータを集計しよう①データの整形

こんにちは。aliceです。
ノンプロ研のPowerQuery講座で百人組手と呼ばれるペアプロをやっていただきました。

相手はMask de keitaro先生。

PowerQueryからPowerPivot・DAX・ダッシュボードまで内容盛りだくさん、次々と繰り広げられる華麗な技に感動しまくりでした。

内容はこちらです。

このようなExcelがあります。

テーブルっぽくないところや泊数という列があるところがイケていないです。

このデータを使ってこのようなダッシュボードを作ります。

素敵ですね✨

せっかくいろいろ教えていただいたので、忘れないうちに復習をしたいと思います。

今回はデータの整形について振り返ります。

１　元データをテーブル化する

まずは元データをテーブル化します。

ヘッダー名が微妙ですがそこは気にせず行きましょう。

２　データを取り込む

新しいブックを作成してパワークエリエディタにテーブルを取り込みます。

ここではテーブルを取り込みましょう。

３　変更された型を削除する

まずは「適用されたステップ」の「変更された型」を削除します。
型はあとで設定するので削除してOKです。

４　不要な列を削除する

次にデータを絞りましょう。

最初にやることはデータを絞ることです！（先生、忘れていてごめんなさい💦）
不要な合計列を削除します。DAXで合計は計算するので不要だそうです。

５　ヘッダー処理

今のヘッダー名は「列１」などの名前です。これでは何の列かわからないのでヘッダーを処理します。

まず、１行目をヘッダーとして使用したいので「１行目をヘッダーとして使用」をクリックします。

まだ1行目にヘッダーにしたい項目があるので列名を変えていきましょう。

今回は列数が少ないので自分で変えます。

ぽちぽちと変更していきます。

変更できたら1行目が不要になるので「行の削除」から「上位の行を削除」を選択して1行目を削除します。

１行目を削除したいので１を入れます。

良い感じの列名ができあがりました！

６　日付処理（プレフィックス）

次に日付の処理をします。

今のデータでは「年」、「月」、「日」が分かれていて日付として処理できません。

これを日付形式のデータに変更しましょう。

まず「年」が「21」なのが気になります。

そのようなときはプレフィックスを使って「2021」にしましょう。

プレフィックスを使うと値の前に特定の文字を追加することができます。

「変換」タブの「書式」から「プレフィックスの追加」を選択します。

「21」を「2021」にしたいので値に「20」と入れます。

年が「2021」になりました。

これで日付形式として利用できそうです。

日付形式のデータを作成するまえに「年」、「月」、「日」のデータ型を「整数型」にします。ここでデータ型を変更しないとあとでエラーになるので必ずデータ型を変更しましょう。

整数型に変更されました。

７　日付処理（列の追加）

それではこの「年」、「月」、「日」を使って日付形式の列を作成しましょう。

「列の追加」タブから「カスタム列」を選択します。

まず、列名を「宿泊開始日」にします。

次にカスタム列の式の欄で数式を入れます。

ここに「#date([年],[月],[日])」と入力します。

[年]・[月]・[日]については右側の使用できる列から選択します。

構文エラーが検出されていないことも確認しましょう。

これで宿泊開始日列が作成されました。

ここでもデータ型の変更を忘れずにやりましょう。（結構忘れがち💦）

日付型になりました。

８　インデックスを作成する

次にインデックスを作成します。

なぜインデックスが必要なのかというと、このデータでは泊数が１の場合と２以上場合があります。

例えば宿泊開始日が2021年11月14日で泊数が2の場合、宿泊日は2021年11月14日と2021年11月15日になります。

データとして2021年11月14日と2021年11月15日に分けた方が集計するには便利ですが同一の宿泊内容かわからなくなります。

そこでインデックスを付けて、宿泊内容を管理します。

インデックスの追加はとても簡単です。

「列の追加」タブの「インデックス列」から「1から」を選択します。今回は1から開始したいので「1から」を選びました。

インデックス列が追加されました。

早い・簡単・便利です。

列名も変更しましょう。

数式バー？で直接変更するとステップ数が増えないというメリットがあります。

列名が管理番号に変わりました。

９　不要な列を削除する

最後に不要な列を削除します。

必要な列を選択してから「ホーム」タブの「列の削除」から「他の列の削除」を選択します。

不要な列を削除できました。

「他の列の削除」をするときは、並べたい順に列を選択すると並び替えも同時にできるのでオススメです。

データの整形はこれで終わりです。

お疲れさまでした。

次はこのデータをもとにファクトテーブルを作ります。

ファクトテーブルって何？と思っても大丈夫ですよ！私もはじめて聞きました。一緒に学んでいきましょう！

つづく。