SharePointリストデータを取得する際にハマった話
こんにちは。
PowerPlatformの魅了されているMasayanです。
でも、今日はSharePointリストのデータをExcelで集計する際にハマった点をお伝えしたいと思います。
どういった取得方法があるかを記載し、ハマったことを記載しております。
エクスポート機能を使う
リストに機能として存在している「Excelにエクスポート」機能を使うというのも一つの手かと思います。
「Excelにエクスポート」を使う「query.iqy」というファイルがダウンロードされます。新規にExcelファイルを開き「データ」タブ→「既存の接続」をクリックします。
「既存の接続」のポップアップで「参照(B)…」を選択
「データファイルの選択」のポップアップで先ほどダウンロードした「query.iqy」ファイルを開くと「データのインポート」のポップアップが表示されるので、既存のワークシート(E)をチェックし「＝$A$1」を選択してOKボタンをクリックすると明細が表示されます。
これをピボットテーブルで集計するのもよし、他のテーブルとリレーションを組んだりしてもよしという形です。
「はい・いいえ」列はTrueとFalseとで表示されるので、PowerQueryで別の値に変換しても良いですね。
「クエリと接続」の接続に登録されているので、「データ」タブ→「すべて更新」→「更新(R)」からデータの最新化も可能です。
PowerQueryでリストのデータを取得
ただ、上記のエクスポートの方法の場合は、「ユーザまたはグループ」列のメールアドレス、部署名などは取得できません。
そのため、また別にPowerQueryでデータを取得する方法を書きます。
※今回私のExcelの環境がSharePointリストを開くことができなかったので、PoweBIを使用していますが、Excelでも同じ認識です。
「データを取得」→「詳細」→「オンラインサービス」→「SharePointリスト」→「選択」ボタンをクリックします。
「サイトURL」を入力してOKを押すとナビゲータ画面が表示されるので
取り込むリストをチェックして「読み込み」
※実装2.0にチェックすると既定のビューの列のみ取得できます。
すると内部名で列が抽出されていることに気づきます。
内部名は列名を変更するとして割愛します。
ハマった話① ユーザーまたはグループ列がID表示
それ以外に気になった点としては「ユーザーまたはグループ」列がIDで表示されている点です。（名前すらでてこない）
色々調べて見たところSharePointリストではユーザー単位に「ID」を持っており、「ユーザー情報リスト」というリストで管理していると確認しました。
こちらの記事をもとに「ユーザー情報リスト」の内容を知ることができました🙇♂️
Excelの場合、データモデルに追加する操作は必要でしたが、同じ要領でリレーションシップを設置することができます。
ただ、「ユーザー情報リスト」が「UserInfoList」という名称になっている可能性があります。
イメージ７～９と同じ要領で「ユーザー情報リスト」を探してみるとありました。「ユーザー情報リスト」にチェックして右側の画面でスクロールするとnameやe-mailの列もあります。
このまま読み込みを行い、先ほどの「レコーディング」リストと「ユーザー情報」リストとの間でリレーションを組むことが可能です。
モデルビューに遷移し「レコーディング」の「AuthorID（登録者）」と
「ユーザー情報リスト」の「Id」を「多対１」で連携します。
その後、レポートビューに遷移し、「視覚化」→「ビジュアルのビルド」から「テーブル」を選択し、「ユーザー情報リスト」のE-Mail、LastName、FirstNameなどを取得することができました。
（私個人の情報なので隠してます）
ハマった話② 日時が太平洋標準時（米国およびカナダ）で表示
これは仕事で怒られた話なんですが、SharePointリストをPowerBI（Excel）で取得すると大平洋標準時（米国およびカナダ）の時間で表示されてしまいます。時差にしてマイナス9時間。
これはSharePointのサイトの設定＞地域の設定からタイムゾーンを（UTC+09:00）大阪、札幌、東京としても関係がないようです。
これも頭を悩ましましたが、PowerQuery上の「FieledValueAsText」列を使用することで解決できるそうです。
こちらの記事により知ることができました🙇♂️
内容としては、日時の列を削除します。
先ほどのbedtime、wakeuptimeはもちろん、「日付と時刻」列で時刻を含めていない列も下記のように15:00と表示されているので、削除します。
その後、「FieldValueAsText」列を展開。
bedtime、wakeuptime、day、Modified、Createdのように「日付と時刻」列をチェックしてOKを押します。
「閉じて適用」をクリックすると読み込まれます。
初回は読み込みに時間がかかりましたが、更新する分にはそこまで時間がかからなかったです。
以前、気ままに勉強会#59に参加させていただいたにも関わらず、ふらりさんが仰っていた日時の取り扱いにハマった話を失念してしまっていたのですが、これでもう絶対に気をつけるという教訓になりました。
ちなみにSharePointリストの日時のデータをPowerAutomateでExcelOnlineのアクションでExcelテーブルに転記したときは「(UTC-09:00)世界協定時-09」で表示されていたので、日時コネクタのタイムゾーンの変換アクションで「(UTC+09:00)大阪、札幌、東京」に変換しました。
まとめ
SharePointの日時はサイトの情報＞地域の設定で「(UTC+09:00)大阪、札幌、東京」にしておく
SharePointリストのデータ取得はエクスポート機能は簡単で便利だが、部署単位、役職単位などの集計は不可
部署、役職単位の集計をしたい場合は、PowerBI、ExcelからサイトURLを指定して取得可能だが、ユーザー情報は、ユーザー情報リストから取得し、日時情報は「FieldValueAsText」列を展開する必要がある。
今度はPowerAppsを引き継ぐことになるので、各コントロールのプロパティを書いてみたいです。
この記事が気に入ったらサポートをしてみませんか？