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

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

相手はMask de keitaro先生。

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

画像18


内容はこちらです。

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

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

画像2


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

素敵ですね✨

画像1

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

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


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

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

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

画像3


2 データを取り込む

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

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

画像4


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

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

画像5


4 不要な列を削除する

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

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

画像6


5 ヘッダー処理

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


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

画像8


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

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

画像9


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

画像9


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

画像11

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

画像33


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

画像12


6 日付処理(プレフィックス)

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

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

画像13

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


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

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

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


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

画像14

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

画像15

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

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

画像17


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

画像18


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

画像19


7 日付処理(列の追加)

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

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

画像20


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

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

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

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

画像21


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

画像22


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

画像23


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

画像24


日付型になりました。

画像25


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

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

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

画像26

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

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

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


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

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

画像27


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

早い・簡単・便利です。

画像28


列名も変更しましょう。

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

画像29


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

画像30


9 不要な列を削除する

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

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

画像31


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

画像32

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


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

お疲れさまでした。

画像33

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

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


つづく。


この記事が気に入ったらサポートをしてみませんか?