Power BI レストラン売上の可視化・分析 Power Query編

内容

Japan Power Platform Community Caravan in 佐賀内のPower BI ハンズオンの操作手順書になります。

使用データ　

　北海道オープンデータにあるレストラン売上データを元に、Power BI Desktopを使用した可視化・分析を行います。

レストラン売上データ（北海道オープンデータ）

Power BI Desktopインストールと初期設定

　Power BI Desktopのインストール

Power BI Desktopのインストールを行います。下記のURLにアクセスしたら、「高度なダウンロードオプション」を選択します。
Power BI Desktopのダウンロード画面が表示されるので、言語が「日本語」になっていることを確認し、「PBIDesktopSetup_x64.e4xe」にチェックをつけダウンロードボタンをクリックします。
ダウンロードしたセットアップファイルを実行します。

Power BI Desktopの初期設定

Power BI Desktopを起動後、

オプションの設定

タイムインテリジェンスの設定

「データの読み込み」のタイムインテリジェンス項目にある、「新しいファイル自動の日付／時刻」のチェックを外す。

　
理由としては、Power Queryを使用して日付テーブルを生成するためになります。

日付テーブルを追加する手段

プレビュー機能設定

　プレビュー機能で機能ごとにON・OFFの設定が可能になってます。全てチェックをつけて機能を確認しても問題ありませんが、今回は下記の項目がチェックがついていることを確認します。

「フィールドパラメーター」
「オブジェクト上での対話式操作」
「視覚的な計算」

Power Queryエディタ起動とベストプラクティス

Power Queryエディタの起動

　ホームタブのクエリ内の「データの変換」若しくは、ホームタブの「データの変換」にてPower Queryエディタ画面が起動します。

　使用しているパソコンの解像度によって、位置が異なることもあります。

Power Queryを使用するときのベストプラクティスについて

Power Queryを使ってデータ加工やデータクレンジングを行う際に、最も効率的に利用で出来るようベストプラクティスが設けられています。
Power Queryにて作業を行う際は、ベストプラティクスのルールに従って操作を行います。

データ読込・パラメーターの設定

パラメーター設定画面起動

　データソース先が変わっても、変更が容易なようにファイルパスのパラメーターを設定します。
ホームタブのパラメーターの管理をクリックします。

パラーメーター設定

　新規作成をクリックし名前にパラメーター名を入力、種類をテキストにします。
　

現在値は、今回使用するオープンデータのページに「URLをコピー」したものを貼り付けます。

データ(CSV)を参照する

ホームタグの「新しいソース」をクリックします。

様々なデータソースが表示されるので、今回はWeb上にあるCSVファイルを参照するので、「その他」のWebを選択します。

URLの部分をクリックし、パラメーターを選択すると、「パラメーター設定」で設定した名前が表示されますので、「ＯＫ」ボタンをクリックし、取込画面が表示されるので取込する内容に相違がなければ「ＯＫ」ボタンをクリックします。

データ読込を確認とテーブル名変更

摘要したステップのSource部分をクリックすると、最初にWeb.Contents関数でパラーメーター設定したファイルパスを参照し、Csv.Document関数でCSVファイルとして読み込まれていることが分かります。

テーブル名がパラメーター設定した名前になっているので、テーブル名をSalesに変更します。

データモデリング

スタースキーマとは

完成予定のデータモデリング

下図のようなデータモデリングを目指して、Power Queryを使ってデータクレンジング・加工処理を行います。

データクレンジング（Power Query)

まず最初にデータを見る

フィルタの適用

モバイルオーダー特典　ソフトドリンク無料を対象外にする

　レストラン売上データ内に、「モバイルオーダー特典　ソフトドリンク無料」があり、その後に飲料データがあり金額も０となっています。

フィルタにてモバイルオーダー特典の商品は条件からはずします。商品コードが「99999」のチェックを外します。

フィルタを適用した理由をコメントで入力

なぜフィルタにて除外した理由をコメントで入力することにより、変更があった場合に内容を振り返ることができます。

同様に決済ステイタス

　同様に決済ステイタスで、「注文キャンセル」と「代金のみ払い戻し」を除外します。

不要な列の削除

　今回の分析で使用しない項目は列の削除で削除します。
今回は「商品ID」、「ステイタス」、「注文時の経路」の列を削除します。

商品マスタ(Product)テーブル

テーブルの複製とテーブル名変更

Productテーブルを作成するため、Salesテーブルから複製を行います。
クエリのSalesテーブルで、右クリックで複製をクリックします。
複製したテーブル名を「Product」に変更します。

不要な列の削除

重複の削除

列の追加　区切り記号の前のテキスト表示

竹鶴ピュアモルトであっても、竹鶴ピュアモルト(シングル)(ソーダ)のように複数商品があった場合、竹鶴ピュアモルトで実績を見る場合、区切り記号のテキスト表示にて、「(」以前の文字を表示し、竹鶴ピュアモルト単体での実績を分析することもできます。

決済方法テーブル

商品マスタと同様に行います。

言語マスタ(Launguage)テーブル

ホームタグのデータの入力をクリックします。
言語、言語名を入力しテーブルを作成することも可能です。

Salesテーブル

不要な列を削除

Salesテーブルで、Productとリレーションキーとなる商品コード以外、列削除を行います。

決済日時を日付と時刻に分ける

列の追加で、時刻を選択し時刻のみをクリックします。

決済日時を日付型に変更と列名を変更する

時間帯(Time)テーブル

空のクエリ

List.Times(#time(9,0,0),12 * 60, #duration(0,0,1,0))

時間単位

日付テーブル(Datetbl)作成

日付テーブルを作成します。時間の都合上、空のクエリから2022年3月1日～2023年2月28日の日付テーブルを作成するクエリを作成したのでコピーします。

レストランの売上データは1年未満ですが、日付テーブルの条件として１年以上の日付の作成が条件となっているので365日としています。

let  
  source = List.Dates(#date(2022, 03, 01), 365, #duration(1, 0, 0, 0)),  
  convert_table = Table.FromList(source, Splitter.SplitByNothing(),{"Date"},ExtraValues.Error),
  Changed_Type = Table.TransformColumnTypes(convert_table, {{"Date", type date}}),
  追加_year_数値 = Table.AddColumn(Changed_Type, "年", each Date.Year([Date]), Int64.Type),
  追加_month_数値 = Table.AddColumn(追加_year_数値, "月", each Date.Month([Date]), Int64.Type),
  追加_day_数値 = Table.AddColumn(追加_month_数値, "日", each Date.Day([Date]), Int64.Type),
  
  // 3月が決算月
  追加_年度_数値 = Table.AddColumn(追加_day_数値, "年度", each if Date.Month([Date]) > 3
        then Date.Year([Date])
        else Date.Year([Date])-1, Int64.Type),
  追加_週_数値 = Table.AddColumn(追加_年度_数値, "週", each Date.WeekOfYear([Date], 1), Int64.Type),
  
  // 0:日曜日～6:土曜日
  追加_曜日_数値 = Table.AddColumn(追加_週_数値, "曜日", each Date.DayOfWeek([Date], 0), Int64.Type),
  追加_曜日名_文字 = Table.AddColumn(追加_曜日_数値, "曜日名", each Date.DayOfWeekName([Date], "ja-JP"), type text),
  追加_月名_文字 = Table.AddColumn(追加_曜日名_文字, "月名", each Date.MonthName([Date]), type text),
  追加_年月_文字 = Table.TransformColumnTypes(Table.AddColumn(追加_月名_文字, "年月", each Text.Combine({Text.From([年], "ja-jp"), "年", [月名]})), {{"年月", type text}}),
  追加_年月_数値 = Table.AddColumn(追加_年月_文字, "年月_数値", each Date.Year([Date]) *100 + Date.Month([Date]),Int64.Type),
  追加_年名_文字 = Table.AddColumn(追加_年月_数値, "年名", each Date.ToText([Date],"yyyy年","ja-jp"),Text.Type),
    追加_年度_文字 = Table.AddColumn(追加_年名_文字, "年度名", each Text.Combine({Text.From([年度]),"年度"}),Text.Type)
in
    追加_年度_文字

日付テーブル

Power Queryエディタを閉じる

日付テーブル以外はリレーションが繋がっていることを確認

　日付テーブル以外は、下図のようにSalesテーブルにリレーションが繋がっていることを確認

次回

データモデリングとメジャー作成・ビジュアライズになります。