Como fazer Querys na TeamMembership utilizando Power Query Builder/XrmToolbox
Fala pessoal!
Recentemente passamos por um cenário curioso. Estávamos utilizando um plugin do XrmToolbox chamado Power Query Builder para fazer Query na tabela TeamMembership, que é a tabela que relaciona equipes com usuários. Imaginem o seguinte cenário:
Um gerente precisa acompanhar as durações de atividades de Ocorrências (Chamados/Incidents) e quer fazer um filtro por usuários de uma determinada equipe. Ao tentar montar a query no PowerQueryBuilder não encontramos a tabela TeamMembership. O que podemos fazer? Bom, primeiramente esse é o esquema para fazer query na tabela de histórico TeamMembeership:
let
GetResults = (z as text, x as number) =>
let
S = Json.Document(Web.Contents(ServiceRootURL, [RelativePath="/teammemberships", Headers=[Prefer="odata.include-annotations=*"],Query=[fetchXml="
<fetch distinct=""True"">
<entity name=""teammembership"" >
<attribute name=""teamid"" />
<attribute name=""systemuserid"" />
<link-entity name=""systemuser"" from=""systemuserid"" to=""systemuserid"" link-type=""inner"" alias=""usuario"" >
<attribute name=""internalemailaddress"" />
<attribute name=""fullname"" />
</link-entity>
<link-entity name=""team"" from=""teamid"" to=""teamid"" link-type=""inner"" >
<attribute name=""name"" />
</link-entity>
</entity>
</fetch>"]])),
P = try Xml.Document(S[#"@Microsoft.Dynamics.CRM.fetchxmlpagingcookie"]) otherwise null,
R = if P <> null
then List.Combine({S[value],@GetResults(Text.Replace(Text.Replace(Text.Replace(Uri.Parts("http://a.b?d=" & Uri.Parts("http://a.b?d=" & P{0}[Attributes]{1}[Value])[Query][d])[Query][d], ">", ">"), "<", "<"), """", """), x + 1)})
else S[value]
in
R,
ResultsList = GetResults("", 1),
ResultsTable = if List.IsEmpty(ResultsList)
then #table(
type table[ #"Nome Completo"= text,
#"systemuserid"= text ],{})
else #"Converted to Table",
#"Converted to Table" = Table.FromList(ResultsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1",
{
"teamid",
"systemuserid",
"teammembershipid",
"usuario_x002e_internalemailaddress",
"usuario_x002e_fullname",
"team2_x002e_name"
},
{
"teamid",
"systemuserid",
"teammembershipid",
"usuario_x002e_internalemailaddress",
"usuario_x002e_fullname",
"team2_x002e_name"
}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",
{
{"teamid", "teamid"},
{"systemuserid", "systemuserid"},
{"teammembershipid", "teammembershipid"},
{"usuario_x002e_internalemailaddress", "useremail"},
{"usuario_x002e_fullname", "username"},
{"team2_x002e_name", "equipe"}
}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",
{
{"teamid", type text},
{"systemuserid", type text},
{"teammembershipid", type text},
{"useremail", type text},
{"username", type text},
{"equipe", type text}
})
,
#"Result" = if List.IsEmpty(ResultsList)
then ResultsTable
else #"Changed Type"
in
#"Result"
Caso precise adicionar algum campo a mais de Usuários (systemuser) ou Equipes (Team), o que é preciso fazer é bem simples. Primeiro Edite o fetch xml:
<fetch distinct=""True"">
<entity name=""teammembership"" >
<attribute name=""teamid"" />
<attribute name=""systemuserid"" />
<link-entity name=""systemuser"" from=""systemuserid"" to=""systemuserid"" link-type=""inner"" alias=""usuario"" >
<attribute name=""internalemailaddress"" />
<attribute name=""fullname"" />
<attribute name=""INSIRA UM NOVO CAMPO PARA USUARIO"" />
</link-entity>
<link-entity name=""team"" from=""teamid"" to=""teamid"" link-type=""inner"" >
<attribute name=""name"" />
<attribute name=""INSIRA UM NOVO CAMPO PARA EQUIPE"" />
</link-entity>
</entity>
</fetch>
Agora adicione os campos no mapeamento das tabelas nos dois parametros da Table.ExpandRecordColumn
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1",
{
"teamid",
"systemuserid",
"teammembershipid",
"usuario_x002e_internalemailaddress",
"usuario_x002e_fullname",
"team2_x002e_name",
"NOVO CAMPO DE USUARIO",
"NOVO CAMPO DE EQUIPE"
},
Depois nomeie as novas colunas como quiser:
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",
{
{"teamid", "teamid"},
{"systemuserid", "systemuserid"},
{"teammembershipid", "teammembershipid"},
{"usuario_x002e_internalemailaddress", "useremail"},
{"usuario_x002e_fullname", "username"},
{"team2_x002e_name", "equipe"},
{"NOME DO CAMPO USUARIO", "NOME AMIGAVEL PARA CAMPO USUARIO"},
{"NOME DO CAMPO EQUIPE", "NOME AMIGAVEL PARA CAMPO EQUIPE"}
}),
Os campos de Nome Amigável são os nomes que estarão presentes na sua tabela de dados gerada após dar um apply no Power BI. Por fim, defina os tipos das colunas selecionadas
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",
{
{"teamid", type text},
{"systemuserid", type text},
{"teammembershipid", type text},
{"useremail", type text},
{"username", type text},
{"equipe", type text},
{"NOME AMIGAVEL USUARIO", type text},
{"NOME AMIGAVEL EQUIPE", type text}
})
Pronto, após dar um apply você pode pegar e relacionar as tabelas que possuem relacionamento como os campos de ownerid, e fazer qualquer tipo de filtro relacionados também as Equipes. Espero ter contribuído com vocês! Caso tenham problemas ou dúvidas por favor deixem em seus comentários.
