2 つのモードがあって 1.0 と 2.0。2.0 の ベータ 表記がなくなったので、そろそろパブリック プレビュー から GA かなと思っていたのだけど、どうやらちょっと違ったみたいかも。
It's important to have similar experiences and functionalities across different Power Query experiences. With this feature, you can use the SharePoint Lists V2 connector in Power Query Online and dataflows, along with its availability in Power Query Desktop today.
Power Query Desktop であれば利用可能だけど、Power Query Online はまだだよってさ。Power Query Online でも動作はしてますけどね。で、以前こんな感じでポストしていた。
SharePoint.Tables(url as text, optional options as nullable record) as table
---
option
[ ApiVersion = 14 | 15 | "Auto" ]
[ Implementation = "2.0", ViewMode = "Default" | "All" ]
SharePoint Server 上に配置されている SharePoint リスト 用の コネクタ であっても同じ SharePoint.Tables 関数(Power Query) が利用される。option 引数が異なるだけ。SharePoint Server 環境に触れる機会がほぼないので、どうなっているのか確認しない。
1.0 と 2.0
1.0 と 2.0 の異なる ポイント は、
- パフォーマンス
- 列名
- タイムゾーン
SharePoint リスト を データソース とする場合、1.0 コネクタ でなくてもよいはずだ、SharePoint Online も様々な向上がされているのだから、これに合わせて調整されている 2.0 コネクタの方がいいんじゃね？という感じ。
パフォーマンス
パフォーマンス はかなり良くなる。
SharePoint Online の サイト に配置した、およそ 10万アイテム - タイトル/数値列 * 2 / 日付時刻列 * 1 / ルックアップ列 * 2 とシステム列という SharePoint カスタム リスト を Power BI Desktop で データモデル に インポート した感じでは 1.0 コネクタ が約 240 sec で 2.0 コネクタ が約 20 sec だった。多くの場合で、パフォーマンス の改善に期待できる。
1.0 コネクタ と変わらないポイントは、リスト アイテム 全件を取得すること。行に フィルタ を適用する ステップ を用意しても全件取得する。
1.0 と 2.0 いろいろ再検証したところ、Table.FirstN など 先頭行からの行のフィルタは効果的に作用する。
ただ、主要な動作が変更される。
GET https://{site_url}/_api/Web/Lists(guid'{list_guid}')/Items
POST https://{site_url}/_api/Web/Lists(guid'{list_guid}')/RenderListDataAsStream
- Navigation ステップに相当する処理では、リスト名 と リストを特定する GUID との名前解決
- RenderListDataAsStream で
SPRenderListDataOptions options : 5707527を POST しスキーマ情報など取得 - RenderListDataAsStream で ページング(2,000件 もしくは 1,000件で済むこともある)しながら取得
概ねこのような処理。で、注目は ページング しながら アイテム を取得する時の Request body に
{
"parameters":{
"RenderOptions": 2,
"ViewXml": <<xml>>
}
}
<View>
<ViewFields>
<FieldRef Name="<フィールド名>" />
</ViewFields>
<RowLimit Paged="TRUE">2000</RowLimit>
</View>
という感じになっている。<ViewFields> の要素は、列を選択するステップが反映されていて、
let
Source = SharePointListData,
RemovedOtherColumns = Table.SelectColumns(Source,{"単価", "数量", "受注日"})
in
RemovedOtherColumns
この記述で必要な列の情報だけを取得できる。メタデータ や システム情報も必要なだけだから、これは素晴らしい。
ルックアップ列
let
Source = SharePointListData[[商品]],
ExpandedToRows商品 = Table.ExpandListColumn(Source, "商品"),
ExpandedToFields商品 = Table.ExpandRecordColumn(
ExpandedToRows商品,
"商品",
{"lookupId", "lookupValue", "isSecretFieldValue"}
)
in
ExpandedToFields商品
SharePoint リスト の ルックアップ 列 : 商品 を展開するのも簡単に素早くできる。lookupId, lookupValue, isSecretFieldValue として値はすでに取得済みであるから。1.0 コネクタ で必要だった ルックアップ先情報の展開などに工夫する必要がほぼない。
初出 2020 秋 なのだけど、これまでずいぶんとお世話になった。ルックアップ先の リスト を別の クエリ で取得して、Power Query でマージするっていう。じゃぁ 2.0 コネクタ だとなにも問題ないのかというと少しあって、
SharePoint join limit
This issue is limited to the SharePoint Online list v2.0 connector
The SharePoint Online list v2.0 connector uses a different API than the v1.0 connector and, as such, is subject to a maximum of 12 join operations per query, as documented in the SharePoint Online documentation under List view lookup threshold. This issue will manifest as SharePoint queries failing when more than 12 columns are accessed simultaneously from a SharePoint list. However, you can work around this situation by creating a default view with less than 12 lookup columns.
ルックアップ列を結合する クエリ の結果が得られるのだけど、SharePoint 側で処理できる結合は 12 まで。[SharePoint リスト と ライブラリの制限] なのでどうにもならない。
列名
SharePoint リスト の表示列名 で取得できる。1.0 コネクタでは EntityPropertyName(内部列名とは違う)を変換したものだったので、どの列をさしているかわからないこともあったのでやれやれである。
タイムゾーン
1.0 コネクタでは 常に UTC。SharePoint リストの表示は [サイトの設定] に依存する。なので、結構やらしているケースがあると思う。
The backend API for SharePoint uses UTC time and sends this UTC time directly to Power BI. Power BI doesn't convert this UTC time, but reports it to the user.
2.0 コネクタでは リストが配置された SharePoint サイト の [Site Settings] → [Regional Settings] に依存する。
使い始めたとき、なんで UTC じゃないの？と思っていたけど、これはこれで合理的なのかもと思えてきた。DatesInUtc という スイッチ を用意してくれたらいいなとも思う。
思ったこと🙄
1.0 コネクタを使う理由はほぼなくて、2.0 コネクタでは取得できない情報があるときのみっていう振り分けで充分だと思う。
SPRenderListDataOptions options : 5707527(101 0111 0001 0111 0000 0111)
| Label | Description | Value |
|---|---|---|
ContextInfo |
Return list context information | 1 |
ListData |
Return list data (same as None) | 2 |
ListSchema |
Return list schema | 4 |
MenuView |
Return HTML for the list menu | |
ListContentType |
Returns information about list content types. Must be combined with the ContextInfo flag |
|
FileSystemItemId |
The returned list will have a FileSystemItemId field on each item if possible. Must be combined with the ListData flag |
|
ClientFormSchema |
Returns the client form schema to add and edit items | |
QuickLaunch |
Returns QuickLaunch navigation nodes | |
Spotlight |
Returns Spotlight rendering information | 256 |
Visualization |
Returns Visualization rendering information | 512 |
ViewMetadata |
Returns view XML and other information about the current view | 1024 |
DisableAutoHyperlink |
Prevents AutoHyperlink from being run on text fields in this query | |
EnableMediaTAUrls |
Enables URLs pointing to Media TA service, such as .thumbnailUrl, .videoManifestUrl, .pdfConversionUrls
|
4096 |
| ParentInfo | Returns parent folder information | |
| PageContextInfo | Returns page context info for the current list being rendered | |
| ClientSideComponentManifest | Return client-side component manifest information associated with the list (reserved for future use) |
その他