- 前回の記事
- Power Queryで他のブックからデータを取得する
- データソースに接続する
- データを読み込む
- 年齢列を追加するクエリを作ってみる
- 今日の日付列を追加してみる
- 年齢を求める
- どハマりポイント 大文字・小文字を区別する
- まだまだ関数のところは未知数
- 列の削除
- 終わりに
- M式言語で他のブックからデータを取得する
前回の記事
Power Queryで他のブックからデータを取得する
とにかく御託はいいから、どうやってデータを取得するんだよ！！
困ったときのバイブル、マイクロソフトの公式サイトに限ります。
他のブックからデータを取得する手順はこちらにありました。
例では、Power BI(MicrosoftのBIツール)ですが、Excelでも考え方は同じです。
実際に手を動かしてやってみることにしましょう。
データソースに接続する
データソースとして、単純なExcelファイルを用意しました。
test.xlsx Sheet1
このExcelファイルからPower Queryを使ってデータを取得してみます。
このExcelファイルとは別に新規のブックでPower Queryを使います。
新規のブックを開き、データタブ→データの取得→ファイルから→ブックから と選択します。
ファイルの選択ダイアログで先ほどのtest.xlsxを選択してみると・・
ナビゲーターが出てきて、シートの一覧、データのプレビューが表示されました。
ナビゲーターの編集ボタンをクリックすると、Power Query エディターが立ち上がります。
ここでデータを加工するわけですね。
特に加工が不要の場合は、「閉じて読み込む」をクリックすればデータが読み込まれます。
Power Queryという名前が表すように、この機能のキモはクエリです。
Power Queyはこの「クエリを効率的に作る機能」と言えると思います。
エディターの左ペインにクエリ一覧があります。
今は、デフォルトで作成されたSheet1というクエリだけが存在している状態です。
真ん中のペインには、選択されたクエリのデータプレビューが表示されています。
ここで自分が目的とするデータを抽出できるようにクエリを作成し、プレビューで実際に抽出されるデータを確認しながらクエリをブラッシュアップしていく・・・そんな流れでしょうか。
右側ペインは・・・この時点ではよくわかりません。
データを読み込む
作成したクエリをシートに読み込んでみましょう。といっても何も加工していませんが。
ファイルタブの「閉じて読み込む」をクリックすると、対象のファイルのデータがテーブルとして読み込まれました。
テーブルをアクティブにした状態でブックの上部のタブを見ると、クエリ実行前には存在しなかった「クエリ」タブが現れました。
クエリタブの編集をクリックすると、先ほどのPower Query エディターが表示されました。
編集のほかにもおもしろそうな項目がありますね。
試しに接続ファイルのエクスポートをクリックしてみると、デフォルトで「クエリ - Sheet1.odc」というファイル名で何かが保存されました。
何かよくわからないですが、保存されたファイルをメモ帳で見てみると、
中身はxmlファイルでした。
SQLコマンドや、javascriptが書いてあるのが確認できますが、細かいことはわかりません。わかり次第追記します。
他のブックからデータを読み込むという当初の目的は完了しました。
年齢列を追加するクエリを作ってみる
単純に他のブックからデータを読み込むのはとても簡単であることがわかりました。
では、今度は生のデータを読み込むだけではなく、少しデータを加工してみたいと思います。
生年月日の項目があるので、ここでは生年月日と今日の日付から年齢を追加してみたいと思います。
いきなり年齢列を追加するのはなんとなくハードルが高そうなので、１歩ずつ進めてみます。
まずは生年月日から年を抽出して新しい列として追加しみようと思います。
列を追加する方法
クエリタブ→編集をクリックしてPower Queryエディターを開きます。
新規で列を追加する場合は、Power Queryエディターの「列の追加」をクリックします。
すると、全般、テキストから、数値から、日付と時刻から の４つの選択肢があることがわかります。
今回は生年月日から「年」だけを抽出したいので、生年月日を選択した状態で「日付と時刻から」→「日付」と選択します。
すると、ありました、年。
これで日付から年だけが抽出できそうです。
年をクリックすると・・・
生年月日から年だけが抽出され、新たな列として作成されました！
今までは、わざわざワークシート関数で追加したりしていたのに、Power Queryを使えばそんなものは必要なくなりそうですね。
そして、ここで地味に右ペインにも注目。
「適用したステップ」というところに「×挿入された年」という項目が追加されています。
この欄には、これまで操作した内容が一覧で表示されています。
どのような操作をしたか、一目で確認することができます。
また、変更を取り消したいときは、「×挿入された年」の×をクリックすると、操作が取り消されます。
今日の日付列を追加してみる
さきほどの要領で今日の日付を追加してみたいと思います。
今日の日付はどのように求めるのか調べてみると、日付関数のページがありました。
このページの関数をこねくりまわせば今日の日付を取得できそうです。
カスタム列の式に下記のように書いてみます。
DateTime.Date(DateTime.LocalNow())
そうすると・・・
今日の日付が取得できました！！
Excel関数のように１つの関数では求まりませんでした。
他にいい方法があれば知りたいところです。
後でもっと調べてみることにします。
今のところはこれで良しとしましょう。
年齢を求める
今日の日付が求まったので、あと少しで年齢が求まりまそうです。
今日の日付と生年月日を使って、年齢は次の式で求めることができます。
((今日の日付-生年月日)/365)から小数の切り下げ
さて、日付の差はどうやって求めるのでしょうか・・・
色々探してみた結果、こちらの機能を使えばよさそう。
小数の切り下げについては、こちらの機能でイケそうです。
また、関数をこねくり回してみます。
Number.RoundDown(Duration.Days(DateTime.Date(DateTime.LocalNow())-[生年月日])/365)
これでイケました。
サクッと書いていますが、結構時間かかりました・・・。
どハマりポイント 大文字・小文字を区別する
Power Queryの式は大文字・小文字を区別するようで、Windowsの大文字・小文字の区別がない感覚で書くと、エラーが続出します。
マイクロソフトにしては、わかりやすいエラー内容ですね（白目）。
あと、どういう条件で出るのかまだわかっていませんが、
このエラーが続出しました。
エディターの挙動が不安定なんでしょうか。
それとも私の扱いが雑なんでしょうか。
これから解明していきたいと思います。
まだまだ関数のところは未知数
手探りでなんとか生年月日から今日時点の年齢を求め、新しい列として追加することができました。
まだまだ式の部分や、エラーの部分など、謎は多いですね。
しかし、式について見ると、たくさんの関数が用意されているので様々なことができそうですね。
列の削除
年の列を追加してみましたが、邪魔ですね。
ということでこの列を削除してみましょう。
列を削除するときは、Power Queryエディターで該当の列をアクティブにした状態で「ホーム」→「列の削除」をクリックします。
年の列が削除されて、右ペインの「適用したステップ」に「削除された列」というステップが追加されました。
終わりに
Power QueryでExcelの可能性がさらに広がりそうな予感。
次は、表の結合の方法について紹介しますので、お楽しみ（？）に。
ここまでお読みいただきありがとうございました。
M式言語で他のブックからデータを取得する
【Power Query M式言語】Excelワークシートのデータを取得する【Excel.Workbook関数】 - 適材適所
【Power Query M式言語】CSVファイルを読み込む方法について解説【Csv.Document関数】 - 適材適所
