適材適所

WindowsやPowerShellやネットワーク、IBMなどのシステム系の話やポイ活など気になったことも載せているブログです。

【Excel Power Query入門】Power Queryを使うと何ができるのか

Excelを使った外部データへの接続を強力にアシストしてくれるような気がするPower Query。

Power Queryについて全くの初心者である私が、PowerQueryについて調べながら、道なき道を進み、これからPower Queryについて学ぼうとする人の少しでも役に立つように道を作っていこうと思います。

本当にPower Queryについて全くの初心者なので、自分が調べて手を動かして理解したてほやほやのことを書いていきます。

そのため、これから学ぼうとする人もすんなり入っていける!!かも。

それでは、いざ、Power Queryの奥深き道へ!!

Power Queryとはなんなのか

Power Queryとは何でしょうか。

Excel 2019で他のブックへ接続するマクロを記録をすると、この機能が使われていることが確認できます。

デスクトップにあるtest.xlsxに接続する作業を記録してみると・・・。

f:id:shinmai_papa:20200907202959p:plain

Sub Macro1()
'
ActiveWorkbook.Queries.Add Name:="Sheet1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & "ソース = Excel.Workbook(File.Contents(""C:\Users\USER\Desktop\test.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "Sheet1_Sheet = ソース{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "変更された型 = Table.TransformColumnTypes(Sheet1_Sheet,{{""Column1"", type text}, {""Column2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "変更された型"
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Sheet1;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Sheet1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Sheet1"
.Refresh BackgroundQuery:=False
End With
Range("B2").Select
End Sub

結果

f:id:shinmai_papa:20200907203004p:plain

何が何だかわからないのですが、これで一瞬で他のブックのデータを取得することができました。

どうやら他のブックやCSVといった他の資源から簡単にデータを取得できる機能のようです。

ここでマイクロソフトのドキュメントを見てみます。

日本語のページのはずですが、日本語ではなく、英語です。

Google翻訳によれば、

Power Queryは、データ変換およびデータ準備エンジンです。Power Queryには、ソースからデータを取得するためのグラフィカルインターフェイスと、変換を適用するためのPower Queryエディターが付属しています。エンジンは多くの製品やサービスで利用できるため、データの保存先は、Power Queryが使用された場所によって異なります。Power Queryを使用すると、データの抽出、変換、および読み込み(ETL)処理を実行できます。

とあります。

「Power Queryは、データ変換、データ準備エンジン」で

「Power Queryを使用すると、データの抽出、変換、および読み込み(ETL)処理を実行できます。」とのことです。

これって、超便利じゃね??

つまりSQL的なことができちゃうってことだよね?

これさえマスターできれば、捗りまくる予感がすごいですね。

Power Queryがどのように役立つのか

先述のマイクロソフトのドキュメントにはこうあります。

ビジネスユーザーは、時間の最大80%をデータの準備に費やし、分析と意思決定の時間を遅らせます。この状況を引き起こす多くの課題があり、Power Queryはそれらの多くに対処するのに役立ちます。

まじか。

こんなにデータの準備に時間を費やしてたのか。

知らなかった。

じゃあ、Poewr Queryを使えば最大で80%の無駄な時間を減らせるってことか!!

f:id:shinmai_papa:20200910083135p:plain
さすが!

Power Queryはどのように使うのか

Power Queryは専用のエディターが用意されており、グラフィカルに使うことができるようです。

そしてエディターで作った変換は、「 Power Query式言語(M言語)」という専用の言語に自動で変換されます。

M言語なのでMの人かSの人しか理解できないなんてことはないのでご安心を。

どっちかというとMなのでM言語、なんとかなりそうです。

おっと、話がそれてしまった。

グラフィカルではなく、バッチ的にプログラムで使う場合は、このM言語を理解する必要がありそうです。

Power Queryをマスターするとどうなるのか

ここまで見てきたように、Power Queryをマスターすれば、他の資源へ効率的にアクセスできそうです。

Excelの可能性を大きく広げる機能であることは間違いありません。

しかし、そこにたどり着くためには、

1 グラフィカルUI
2 M言語

の2つをしっかり理解する必要がありそうです。

何を理解すればいいかわかったところで、導入はこれくらいにして次回から、Power Queryについてマイクロソフトのドキュメントを参考にしつつ、Power Queryの世界へ入って行こうと思いますので、お付き合いいただればと思います。

終わりに

さて、Power Queryの連載?を今回から始めていきますが、どのくらい続くのか、どんな話が出てくるのか、私にも全くわかりません。

途中、いろんな脱線もあり、また、気まぐれの不定期で気楽にやっていきたいと思います。

では、ここまでお読みいただき、ありがとうございました。

Power Query・・・?な関連記事

www.tekizai.net

www.tekizai.net

www.tekizai.net

www.tekizai.net