背景
Excel のリンクされたデータ型、使ってますか？
以下の画像にある「データの種類」の部分のやつです。
今回はここでで使える社員一覧データ型を Power BI で作って、Excel で使ってみようというお話。
これによって、以下が簡単にできるようになります。
- Forms に記録された ID (UserPrincipalNameとか) から、所属や氏名などを追加する
- Teams の Attendance.csv に含まれる メール や UPN から、所属や氏名などを追加する
- メール一覧から、社員一覧へ変換する
リンクされたデータ型とは？
Excel での提供時期とVersion
May 2021 update
Here's what's new in Version 2105 (14026.20246).
環境
- 無償版: マイワークスペースへ発行で、個人利用可能
- Pro/Premium: Premium領域メンバーで共有可能
上記二環境で同一名称にしても、問題ないですが、分かりにくくなるので個人用を名称変更するなりした方がよさそう
制約
- Linked Data Types 機能自体が表示されない
- Linked Data Types を非サポートなアカウントがログインされている場合。（片方がサポートしていても）
詳細は以下
Excel Linked Data Types missing when using multiple Microsoft 365 accounts
概要
詳細
Power BI でお勧めのテーブルを準備する
基本は以下
OData でユーザー情報を取得して必要なデータへ加工
- Odata コネクターを探す
- SiteUserInfoList で特定のサイトのメンバー一覧を取得する
SiteUserInfoList の詳細は以下で
故に、必要な人が全員いるサイトが必要
ここに関しては、ActiveDirectory で取得したり、Automate で グループメンバー一覧取得したりと色々方法はあるかと
- 必要な情報に整形してやる。まぁ、ここはよしなに
おすすめのテーブルの作成
- おすすめのテーブルを「はい」にして、行ラベル・キー列を設定
| 対象 | 説明 |
|---|---|
| 説明 | Excel でデータ型（おすすめのテーブル）として説明が表示されるので、使う際に分かりやすいように |
| 行ラベル | セルの値を認識させたあとは、この値がセルに表示されるので、一意にわかる値がおすすめ |
| キー列 | テーブル内の PrimaryKey って考えればよいかと |
列を非表示にする事でデータ型の列には当然出てきません。Excel では以下のように表示にした列を追加可能
- 発行することで、利用可能にします
発行をしない限り、Excel で利用できません。
Power BI サービスに対してデータセットを発行またはインポートすると、Excel のデータ型ギャラリーにおすすめのテーブルが表示されます。
おすすめのテーブルを修正した場合は、毎回更新が必要になります。
おすすめのテーブルを更新した場合は、Excelを再起動する必要があります
Excel ではデータ型の一覧がキャッシュされるため、新しく発行されたおすすめのテーブルを表示するには、Excel を再起動する必要があります。
Excel で利用する
-
変換範囲を選択して、データ型を選択することで変換完了
-
あとはよしなに列追加
列追加方法
- テーブル右上に出てくるアイコンから
- ユーザー型のアイコンから
- 式をインテリセンス使いながら入れる
基本は以下
参考情報
詳細は以下で
あとがき
・Excel でのデータ型のエクスペリエンスは、lookup 関数に似ています。 Excel シートから提供されるセル値を受け取り、Power BI のおすすめのテーブルで一致する行が検索されます。 検索エクスペリエンスには、次のような動作があります。
・行の一致は、おすすめのテーブルに含まれるテキスト列に基づいています。 これには Power BI Q&A 機能と同じインデックスが使用されます。これは英語での検索用に最適化されています。 その他の言語で検索すると、正確な一致が得られない場合があります。
・おすすめのテーブルには、ユーザーがそのテーブルのデータのクエリを実行する際に Power BI Q&A のインデックスが使用されます。 詳細については、「インデックス作成と Q&A が連動するしくみ」を参照してください。
・ほとんどの数値列は、照合の対象とは見なされません。 行ラベルまたはキー列が数値である場合は、それらは照合の対象に含められます。
上記のような記述があるが、以下現象があったので、何かが変・・
- Q&A に対して、テーブル＆列を含めてなくても反応した。一致しないと思ったのだが・・
- Email も何もかもが一致しないことがあった。（500万 Index の上限に引っかかったのかも？
・Q&A ツール内で明示的にオフにされていない限り、列名とテーブルはすべて、インデックスに挿入されます。
・100 文字未満のテキスト値はすべてインデックス化されます。 100 文字以上のテキスト値のインデックスは作成されません。
・Q&A インデックスには、最大 500 万の一意の値が格納されます。 この数を超えると、インデックスにすべての潜在的な値が保持されないため、Q&A の正確性が低下することがあります。
・インデックス作成中にエラーが発生した場合、インデックスは部分的な状態のままになり、次の更新で再作成されます。
Q&A 自体、まだプレビュー扱いなので、しょうがないのかな、という感覚
とはいえ、データ型はとっても便利なので、Excel 自体がなくならない限り是非使っていきたいですね。
keyword
create sharepoint site member list in Excel Organization Data Types Gallery