Power BI - 26 - Power BI Desktop と SharePoint リスト

Power BI Desktop で SharePoint リストのデータを使用できるようにしてみる。以前から可能だったのだけど、そのままインポートするのもなんので クエリエディタ で加工してみた。

SharePoint リストについては "SharePoint リスト" / "SharePoint Online リスト" とふたつあるけど機能は同じ。

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。

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 サイトではうまく使えなかったりでした機能でしたが問題は概ね解決しているようです。

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

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

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 の場合、[データ]タブ - [取得と変換] - [新しいクエリ] - [ファイルから] - [フォルダーから]