梗概
これまで扱ったデータは1年分で二万三千数百行で、3年分で7万行弱である。これをいちいち手作業で行うのは、大変な手間がかかる。
ここでは、パワークエリとExcelを連携することによって、データ作成の作業が効率的になる手順を示す。
データの形式はExcelとPower BIともに、リスト形式でなければならない。たとえ、Excelでピボットテーブルを作成して、クロス集計表に直すとしても、取り込むデータ形式は必ずリスト形式にすること。
なお、ExcelとPower BIのパワークエリは共通である。
本体の作成 Excelのテーブル機能の活用
本体はsheet1、パワークエリのマージ用にsheet2を作成
sheet1とsheet2をテーブル表示にする。セルの1つを左クリックして、Ctrlキー＋ｔを押す。
先頭行をテーブルの見出しとして使用するにチェックを入れてOKを押す。
sheet1の例
商品の隣に個数と入力すれば、自動的にテーブルに取り入れられる。
個数をテーブルに取り入れたところ
個数の最初のセルに上記の乱数を入力する
乱数が自動的に各セルに入力される
ここで、乱数の値は数式のままになるので、Shift＋Ctrlキー＋↓でセルを選択し、コピーをしてから値貼り付けを行う。一度、Bookを上書き保存をする。
sheet2に移り、同様にCtrlキー＋ｔでテーブルにする。
先頭行をテーブルの見出しとして使用するにチェックを入れてOKを押す。
sheet2のテーブル表示
データの取り込み パワークエリの活用
Excelを使い、この2つをパワークエリに取り込む。
Excelメニュー＞データの取得＞ファイルから＞Excelブックから を選択して左クリック
自身が作ったExcelブックを指定する。ここではほか弁コロナData用Ver1とした。
選択して左クリックする
パワークエリが立ち上がり、このような画面がでる
複数アイテムの選択にチェックを入れ、sheet1、sheet2にチェックを入れて、
データの変換をクリックする
sheet1を左クリックして、商品を左クリックする
クエリのマージ
クエリのマージを左クリックする
この画面の真ん中の空欄の右端の矢印を左クリックする
sheet2を左クリックする
この画面のsheet1の商品と、sheet2の商品を左クリックする
一番下の選択範囲では……で行数が一致していることを確認したら、OKを押す
この画面のsheet2の右端にある展開マークを左クリックする
商品と元の列名……のチェックを外して、OKを押す
この画面がでたら、一度Excelに落とす
閉じて読み込むの下半分を左クリックし、閉じて次に読み込むを左クリックする
この画面がでたらOKを押す
クエリと接続の×印を左クリックして消し、sheet1（２）を左クリックする
この画面の右端（分類の横）に売上と入力する
売上の下のセルに図の式を入力する。個数と定価はセルをクリック。ほかは手入力。
自動的にすべての列に入る。
Shift＋Ctrlキー＋↓で選択して、コピーをして値貼り付けをする。
出来上がったデータをテキストファイルに落とす。名前をつけて保存をクリックして任意の名前をつける
ここでは、名前をBook1テキストとして、テキストを選んで保存する
この画面は無視してもよいので、OKを押す
出来上がったテキストファイル。メモ帳にて確認。これが本体のデータとなる
本体を取り込むときには、必ずフォルダー接続にしなければならない。1回限りの扱いであれば、本体自身を指定してもいいが、年ごとにデータを積み重ねて更新をかける場合にはフォルダー接続をして、そこに新しいデータを放り込み更新をかければ、グラフは自動的に変化する。
1回限りであろうがなかろうが、普段からフォルダー接続をする習慣をつけて欲しい。
いま作成したBook1テキスト.txtをBookというフォルダに入れる。
新しいExcelを立ち上げて、フォルダからを選択
Bookを選択して開くをクリック
この画面がでたら結合をクリック
データの結合と変換をクリック
この画面がでたらOKを押す
Source.Nameは要らないので、列の削除の下半分をクリックする
列の削除をクリック
各列の表示、年月日はカレンダー、商品と分類はABC、残りは123の整数となっていることを確認する
Shiftを押しながら売上をクリックすると、すべて選択の状態になる
ヘッダーを1行目として使用をクリックする
この状態になったら、行の下半分をクリックする
空白行の削除とエラーの削除を順にクリックする
Power BIメニューの変換をクリックして、書式のトリミングとクリーンを順にクリックする
Power BIメニューのホームへ戻り、1行目をヘッダーとして使用、をクリックする
これらの操作により、本体のデータはクリーンなものになる。
本体からデータの切り出し
Bookを右クリックして、名前の変更を行う。ここではfほか弁コロナDataとした
fほか弁コロナDataを右クリックして、参照を左クリックする
このようになるので、新しくできたfほか弁コロナDataを右クリックして
商品という名前に変える
商品を左クリックして、Power BIメニューの列の削除の下半分をクリックし、
他の列の削除をクリックする
Power BIメニューの行の削除の重複の削除を左クリックする
データはこのようになる。
商品を右クリックして、複製を左クリックする
商品（２）を右クリックして、名前の変更を左クリックし、分類とする
適用したステップの削除された他の列の歯車を左クリックする
商品のチェックを外して、分類にチェックを入れて、OKを押す
左はデータ列、右は適用したステップを表す
適用したステップの削除された重複を左クリックすると、分類が右のようになる
3つのクエリが出来上がる
閉じて読み込むの下半分をクリックして、閉じて次に読み込むをクリックする
接続の作成のみにチェックを入れ、このデータをデータ モデルに追加する
にチェックを入れて、OKを押す
この画面は邪魔なので、左上の×印で消す
Excelのリレーションの組み方
Power Pivot ＞ 管理 を左クリックする
Power Pivotメニューの表示の項にあるダイヤグラムビューを左クリックする
この画面上で、商品は本体の商品へ、分類は本体の分類へドラッグする
このようにリレーションが組まれればOK。×印で閉じる
本体の取り込み Power Pivot編
Power BIを立ち上げると図のような画面が出てくる
ここで、左端の一番上のデータを取得をクリックしてもいいし、あるいは×印をクリックしてこの画面を削除してもよい。
データを取得をクリックした場合
フォルダーを左クリックして、接続を左クリック
フォルダパスを手入力してもいいし、参照をクリックして場所を指定してもいい
立ち上がりの画面を消した場合
Power BIメニューのデータを取得をクリック
データを取得の下半分をクリックして、詳細をクリックする
フォルダーをクリックして、接続をクリックする
フォルダパスを手入力してもいいし、参照から選んでもよい。選択できたらOKを押す
この画面がでたら、結合を押し、データの結合と変換を押す
この画面がでたら、OKを押す
ここで、Excelを用いて解説したパワークエリの画面が出てこなければ、次のようにすればいい。
変更の適用をクリックする
画面右端のフィールドにある、取り込んだBookを左クリックして、Power BIメニューのホームを選択する。
データの変換の下半分をクリックして、データの変換をクリックすれば、あとはExcelの場合と同様の手順でデータの切り出しができる。さらにPower BIの場合は、切り出しの場合のみ自動的にリレーションが組まれる。
本体からの切り出しと、自動的に作成されたリレーション
切り出しの確認が終わったら、閉じて適用の下半分をクリックして、
閉じて適用をクリックする
画面右端のフィールドに本体と切り出しの名前があることを確認する。また年度テーブルを作成した場合は、必ず手動でリレーションを組むこと。
以上で、パワークエリとExcel、Power BIによるデータの作成方法は終わりである。
クロス集計表（これを横持ちデータという）をリスト形式（これを縦持ちデータという）に変換したい場合は、パワークエリのピボットの解除を行えばよいが、詳述は割愛する。理由はほとんどといっていいくらい、使わないからである。
確かにクロス集計表はグラフ化のときに、商品伸び率を計算する場合に使用したのだが、そのクロス集計表も本体のデータに取り込んだわけではない。
サイトご利用方法
次のページ・前のページを利用するよりも、グローバルメニュー（ヘッダー部分にある項目）をクリックしていただければ、その項目の全体像が一目でみることができ、クリックすればそのサイトへ飛びます。
google、yahoo、Bingなどで検索する場合、検索ワードは先頭に、孤立じじい、と入力しその後に、ダッシュボード or インテリア or 統計 or 談話室、とどれかひとつを入力すると、その検索サイトが上位表示されます。
