Neste artigo, demonstrarei como usar o Power Automate para extrair dados através de uma consulta ao dataset e exportá-los via CSV para uma pasta do Sharepoint. (SEM LIMITE DE LINHAS)
Fala pessoal!!!
Tudo bem com vocês? Espero que sim.
Me chamo Rafael Leite, sou consultor de BI, apaixonado por visualização e análise de dados, design e extremamente curioso por toda a complexidade que existe por trás de cada processo. Gostaria de trazer para vocês um case que foi bastante desafiador e tenho certeza de que pode contribuir com algum colega que já tenha passado ou passará por uma situação semelhante.
Sabemos que o Power BI hoje é sem dúvidas a ferramenta mais utilizada no mercado quando o assunto é análise e visualização de dados, mas quem já não se deparou com a seguinte pergunta?👇
Sim, isso acontece e muito, ainda existem diversos cenários onde há a necessidade de o usuário fazer a exportação desses dados, seja para sua própria análise ou compartilhamento dos dados de seu dashboard com outras pessoas e/ou empresas. Temos algumas possibilidades de trabalhar com essa exportação, mas nesse artigo vou me concentrar em apenas uma delas, a de exportar os dados através de uma consulta ao dataset quando este está publicado.
No método que trarei aqui para vocês, teremos a chance de exportar dados sem que esbarremos no limite que a API possui, a de exportar apenas 150k linhas para o Excel ou de 30k para csv, porque eu disse “chance”? Pois dependerá do número de colunas que existir em seu visual, no cenário que atuei tínhamos cerca de 57 colunas, nesse cenário eu acabava sendo limitado pelo tamanho do próprio arquivo e não pelo limite de linhas, essa quantidade de colunas foi algo que reduzimos após definição de cenário com o usuário.
Antes de colocarmos a mão na massa, vou compartilhar com vocês alguns conteúdos que me ajudaram bastante a ter êxito nessa missão:
Documentação: https://learn.microsoft.com/pt-br/rest/api/power-bi/datasets/execute-queries
Fourmoo: https://www.fourmoo.com/2022/06/15/exporting-a-power-bi-visual-data-to-a-csv-file-in-sharepoint/
Microsoft Power BI Blog: https://powerbi.microsoft.com/en-us/blog/unlocking-new-self-service-bi-scenarios-with-executequeries-support-in-power-automate/
Agradecimentos: Em especial a Ruth Pozuelo Martinez proprietária da Curbal AB e também ao amigo de time Leonardo Vitorino Silva, sem eles esse trabalho não seria concluído. É gente, apesar de a Ruth possuir uma infinidade de inscritos em seu canal, as dúvidas que eu precisei tirar, ela me respondeu, ehhhhh...
📍 Então, vamos com a mão na massa 😃
O método em questão funcionará nas licenças Power BI Pro, Premium por usuário e Premium.
Passo 1:
Consulta DAX para extração dos dados. Essa consulta será utilizada para extrair os dados em um csv. Para isso, criei meu conjunto de dados do Power BI com o relatório e o carreguei no Serviço do Power BI. Isso foi necessário pois será nesse conjunto de dados que farei a minha consulta. Deve estar se perguntando... Como vou fazer essa bendita consulta DAX para utilizá-la na consulta???
Calma, sei bem como é isso, passei pelo mesmo sentimento 😁 mas a boa notícia é que você não precisa necessariamente conhecer a fundo sobre isso, aqui está um método mais fácil de obter a nossa consulta:
// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('dCalendario'[data])),
AND('dCalendario'[data] >= DATE(@{variables('FiltrarMinData')}), 'dCalendario'[data] <= DATE(@{variables('FiltrarDataMax')}))
)
VAR __DS0FilterTable4 =
FILTER(KEEPFILTERS(VALUES('fRegistros'[numero_sequencial])), and('fRegistros'[numero_sequencial] >= @{variables('MinLinhas')},'fRegistros'[numero_sequencial] <=@{variables('IncrLinhas')} ))
VAR __DS0Core =
SUMMARIZECOLUMNS(
'fRegistros'[status_consolidado],
'fRegistros'[data_cadastro_parceiro],
'fRegistros'[cpso],
'fRegistros'[spc],
'fRegistros'[nome],
'fRegistros'[data_nascimento],
'fRegistros'[email],
'fRegistros'[nome_mae],
'fRegistros'[nacionalidade],
'fRegistros'[cidade_residencia],
'fRegistros'[estado_residencia],
'fRegistros'[atividade],
'fRegistros'[profissao],
'fRegistros'[renda],
'fRegistros'[nbt],
'fRegistros'[nat],
'fRegistros'[nct],
'fRegistros'[dti],
'fRegistros'[dct],
'fRegistros'[nome_basico],
'fRegistros'[nscb],
'fRegistros'[nbb],
'fRegistros'[nab],
'fRegistros'[ncb],
'fRegistros'[nome_certificado],
'fRegistros'[nsco],
'fRegistros'[nbo],
'fRegistros'[nao],
'fRegistros'[nco],
__DS0FilterTable,
__DS0FilterTable4
)
EVALUATE
__DS0Core
ORDER BY
'fRegistros'[data_transacao] ASC
Juntamente com a descrição de alguns parâmetros, vou deixar aqui a documentação caso alguém queira se aprofundar um pouco mais.
Parâmetro DEFINE: A definição precede a instrução EVALUATE e são válidas para todas as instruções EVALUATE. As definições podem ser variáveis, medidas, tabelas1 e colunas1. As definições podem fazer referência a outras definições que aparecem antes ou depois da definição atual. Pelo menos uma definição será necessária se a palavra-chave DEFINE estiver incluída em uma consulta.
Parâmetro EVALUATE: No nível mais básico, uma consulta DAX é uma instrução EVALUATE que contém uma expressão de tabela. Pelo menos uma instrução EVALUATE é necessária, entretanto, uma consulta pode conter qualquer número de instruções EVALUATE.
Parâmetro ORDER BY (Opcional): Aqui é como você gostaria de ordenar a sua consulta, escolherá por campo quer fazer essa ordenação e se esta é em ordem Crescente ou Decrescente.
Link da documentação: https://learn.microsoft.com/pt-br/dax/dax-queries
Então percebam que a estrutura da query seguiu as seguintes etapas:
Iniciando com o parâmetro DEFINE temos inseridos a ele as Variáveis que serão aplicadas a consulta, percebam que as variáveis seguem um padrão, sendo assim: __VAR(nome da variável), um exemplo do que consta na query: VAR __DS0FilterTable, isso aplicando e a cada variável criada, a variável anterior é aplicada a posterior. A última variável a ser inserida na consulta final segue esse padrão: __DS0Core, sendo ela a que será aplicada ao parâmetro: EVALUATE
Se trabalharmos apenas com a consulta diretamente ao dataset o limite de linhas será algo em torno de 100k linhas, para modificar esse limite, faremos uma nova consulta, mas essa será utilizada como um fluxo dentro do Power Automate e após isso será inserida em uma variável.
Esse será o código que utilizaremos para definir o que chamaremos de looping dentro da automação:
EVALUATE
ROW(
"LoopsNecessarios", ROUNDUP(
DIVIDE(@{variables('IndiceMax')},@{variables('IncrLinhas')}),1)+1
) Vamos ao Power Automate para explicar um pouco mais o do que está acontecendo.
Nosso fluxo seguirá essas etapas conforme a imagem abaixo, após elas vou explicar cada uma delas.
Como criei um botão para que o usuário possa exportar seus dados de acordo com os filtros que ele aplica, o fluxo inicia justamente com ele. Após isso adiciono um menu para armazenar as variáveis que serão aplicadas.
A primeira variável criada é para buscar a quantidade mínima de linhas:
A segunda variável é a que utilizaremos definindo o número que será utilizado como base para seguir para o próximo loop, no exemplo da imagem utilizei 10000 pois fez mais sentido no meu cenário:
A terceira variável que estou utilizando é para que possamos realizar um cálculo dentro da consulta e que será definida para a contagem de loops necessários:
A etapa a seguir é a consulta sendo realizada no dataset, definindo a quantidade de loops necessários para que consigamos buscar a maior quantidade de linhas possível, sem que sejamos impedidos pelo próprio limite da API.
Nessa próxima etapa temos a saída da consulta:
Aqui temos uma variável onde receberá o seu output em outro fluxo:
Nessa variável vamos definir o inicio do loop, começando em 1:
Nessa variável, como tenho no dashboard a opção de filtrar o visual de tabela com a data selecionada pelo usuário, eu precisei buscar esse range do filtro, para isso utilizei as variáveis para buscar o valor mínimo e máximo das datas:
Um outro cenário que também filtra o visual de tabela é um parâmetro, onde o usuário define os dados que serão mostrados, esse parâmetro foi utilizado para selecionar algumas transações que atendessem os determinados critérios:
A próxima etapa do fluxo é onde vamos definir a consulta com as variáveis que criamos anteriormente:
A etapa da consulta que está dentro do “Do Until” é onde entrará a Dax Query que extraímos lá no Power BI utilizando o Performance Analyzer, com algumas modificações, pois passamos a essa consulta as variáveis que criamos.
A seguir temos o fluxo onde será criado o arquivo csv:
As variáveis que criamos anteriormente também vão dentro de “Do Until”:
Nessa etapa a seguir precisamos agrupar nossos arquivos csv, se não fizermos isso, vários arquivos serão criados e a ideia é que tenhamos todas as nossas informações em um único arquivo:
Por fim temos a etapa onde salvamos o nosso arquivo csv lá no sharepoint:
Seguindo essas etapas teremos nosso arquivo csv criado, no teste realizado pude extrair um volume maior do 150k linhas, vocês notarão que ao criar o arquivo haverá o cabeçalho se repetindo nas linhas abaixo, isso acontece porque estamos criando vários arquivos no fluxo e por fim agrupando todos os arquivos em um só.
Testem, qualquer dúvida estarei à disposição para ajudar. Até mais pessoal.