2015/12/27

Power BI - 15 - Power Query テーブルの和結合

複数のクエリ結果のテーブルをひとつのテーブルに結合することについて。
データソースが複数になることはよくあるし、日ごとのログをまとめて集計するときなど和結合することになる。

Table.Combine(MSDN - Table.Combine)
Table.Combine(tables as list) as table
引数 'tables' は list なので、Table.Combine({table1, table2, ...}) という記述になる。
また、連結演算子(&) で和結合、table1 & table2 & ... でもよい。
let
    Table1 = #table(type table [F01 = text, F02 = Int64.Type], {{"a", 10}, {"b", 20}}),
    Table2 = #table(type table [F01 = text, F02 = text], {{"c", "30"}, {"d", "40"}}),
    Table3 = #table(type table [F01 = text, f02 = Int64.Type], {{"e", 50}, {"f", 60}}),
    Table4 = #table(type table [F01 = text, F03 = Int64.Type], {{"g", 70}, {"h", 80}})
in
    Table.Combine({Table1, Table2, Table3, Table4})
//    Table1 & Table2 & Table3 & Table4
であるとき、
データ型が異なってもカラム名に従い結合される。データ型の変換は別途必要なことがありえる。
カラム名はいわゆる Case-Sensitive。ただし、Power Query 上だけ。ロードしたときにエラーになるがロード先が Excel テーブルの場合はナンバリングが追加されるのでエラーにはならない。
結合されるテーブルすべてのカラムが追加されるが、値が存在しないレコードでの フィールドの値は null。

2015/12/02

Power BI - 14 - Power Query SharePoint.Tables

全く関係ないですが PowerApps のプレビューについてリリースさましたね。とても楽しみです。
いち早く試してみたい場合には、
Microsoft PowerApps: power your business with apps
からリクエストしてみてください。そのうち案内が届くと思います。

さて、今年も参加させてもらっている Office 365 Advent Calendar 2015 の 2日目です。
Power BI  を推している今年なので、引き続き Power Query + Office 365 についてポストします。

Power Query は様々なソースからデータを収集加工(ETL)することができるとても便利な道具なのですが、SQL Server などデータベース以外を簡単にデータソースにすることができます。その中でOffice 365 に関連したソースとして SharePoint リストを取り上げてみます。
ja-jp なSharePoint サイトではうまく使えなかったりでした機能でしたが問題は概ね解決しているようです。

2015/11/23

Power BI - 13 - Power Query Text.Format

Power Query の追加/更新された関数について
プレースホルダ と カルチャを指定できる文字列への変換。

Text.Format
Text.Format(formatString as text, argument as any, optional culture as nullable text) as text
引数 'argument' は、list もしくは record
プレースホルダは 引数 'argument' が list のとき、#{n}、record のとき、#[fieldname]
引数 'culture' は、Text.From と同じ

Power BI - 12 - Power Query Text.Middle vs Text.Range

Power Query の追加/更新された関数について
"Text.Middle vs Text.Range" としましたが、Text.Middle でいいじゃんという内容。いずれも引数は変わらず文字列の切り出しができるが、引数 'count' 範囲外の扱いが異なる。

Text.Range (MSDN - Text.Range)
Text.Range(text as nullable text, offset as number, optional count as nullable number) as nullable text

Text.Middle
Text.Middle(text as nullable text, start as number, optional count as nullable number) as nullable text

2015/11/14

Office Insider が始まりました。

Office Insider が始まりました。日本語サイトはまだなのだけど、ja-jp 環境であってもこの先に追加される Office の新しい機能などを試すことができる。
追加されていく機能は、
What's New and Improved in Office 2016 for Office 365
で公開されるものなのだろうけど、Current Branch(CB)より先にリリースされる FirstReleaseCurrent という更新スケジュールになる。対象は C2R のサブスクリプション製品ということだろう。

2015/11/08

Power BI - 11 - Zabbix API を Power Query で使ってみる

Web.Contents (Power Query) を使うとコンテンツのダウンロードができる。バイナリコンテンツを加工することで必要なデータにすることができるのだけど、POST が必要な Webサービスもあるので。
Web.Contents(url as text, optional options as nullable record) as binary
引数 "options" を使ってリクエスト時に Content などを渡すとよい。そこで、Zabbix を使うことがあったので、Power Query + Zabbix API を試してみた。なお、Zabbix API は JSON-RPC というプロトコルなので、JSON 形式のパラメータを送信 / JSON 形式で結果を受信する。Content-Type は application/json-rpc。

例えば、Zabbix API のバージョンを得るためのリクエストは、
{
    "jsonrpc": "2.0",
    "method": "apiinfo.version",
    "params": [],
    "id": 1
}
こんな感じなので、Power Query では
let
    ZabbixServer = "http://Server/zabbix/api_jsonrpc.php",
    RequerstJSON = Text.Format(
         "{""jsonrpc"":""2.0"",""method"":""apiinfo.version"",""id"":#{0}}",
         {Number.Round(Number.RandomBetween(1,9999))}
    ),
    Options = [Headers = [#"Content-Type" = "application/json-rpc"],
               Content = Text.ToBinary(RequerstJSON)],
    Response = Web.Contents(ZabbixServer, Options),
    ImportedJSON = try Json.Document(Response),
    Result = if ImportedJSON[HasError] 
             then ImportedJSON[Error]
             else if ImportedJSON[Value][id] = null 
                  then ImportedJSON[Value][error]
                  else ImportedJSON[Value]
in
    Result

2015/10/13

Power BI - 10 - Power Query の リファレンス

Power BI Desktop や Excel で使用できる Power Query のリファレンス
MSDN:Power Query (informally known as "M") Formula reference
メインはこれでよいかと。 ただし、日本語訳はまだないのでこんな感じで見ることはできる。

空のクエリなど作成し、shared を参照すればよい。少し内容が怪しいけれどもどんなのあるかなぁって感じでも全く問題ない。

Power BI - 9 - 複数のExcel ファイルをインポートする

複数の Excel ファイルから必要なデータだけを Power BI Desktop にインポートしてみる。
Excel.Workbook(Power Query) の引数/binary に File.Contents(Power Query) で読み込んだExcel ファイルを渡すことで解析され読み込みできるようになるのだけど、


まずはこんな感じになる。"名前付き範囲"が取得できているので、ドキュメントによっては印刷範囲も取れます。また、Book / Sheet 範囲の名前付き範囲が同じ名前の場合リネームされてしまうから使う前にはよく考慮しておきたい。
シートをまるっと読み込んでもよいのだけど、加工に手間が掛かる傾向にあるかな。 そして、ナビゲーターでは非表示のシートと非表示のシートにあるテーブルは表示されないけど読み込むことは可能。

Power BI - 8 - ログファイルなどをまとめてインポートする

Power BI Desktop に ログファイルなどのシーケンシャルファイルをまとめてインポートすることが可能。
 ログファイルは テキスト形式 で CSV などにフォーマットされているから、複数のログファイルのETLを一気に実施してしまってもよい場合が多い。ヘッダが固定されていれば、Binary.Combine で結合後 Table.SplitColumn で分割すればよい。

Folder.Files なのだけど、リボンからポチポチすれば事足りるので、
[ホーム]タブ - [データを取得 / 新しいソース] - [さらに表示]

Excel 2016 の場合、[データ]タブ - [取得と変換] - [新しいクエリ] - [ファイルから] - [フォルダーから]

2015/09/23

Power BI - 7 - Excel 2016 のBI 関連する機能

Excel 2016 のPower BI に関連する機能、リリース前の情報は、
New ways to get the Excel business analytics features you need
の通りで、
  • Power Pivot for Excel
  • [データ] タブ - [取得と変換] グループに追加された機能
について。

2015/09/22

Office 2016 The new Office is here

新しい Office、Office 2016 の リリース。結局、日付をまたぐことなく 9/22。
The new Office is here

What's new in Access 2016

時差の都合上日本時間で明日 9/23 に Office 2016 がリリースされるわけだが、Access 2016 に関するソースの先出し。
What's new in Access 2016
次期リリースの SharePoint Server で使用できる Access アプリ / Access Services の機能が現在のSherePoint Online のものと同等になるようになること以外に大きな機能追加はない。

2015/09/11

Office 2016 は9/22 からロールアウト

Admins—get ready for Office 2016, rollout begins September 22!


楽しみだな。

更新モデルは、Current Branch と Current Branch for Business があるそうです。お仕事でOffice を展開して使っている管理者の人は要チェックです。BITS、多要素認証やDLP(Data Loss Prevention)のサポートもね。

Prepare to update Office 365 ProPlus to the Office 2016 version
Overview of update branches for Office 365 ProPlus

Note: For Office 365 Personal and Office 365 Home users, you can start installing 2016 apps on September 22nd from office.com, and automatic updates will begin rolling out in October—so this new tri-annual update model is purely for our organizational subscribers to Office 365 ProPlus.
個人向けSKUは9/22からOffice.comを経由してOffice 2016 のインストールが可能。で、10月から自動更新は開始ということか? 常に新しい Office が使える。というはずだから、まぁそうなんだろう。

2015/08/27

SharePoint Server 2016 IT Preview と Access Services

SharePoint Server 2016 IT Preview が公開されたので、Access Services がどうなったのかなと試してみた。
もろもろの情報は、
SharePoint Server 2016 IT Preview
を参照してインストール。Access Services を有効かするには、以前の手順 通りで完了。
見た目で違いはないね。

2015/08/17

Power BI - 6 - ダッシュボードを共有する

手早く共有ができてよい感じかと。
まぁ、Share (and unshare) a dashboard from Power BI にある通りなのだけど、ちょっと確認したいことがあったので。

[ダッシュボードの共有] もしくは、メニューの[共有] の押下

2015/08/16

Power BI - 5 - 独自ドメインで使い始めてみる

いまさらだけど、Power BI を使えるようにしてみた。


ビジネス向け Office 365 などを使用している場合、すでに Power BI が利用できるのだけど、
新規に "新しいPower BI" だけ
を使えるようにしてみたということ。

2015/08/02

Power BI - 4 - OneDrive 上のファイルを更新してデータセットの更新をしてみる

サイトドキュメントの参照に使えるかなと。 

OneDrive 上のファイル(*.xlsx)をソースしたデータセットを更新してみる。
OneDrive 上のファイルを直接インポートした場合、

Refresh Excel, CSV, and Power BI Desktop files stored in OneDrive

にある通り、[今すぐ更新]を実行するか、1時間に1回の自動更新でソースの更新を取得することになる。更新のスケジュールができないというだけ。これで充分なはずだけれども。。。

2015/07/30

Power BI - 3 - OneDrive 上のファイルをソースにしてみる

OneDrive for Business に保存したファイル(*.xlsx)をソースとしてインポートしてみる。
Office 365 を使っているなら OD4B 使っているでしょうし、データセットの更新ができるので手軽でよいかなと。
[データの取得] - [データのインポートまたは接続] - [ファイル]

2015/07/26

Power BI - 2 - Excel ファイルをソースにして試してみる

ローカルのExcel ファイル(*.xlsx)のファイルをソースとして Power BI にインポートしてみる。
直接インポートすることになるのだけど、あらかじめの データ整形が必要なことがわかった。

まずは、[データの取得] - [データのインポートまたは接続] - [ファイル]

Power BI - 1 - GA ですよ

Power BI is Generally Available today
Power BI がリリースされたので使ってみる。

2015/07/08

Access 2016 Preview 16.0.4229.1002

タイミング良くなかったか、矢継ぎ早になってしまった。
パブリック プレビューで比較的大幅に更新がきている。機能が追加になることではないので仕上げに近いなんだろうか。Access はそんなに更新がないからよいけど、他製品についてはプレビューを試しておいたほうがよいだろうな。

2015/07/06

Access 2016 プレビュー

ぼちぼちとリリースに近づいてきているのかなと。Access 独自の新機能は見当たらないけれども。
Office 365 ProPlus でインストール
 SKUによっては表示されるアイコンは異なる。

2015/07/02

2015 Microsoft MVP アワード に表彰いただいた。

Microsoft MVP アワード に表彰していただけました。

忙しいことを理由に フォーラムやブログへのポストをかなり怠けていたのですが、勉強会でお話しさせてもらったりとか多くの機会に恵まれ、とても楽しく過ごすことができました。感謝しきれない気持ちです。本当にありがとうございました。

引き続き、よろしくお願いいたします。また1年間を楽しみたいと思います。

2015/06/10

Office 2013 の更新ができないとき

多くの場合、月1回 Click-to-Run / クイック実行の Office の更新が実施できるのだけど、
June 2015 Office Update Release
情報が公開されてからも更新が実施できないことがある。他にも原因はあるだろうけど、CDN に依存する件。

現在、製品の更新に使用されているリポジトリは、
http://officecdn.microsoft.com/pr/39168d7e-077b-48e7-872c-b232c3e72675/Office/Data

ここに配置された v32.cab もしくは v64.cab をダウンロードし、更新できるバージョンがあるかどうか確認していて、v32.cab / v64.cab が取得できても更新されていない状態になることがある。この時、 Office の更新は実行されない。
"今すぐ更新"を実行しても更新されない

http://officecdn.microsoft.com/pr/39168d7e-077b-48e7-872c-b232c3e72675/Office/Data/v32_15.0.xxxx.yyyy.cab
はダウンロードできるのだけど。。。

2015/05/17

第11回 Office 365 勉強会 にお邪魔してきた。

第11回 Office 365 勉強会 Office365新任管理者さん いらっしゃ~い にお邪魔してきた。
勉強会の内容はもちろんだけど、雰囲気がよくて おやつ がいいよね。

テーマが新任管理者さんいらっしゃ~いだったので、 LT(マルウェアの話) をさせてもらったのだけど、補足ポストしておきたい。時間配分が下手なんですね。
さて、

2015/03/17

Access 2016 Preview

プレビューが来ました。が、Access に変更はないはず。
目玉は、Excel の機能強化と DLP とかじゃないかな。
Office Blogs : Announcing the Office 2016 IT Pro and Developer Preview
Flexible update management. We’ve created a way for admins to manage the pace at which they receive feature updates and bug fixes while continuing to receive regular security updates.
というのもポイント。機能の更新とセキュリティの更新の管理が個別にできるということかな。
IT プロフェッショナルおよび開発者向けの Office 2016 プレビュー を発表
柔軟な更新プログラム管理: セキュリティ更新プログラムを定期受信するように設定したまま、機能の更新や不具合の修正の受信ペースを管理者が管理できるようになります。