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

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

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

SharePoint リストが配置された SharePoint サイトのURLを投入。ルートサイトではなく"サイトのルート URL"。

カスタム リスト 以外のSharePoint リストも"ナビゲーターダイアログ"に表示されるけれども、必要なSharePoint リストを選択し [編集] でクエリエディタへ。

カスタム リストには "タイトル"(テキスト) / "日付のみ1"(日付) / "金額"(数値:通貨) を用意してある。

該当のSharePoint リストに関連するカラムが多数含まれるが、追加したカスタムフィールドには調整すべき内容が含まれている。SharePoint Onlineの場合、日付時刻に関するフィールドはUTC+0なので普段使用しているタイムゾーンに変換する必要がある。

そこで、タイムゾーン変換をするためにデータ型：日付/時刻/タイムゾーンに変更。
カラム見出しにあるデータ型アイコンをクリック やリボンに配置された"データ型" ドロップダウンでも操作可能。

変換するカラムをすべて選択するなどして、

[変換]タブ - [日付と時刻の列]グループ- [時刻]- [現地時間] でタイムゾーン変換。ここでいう"現地時間" とは ローカルシステムのタイムゾーン。なので、SharePoint サイト で使用されるタイムゾーンと一致していれば日付と時間は同一になる。複数のカスタムリストを処理する上で、それぞれ異なるタイムゾーンを使用している場合、特定のタイムゾーンに変換することができるということでもある。

データ型の変更をそれぞれ実施する。タイムゾーンを含むデータ型(type datetimezone) の値からそれぞれ"抽出"としてもここでは同じ結果になる。

そして、カラム名の変更

データモデルにロードし書式など整えるとレポートに使用できる状態になる。

FieldValuesAsText を使う
FieldValuesAsText 列 を使用することで、SharePoint サイト指定もしくはユーザ プロファイル指定のフォーマットでカスタム リストのアイテムを取得することもできる。タイムゾーンの変換など手間がなくなるのだけど、カラム名が内部列名になってしまうのでとても分かりずらい。これらについては 詳細エディタや数式エディタで記述を実施するなどしてみた。

    Source = SharePoint.Tables("(SharePoint Site URL)", [ApiVersion = 15]),
    SharePoint_List01 = Source{[Id="(GUID)"]}[Items],

    List_FieldValuesAsText = SharePoint_List01[FieldValuesAsText],

    ExpandedFieldValuesAsText = Table.FromRecords(List_FieldValuesAsText), 

※Table.FromRecords を使用する理由は、引数でカラム名を指定する必要がないから。つまり、カスタムフィールドの増減に対応している。

    Table_ColumnNameCombi =
        SharePoint_List01{0}[ContentType][Fields][[EntityPropertyName],[Title]] &
        Table.FromRecords(
            {[EntityPropertyName = "Created", Title = "Created"],
             [EntityPropertyName = "Modified", Title = "Modified"]}),

エンティティをある程度わかっている必要がありそうなのだけど、リスト アイテムの 更新時間なども付け加えておいた。
"{}" (波かっこ) と "[]" (角かっこ) によるアクセスを知っておくと便利。

    List_ColumnNameCombi = Table.AddColumn(
        Table_ColumnNameCombi,
        "FieldList",
        each Record.ToList(_)
    )[FieldList],

カラム名の変更には、{OldName , NewName} という list が必要なので、record を list にRecord.ToList で 変換。また複数のカラム名を変更するので list の list になっていればよい。

    RenameColumnName = Table.RenameColumns(
        SelectedColumns,
        List_ColumnNameCombi,
        MissingField.Ignore
    ),
    SelectedColumns = Table.SelectColumns(
        RenameColumnName,
        Table_ColumnNameCombi[Title],
        MissingField.Ignore
    ),

    ChangeType = Table.TransformColumnTypes(
        SelectedColumns,
        {{"タイトル", type text},
         {"日付のみ1", type date},
         {"金額2", Currency.Type},
         {"Created", type datetime},
         {"Modified", type datetime}}
    )

カラム名を直接指定する必要がある型の変換ステップは最後にしておいた。


やっぱり、うまくいかないこともある...
SharePoint Online にのみ事象を確認。不具合の報告はしたし、別途報告はされているようですが。

• SharePoint サイトで使用される言語設定 
• SharePoint サイト 外部共有機能の使用経緯

に影響している。かといって、サイトの設定変更などで解決できるような内容ではないので、次のようにトライ。

・解決策1
SharePoint.Tables("(SharePoint Site URL)", [ApiVersion = 14])
オプション引数を 14 にする。 この場合、タイムゾーンなどが適用(UTC+0 に限らない)されている変換済みデータになる。なお、サイト既定の言語が英語でないときは解決できない模様。既定の言語：英語 / 第2言語：日本語の場合、外部共有を使用している / 使用していた サイトでも使用可能だった。
ちなみにオンプレミスのSharePoint Server 2016 のサイトでもこれで対応可能。

・解決策2
OData.Feed("(SharePoint Site URL)/_vti_bin/listdata.svc")
これはどのサイトでも使用できた。

これら解決策は、SharePoint.Files でも使えそうです。



let
    Source = SharePoint.Tables("(SharePoint Site URL)", [ApiVersion = 15]),
    SharePoint_List01 = Source{[Id="(GUID)"]}[Items],
    List_FieldValuesAsText = SharePoint_List01[FieldValuesAsText],
    ExpandedFieldValuesAsText = Table.FromRecords(List_FieldValuesAsText),
    Table_ColumnNameCombi =
        SharePoint_List01{0}[ContentType][Fields][[EntityPropertyName],[Title]] &
        Table.FromRecords(
            {[EntityPropertyName = "Created", Title = "Created"],
             [EntityPropertyName = "Modified", Title = "Modified"]}),
    List_ColumnNameCombi = Table.AddColumn(
        Table_ColumnNameCombi,
        "FieldList",
        each Record.ToList(_)
    )[FieldList],
    RenameColumnName = Table.RenameColumns(
        ExpandedFieldValuesAsText,
        List_ColumnNameCombi,
        MissingField.Ignore
    ),
    SelectedColumns = Table.SelectColumns(
        RenameColumnName,
        Table_ColumnNameCombi[Title],
        MissingField.Ignore
    ),
    ChangeType = Table.TransformColumnTypes(
        SelectedColumns,
        {{"タイトル", type text},
         {"日付のみ1", type date},
         {"金額2", Currency.Type},
         {"Created", type datetime},
         {"Modified", type datetime}}
    )
in
    ChangeType