Excel クエリ エディター や Power BI Desktopで SharePoint Online のカスタム リストをデータソースとしたとき列名がよくわからないことがある。SharaPoint リストコネクタでは、SharePoint REST APIが使用されていてクエリ エディターで表示される列名には EntityPropertyName が使用される。以前にはなかったのだけど、コードポイントから文字への変換が行われるようになったので見た目わかりやすくなったが、表示列名のと関連付けが面倒になってしまった。InternalName や EntityPropertyName は DisplayNameを変更しても変わらないので以前からわかりにくいことはあったのだけど。これを解決しておくPower Query のデモ的なお話。
 |
| まぁないですけどね。 |
ビューを指定できる関数にしておく
せっかくなのでカスタムリストのビューを指定して列を選択できるようにして、可能な限りデータ型を設定しておこう。(SPList as table, optional ViewName as text) as table =>
let
//
ConvertEntityPropertyName = (EntityPropertyName as text) as text =>
let
Custom0 = {0 .. List.Count(Text.Split(EntityPropertyName, "_x"))},
Custom1 = List.Transform(
Custom0,
each Text.BetweenDelimiters(
EntityPropertyName,
"_x",
"_",
{_, RelativePosition.FromStart})
),
Custom2 = List.Select(
Custom1,
each Text.Length(_) = 4 or Text.Length(_) = 8
),
Custom3 = List.Transform(
Custom2,
each [
Old = "_x" & _ & "_",
New = try Character.FromNumber(
Expression.Evaluate("0x" & _)
)
otherwise Old
]
),
Custom4 = List.Distinct(Custom3),
Titles = List.Generate(
()=> [Counter = 0, Title = EntityPropertyName],
each [Counter] <= List.Count(Custom4),
each [
Counter = [Counter] + 1,
Title = Text.Replace(
[Title],
Custom4{[Counter]}[Old],
Custom4{[Counter]}[New]
)
],
each [Title]
)
in
List.Last(Titles),
//
TypeFromSchemaXml = (SchemaXml as text) as type =>
let
ParsedXML = Xml.Tables(SchemaXml){0},
Branch = #table(
type table [
DataType = Text.Type
,ResultType = Text.Type
,Percentage = Logical.Type
,Type = Type.Type
],
{
{"Text", null, false, Text.Type}
,{"DateTime", null, false, DateTimeZone.Type}
,{"Number", null, false, Decimal.Type}
,{"Number", null, true, Percentage.Type}
,{"Currency", null, false, Currency.Type}
,{"Boolean", null, false, Logical.Type}
,{"Integer", null, false, Int64.Type}
,{"Counter", null, false, Int64.Type}
,{"Calculated", "Text", false, Text.Type}
,{"Calculated", "DateTime", false, DateTimeZone.Type}
,{"Calculated", "Number", false, Decimal.Type}
,{"Calculated", "Number", true, Percentage.Type}
,{"Calculated", "Currency", false, Currency.Type}
,{"Calculated", "Boolean", false, Logical.Type}
}
),
Type = try
Branch{[
DataType = ParsedXML[#"Attribute:Type"],
ResultType = ParsedXML[#"Attribute:ResultType"]?,
Percentage = ParsedXML[#"Attribute:Percentage"]? = "TRUE"
and (DataType = "Number" or ResultType = "Number")
]}[Type]
otherwise
Any.Type
in
Type,
//
ParentList = SPList{0}[ParentList],
Views = ParentList[Views],
SelectedView = Table.SelectRows(
Views,
each if ViewName <> null
and List.Contains(Views[Title], ViewName)
then [Title] = ViewName
else [DefaultView] = true
),
ViewFieldsList = SelectedView{0}[ViewFields][Items],
ChangeTitleName = List.ReplaceMatchingItems(
ViewFieldsList,
{
{"LinkTitle", "Title"}
,{"LinkTitle2", "Title"}
,{"LinkTitleNoMenu", "Title"}
}
),
SPListFields = ParentList[Fields]
[
[EntityPropertyName]
,[InternalName]
,[Title]
,[SchemaXml]
],
FieldsNames = Table.SelectRows(
SPListFields,
each List.Contains(ChangeTitleName, [InternalName])
),
FieldNamesCombi = Table.TransformColumns(
FieldsNames,
{
{"EntityPropertyName", ConvertEntityPropertyName}
,{"SchemaXml", TypeFromSchemaXml}
}
),
SelectedColumns = Table.SelectColumns(
SPList, FieldNamesCombi[EntityPropertyName]
),
ChangedType = Table.TransformColumnTypes(
SelectedColumns,
Table.TransformRows(
FieldNamesCombi,
each {[EntityPropertyName], [SchemaXml]}
)
),
RenamedColumns = Table.RenameColumns(
ChangedType,
Table.TransformRows(
FieldNamesCombi,
each {[EntityPropertyName], [Title]}
)
)
in
RenamedColumns
同時に取得できる情報を展開などしているだけ。日付時間のフィールドはSharePoint Online では UTC+0 なので TimeZone 付きにしておいて必要なときに変換する。フォルダが含まれるときは、FileSystemObjectType = 0 のフィルタをどこかで適用すればよいし、アイテムが 0件のときを考慮する場合 ParentList = SPList{0}[ParentList] を ParentList = try SPList{0}[ParentList] にして 最終ステップで確認すればよいかな。とっつきにくいから触ってなかったけど、List.Generate を使えるようになってよかった。繰り返し処理でかなりイケてる。テーブルに列を追加する(Table.AddColumns) もしくは リストの値を変換する(List.Transform) だとややこしい処理も問題なくできる。