Queries DAX para Power Automate: como preencher documentos Word com dados do Power BI
Por vezes, é necessário redigir um relatório sob o formato de texto, em Word, por exemplo, que contém dados referentes a um determinado indicador. O processo mais comum é redigir o texto separadamente dos valores numéricos, preenchendo-se os dados manualmente, ou através de “copy-paste” no documento. Não seria ótimo se nos ligássemos a um conjunto de dados do Power BI e inseríssemos os valores diretamente num documento? É o que veremos neste post.
Modelo de dados semântico do Power BI
O primeiro requisito é dispor de um conjunto de dado do Power BI, agora denominado de modelo de dados semântico. Para este exemplo, vamos usar a demo “Competitive Marketing Analysis Report”:
O relatório contém várias páginas e nelas podemos ver que existem diversos indicadores interessantes, como vendas, unidades vendidas, margens, preços, assim como outros não financeiros, como o score de sentimento dos clientes.
O modelo de dados é um “star schema” com duas tabelas transacionais: a Sentiment e a SalesFact:
O template Word
Para combinarmos o texto com os dados deste modelo semântico, precisamos de um “template”, um modelo em Word que terá uma base fixa, redigida em texto, e alguns “placeholders” onde guardaremos os dados do Power BI.
Este processo consiste em criar um novo documento Word e definir em que partes do texto pretendemos inserir os dados numéricos. É um processo algo aborrecido porque tem uma série de detalhes que podem fazer todas a diferença:
- Primeiro, será necessário ativar-se o menu “Developer” do Microsoft Word, nas opções do Word
- Nesse menu vamos inserir um objeto “Plain Text Content Control”, que se encontra no grupo “Controls”
- Esse objeto será colocado algures no meio do texto que estamos a redigir, como nos interessar.
Por exemplo, ao redigir o Relatório e Contas da Empresa “DemoAutomation”, teríamos o seguinte parágrafo inicial:
O placeholder “vendas” figura mesmo antes da vírgula e é onde os dados serão colocados. Na definição das propriedades do mesmo, podemos verificar a “tag” que será reconhecida no Power Automate:
Depois de completado primeiro parágrafo, poderíamos chegar a algo do género:
O documento Word ficará guardado num document folder do SharePoint, por exemplo, “Templates Word para Power Automate”, e vamos preencher as tags a partir de um Power Automate.
Criação de um fluxo de automação de teste
No Power Automate, vamos criar um fluxo com um trigger manual e acrescentar uma ação do tipo “Populate a Microsoft Word Template”:
Os três primeiros campos são simplesmente a identificação do documento Word utilizado como template, e os restantes são as tags nele criadas com os dados a inserir.
Executando este flow, veremos que corre sem erros, mas que o template Word permanece inalterado. Isto acontece porque o template é suposto ser apenas usado como tal e se pretendemos um documento preenchido, teremos de criar um novo ficheiro Word estático com os valores das tags preenchidas.
Assim, termos de acrescentar uma nova ação no Power Automate:
Os dois primeiros argumentos deste step são respetivamente a localização do site SharePoint e o diretório onde será criado o ficheiro. O terceiro, o nome do ficheiro a criar e o último será o conteúdo do step anterior.
Correndo este fluxo, vejamos os resultados:
Aparentemente, o ficheiro foi preenchido corretamente, e colocado no document folder pretendido. Abrindo-o, podemos ver o seguinte:
Pesquisar os dados no modelo de dados semântico do Power BI
Claro que não queremos preencher os dados manualmente, mas sim a partir do dataset do Power BI. O primeiro passo consiste em publicá-lo no Power BI Service, para que possa ser pesquisável a partir do Power Automate.
Deste modo, vamos dar um passo atrás e começar o nosso fluxo por executar uma pesquisa no modelo de dados semântico. O primeiro passo, logo após o trigger, será “Run query against a dataset”:
As duas primeiras caixas contêm, respetivamente, a workspace onde está guardardo o dataset, e a segunda, o próprio dataset. A parte mais difícil é redigir a DAX que pretendemos obter do Power BI.
Para nos ajudar a resolver este problema, podemos voltar ao Power BI Desktop e acrescentar uma página onde visualizamos os dados a importar.
Por exemplo, a página para o ano de 2013, poderia conter as seguintes visualizações:
Nela, temos um slicer, no topo, a selecionar o ano, e três cartões, onde a DAX é definida da seguinte forma:
- Sales $:
= SUM([Revenue]) - SalesGrowthRate:
= DIVIDE([Sales $], CALCULATE([Sales $], SAMEPERIODLASTYEAR('Date'[Date]))) - 1 - Crescimento:
= IF([SalesGrowthRate] >= 0, "Crescimento", "Decrescimento")
Para obtermos a query a enviar para o Power Automate, podemos ativar o “Performance Analyzer” do Power BI Desktop, a partir do friso superior:
Antes de utilizarmos o Performance Analyzer, vamos mudar a página do relatório para forçar o “refresh” das visualizações. O Performance Analyzer permite gravar todas as interações do utilizador com o relatório e, quando mudamos de página, recria todas as visualizações.
Depois de abrir outra página, vamos ativar no painel do lado direito, a gravação das ações do utilizador, a partir do botão “Start recording”:
Quando voltamos à página que contém os indicadores que nos interessa, verificamos que este painel contém agora uma série de detalhes sobre cada um dos elementos visuais:
Vamos clicar em “Stop” para parar a gravação do Performance Analyzer e abrir o primeiro cartão:
Como vemos, ele diz respeito ao indicador “Crescimento”. Do lado direito do Performance Analyzer é possível verificar a duração em milissegundos que demorou a ser produzido. A parte que nos interessa mais é a que está no link “Copy query”. Vamos clicar nesse link e a sintaxe da DAX é copiada para a memória da nossa máquina.
Se a colarmos num bloco de notas, temos:
// DAX Query
DEFINE VAR __DS0FilterTable =
TREATAS({2014}, 'LocalDateTable_a1cd3d03-2df1-466a-bea5-79b9463a9397'[Year])
EVALUATE
SUMMARIZECOLUMNS(__DS0FilterTable, "Crescimento", IGNORE('Medidas'[Crescimento]))
Esta é a DAX que podemos introduzir no passo “Run a query against a dataset” no Power Automate:
Se procedermos da mesma forma para os outros indicadores, teremos as seguintes expressões DAX:
Sales $:
// DAX Query
DEFINE VAR __DS0FilterTable =
TREATAS({2014}, 'LocalDateTable_a1cd3d03-2df1-466a-bea5-79b9463a9397'[Year])
EVALUATE
SUMMARIZECOLUMNS(__DS0FilterTable, "Sales__", IGNORE('SalesFact'[Sales $]))
SalesGrowthRate:
// DAX Query
DEFINE VAR __DS0FilterTable =
TREATAS({2014}, 'LocalDateTable_a1cd3d03-2df1-466a-bea5-79b9463a9397'[Year])
EVALUATE
SUMMARIZECOLUMNS(__DS0FilterTable, "SalesGrowthRate", IGNORE('Medidas'[SalesGrowthRate]))
E o flow estaria agora assim:
Os resultados são devolvidos pelo Power BI sem formatação, o que para a redação do Relatório e Contas não serve. Assim, vamos acrescentar os seguintes steps para formatar os dados em texto, valor percentual e divisa.
Comecemos por inicializar variáveis para guardar os resultados:
A definição da variável “Crescimento” em formato de texto normal pode ser feita com os seguintes steps:
A expressão dentro do step “Append to string variable” é a seguinte:
items('Apply_to_each')?['[Crescimento]']
Note-se que este step está dentro de um loop “Apply to each”, pois o resultado da query ao dataset Power BI é uma tabela (array) que pode conter uma ou várias linhas. Para o caso é indiferente, pois estamos à procura de um valor único (escalar).
No entanto, no caso das restantes variáveis, temos de ter algum cuidado com a formatação. E para estes casos, uma ação “Compose” adicional será útil para nos ajudar.
No caso da variável Sales, temos:
Sendo a expressão no step “Increment variable” a seguinte:
items('Apply_to_each_2')?['[Sales__]']
Como referido, neste caso, vamos acrescentar uma ação “Compose” para formatar o valor numérico. Note-se que a formatação de dados no Power Automate é um pouco difícil e a ação “Format data by examples” ajuda a ultrapassar as principais barreiras:
Como podemos ver acima, fornecendo alguns exemplos ao algoritmo, é possível obter a expressão desejada no passo seguinte:
A última variável poderá ser tratada de forma semelhante:
A expressão na ação “Append to string variable 2” é a seguinte:
items('Apply_to_each_3')?['[SalesGrowthRate]']
E a formatação atavés de exemplos, poderia ser feita da seguinte forma:
Tal como no caso da variável anterior, vamos acrescentar uma ação “Compose” para lidar com a variável “SalesGrowth”:
Finalmente, para terminarmos esta demonstração, voltaríamos à ação “Populate a Microsoft Template” e substituiríamos os valores dos dados introduzidos manualmente para os resultados de cada ação “Compose”:
E como seria de esperar, quando voltamos ao nosso Relatório e Contas já devidamente preenchido, os dados figuram no documento Word com os resultados provenientes do Power BI:
Claro que poderíamos remover as caixas à volta de cada uma das tags e apresentar o relatório com exatamente o mesmo formato de um texto redigido manualmente.
