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

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

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

Power BI - 25 - Power BI Desktop で タイム インテリジェンス関数

タイム インテリジェント関数に必要な日付テーブルとそのリレーションを準備したので実際に使ってみる。

スキーマはこんな感じで、最終的には "受注額" の集計についていろいろと。

Power BI - 24 - Power BI Desktop と 日付テーブル

タイム インテリジェンス関数を使いたいです。なぜなら、期間(年、月、前年とか)を伴う比較集計にとても便利だから。

MSDN : タイム インテリジェンス関数 (DAX)
Office.com : Power Pivot in Excel のタイム インテリジェンス

ファクトテーブル"受注"の "受注日" と "締切日" は 受注と請求に関連する日付とみなしていくつかの集計を行えるようにしてみる。

ここで必要になるのは 日付テーブル。なので、データモデルにテーブル"カレンダー"を追加する。

Power BI - 22 - Power BI Desktop と Access アプリ

データソースから得ることができない情報はデータモデルに追加すればよい。データソースにディメンジョン テーブルとなりえるデータを用意し一緒に抽出 / Extract してもよいのだけど、セルフサービスBI なのだからレポートに必要なデータを自分で持ち込んでもよいのである。運用フェーズに移行しているアプリケーションをできれば更新したくないこともあるし。

テーブル:得意先 の"都道府県"を地理的エリアのスライサに利用できているのだけど、

• 得意先が存在しない地域の集計
• "都道府県"別以外のスライサ

が実現できないのでディメンジョン テーブルを追加し対応する。

Power BI - 21 - Power BI Desktop と Access アプリ

Power BI Desktop だけを使用するだけなら Power BI Service のアカウントやサインインは不要なのだけど、集計レポートを共有をしようとするとき便利だし、Access アプリ(Access Services) をデータソースにしているのだから、Power BI のサブスクリプションを所有しライセンスが割り当てられているいることを前提として Power BI Service への発行 / Publish をしてみる。

発行

Power BI Desktop からの発行

Power BI Service  で[データの取得] をする場合、

• Excel、Power BI Desktop、CSV ファイルから Power BI へのデータの取得
• Power BI 内で Excel、Power BI Desktop、CSV ファイルを置き換える 

Power BI - 20 - Power BI Desktop と Access アプリ

受注結果が記録されている"受注"テーブルで ファクト テーブル。これから受注額などを計算する。

テーブル '受注' には、

• [単価] --- 受注商品の単価
• [数量] --- 受注商品の数量
• [割引] --- 受注明細ごとの割引率
• [受注コード]  --- 受注ごとのユニークキー

を残しているので、明細行ごとの集計額が [単価] * [数量] * (1 - [割引]) になり、[受注コード] ごとに集計額を集計(合計)したものが、受注ごとの受注額に相当する。値を求めるには、

• 新しい列(計算列)を追加する
• 新しいメジャーを追加する

のいずれか。

Power BI - 19 - Power BI Desktop と Access アプリ

Power BI のレポートに使用するために下ごしらえをしてみる。
インポートで作成されたデフォルトのデータモデルで作業することは不可能ではないのだけど、適した状態とはいえないから、使用しない/不要なカラムの排除やスキーマの変換を実施する。
Power BI Desktopでのレポート作業では問題が発生しなくても、Power BI Serviceにインポートしたとき、満足できるパフォーマンスや期待する結果が得られないかもしれない。

ETLのT(Transform/変換)の作業を実施しないと、テーブルに存在するカラムすべてをインポートしてしまう。社員の誕生日などは受注に関するBIには不要であることは明白。また、第3正規化はリレーショナルデータベースに用いられていること多いが、BIでは適していない状態。

Power BI - 18 - Power BI Desktop と Access アプリ

Power BI Service(直接接続)だとデータ加工の程度に限界があるので、Power BI Desktop を使ってみる。Access アプリに専用のクエリなど用意すればできなくはないけれども、他データソースとの結合などもしたいこともあるでしょうし、Power BI Service にアップロードするにしても Power BI Pro の契約は不要なので。

まずはデータのロードまで。

SharePoint Online で実行する Access アプリのデータは、Microsoft Azure SQL データベースなので、[データの取得]では、"Microsoft Azure SQL Database"で接続。事前にAccess アプリの[接続]でファイアウォールを通るようにしておくことは当然に必要。

Power BI - 16 - Power BI Desktop データの入力

データソースなどから抽出することなくデータテーブルを新規に作成することができる。
2015/12 リリースバージョン(2.30.4246.281)からの機能

• データの入力/Entry Data コマンド
• DATATABLE 関数(ひとつ前の2.29.4217.341 から)

データの入力/Entry Data コマンド
データソースがなくてもよいのでちょっとした分析作業やほとんど更新されることがないデータの投入としたら便利かと思う。

[ホーム] → [外部データ] → [データの入力] で "テーブルの作成"ダイアログが開くので、ワークシートからコピペするもよし、手投入するもよし。

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