Guia completo de Integração Power Query - Excel, Power BI e DataFlows - com OneDrive Personal, OneDrive for Business e SharePoint
- Hugo Venturini
- 21 de mar.
- 24 min de leitura
Atualizado: 25 de mar.
Este artigo tem por objetivo sintetizar os métodos de conexão do Power Query, seja no Excel, ou no Power BI Desktop, com arquivos ou pastas localizadas no OneDrive, em suas duas versões, o OneDrive Personal e o OneDrive For Business, além do SharePoint, permitindo assim, uma revisão pontual sempre que necessária.
Destaca-se desde já, dada a ansiedade de muitos, que a versão Personal do OneDrive, que pode ser criada gratuitamente com e-mail pessoal de domínio “outlook.com”, “outlook.com.br”, entre outros, até o momento desta revisão inicial, não permite a conexão com pastas (subpastas ou repositórios), mas apenas diretamente com os próprios arquivos em si, em quaisquer formatos que estejam armazenados.
1. Conectando o Power Query a um arquivo armazenado no OneDrive Personal
Este é o método que estabelece a conexão ONLINE, ou seja, via link WEB do arquivo armazenado, o que difere da conexão local, que pode ser efetuada diretamente, pela pasta mapeada do OneDrive no Explorador de Arquivos do computador, como se esta fosse uma pasta qualquer do Windows. A diferença consiste na flexibilidade de atualização dos dados, uma vez que a conexão deixa de ser local, e passa a ser direta com a “nuvem” do OneDrive, ou seja, online. Ambos os métodos têm seus prós e contras, e estes precisam ser analisados pela perspectiva do problema que se deseja resolver.
Para a obtenção do link de conexão, é necessário acessar o arquivo objeto desta, diretamente pela página do OneDrive através do browser preferido. A partir de meados de 2024, a Microsoft passou a compactar a URL do arquivo, o que forçou uma mudança na forma de obtenção dela, em relação ao que era praticado até então.
Uma vez dentro da página do OneDrive Personal, deve-se clicar a esquerda, em “Meus Arquivos”, para acessar a seção de pastas, como indicado no passo 1, e então navegar através das subpastas até chegar ao arquivo com o qual se deseja estabelecer a conexão, como indicado no passo 2.
Com o arquivo visível no centro da tela, observe a caixa de seleção a esquerda do mesmo, como indicado no passo 3. Ela deve ser selecionada, para que o menu assistente fique visível na parte superior da tela.
Nele, procure pela opção “Compartilhar”, como mostrado no passo 4, e clique. Note que a mesma opção também aparece na linha do arquivo, como indicado.
Esta ação, abrirá a primeira janela de compartilhamento, através da qual é possível alterar a configuração deste, determinando se quem receberá o link poderá editar o arquivo, ou se somente poderá visualizá-lo. Para efeito de integração com o Power Query, esta configuração é irrelevante.
Nesta janela, basta clicar no botão “Copiar”, como indicado, para prosseguir para a próxima, que exibirá o link gerado:
É neste ponto que fica evidente a alteração de método descrita neste artigo: a URL obtida está parcialmente compactada, ocultando partes imprescindíveis à conexão pelo mecanismo do Power Query. O procedimento a partir daqui, é colar este mesmo link, em outra janela do navegador, para descompactá-la, e, por fim, visualizar as partes necessárias:
Portanto, abra uma nova guia no browser, e cole o link copiado, com o comando combinado “Ctrl+V”, como indicado. Em princípio, o link ficará igual, até que se clique “Enter”, para estabelecer a conexão e a abertura do arquivo online:
Clique novamente na barra de endereços, e copie a URL descompactada que estará, então, aparecendo. Utilize o bloco de notas ou outra aplicação para manipular este novo link:
https://onedrive.live.com/edit?id=CFD63A5F9117B312!961742&resid=CFD63A5F9117B312!961742&ithint=file%2cxlsx&authkey=!AOpJjfuOv0LCm94&wdo=2&cid=cfd63a5f9117b312
Para que o mecanismo do Power Query, através do conector “WEB”, consiga se conectar ao arquivo online, a URL deve seguir um determinado padrão, mostrado a seguir:
https://onedrive.live.com/download?cid= &resid= &authkey= &app=Excel
Será necessário manipular o texto, para compor o link definitivo, identificando os elementos no link descompactado obtido anteriormente, e substituindo no padrão.
1º - A raiz deve ser preservada:
https://onedrive.live.com/
2º - Após a barra “/”, o padrão de conexão pede o termo “download” sucedido pela interrogação (?); e após este sinal, a sequência “cid”, que vai desde o próprio termo, até o “E comercial” (&) seguinte:
cid=___________________&
Deve-se preencher esta lacuna com o código que se encontra, na URL pública, após o identificador “id”, destacado em amarelo abaixo, para melhor identificação:
https://onedrive.live.com/edit?id=CFD63A5F9117B312!961742&resid=CFD63A5F9117B312!961742&ithint=file%2cxlsx&authkey=!AOpJjfuOv0LCm94&wdo=2&cid=cfd63a5f9117b312
Note que a sequência é composta somente de LETRAS e NÚMEROS, sem caracteres especiais.
Até aqui:
https://onedrive.live.com/download?cid= CFD63A5F9117B312&
3º - Depois, vem a sequência “resid”, cujo preenchimento segue a mesma lógica:
resid=_______________________&
https://onedrive.live.com/edit?id=CFD63A5F9117B312!961742&resid=CFD63A5F9117B312!961742&ithint=file%2cxlsx&authkey=!AOpJjfuOv0LCm94&wdo=2&cid=cfd63a5f9117b312
Temos então:
https://onedrive.live.com/download?cid=CFD63A5F9117B312&resid= CFD63A5F9117B312!961742
Obs.: geralmente, as sequências “cid” e “resid”, são iguais, e também podem ser preenchidas de forma igual aqui, sem prejuízo da conexão.
4º - Por fim, a sequência “authkey”, que é diferente das outras, e que define a conexão:
https://onedrive.live.com/edit?id=CFD63A5F9117B312!961742&resid=CFD63A5F9117B312!961742&ithint=file%2cxlsx&authkey=!AOpJjfuOv0LCm94&wdo=2&cid=cfd63a5f9117b312
Esta sequência pode conter caracteres especiais.
Agregando:
https://onedrive.live.com/download?cid=CFD63A5F9117B312&resid= CFD63A5F9117B312!961742& authkey=!AOpJjfuOv0LCm94&wdo=2
Por fim, é necessário incluir a última parte:
&app=Excel
Para completar a URL:
https://onedrive.live.com/download?cid=CFD63A5F9117B312&resid=CFD63A5F9117B312!961742&authkey= !AOpJjfuOv0LCm94&wdo=2&app=Excel
É importantíssimo destacar que o OneDrive Personal não impõe camada de segurança para a aplicação. Ou seja, não será exigido de quem acessar a página WEB, login e senha, por exemplo. Do mesmo modo, utilizando o conteúdo do arquivo como fonte de dados no Power Query, não será exigido login e senha para a conexão direta, e consequentemente, para atualizações.
Em termos práticos, se você compartilhar seu projeto do Power BI Desktop, ou do Excel, que contém esta conexão com o arquivo fonte de dados armazenado no OneDrive Personal, com outro profissional, isto significa que este conseguirá fazer um login do tipo ANÔNIMO para manter a conexão do projeto, e permitir as atualizações da fonte de dados.
Este tipo de conexão NÃO é recomendado para uso corporativo que envolva dados sensíveis e/ou sigilosos.
Desta URL definitiva, vale ainda destacar, as características que permitem a conexão, e que diferem da URL pública, obtida no início do tutorial, com o compartilhamento do arquivo:
https://onedrive.live.com/download?cid=CFD63A5F9117B312&resid=CFD63A5F9117B312!961742&authkey= !AOpJjfuOv0LCm94&wdo=2&app=Excel
Uma vez com este link definitivo construído, pode-se estabelecer a conexão com o Power Query, por três diferentes meios: o Excel, o Power BI e os Fluxos de Dados.
Utilizando o Microsoft Excel:
Acesse ao menu “Dados”, passo 1, e nele, busque pelo grupo de comandos “Obter e Transformar dados”, localizado à esquerda, na Faixa de Opções. Neste grupo, clique no comando “Obter Dados”, passo 2 na imagem acima, para abrir o primeiro submenu, onde se deve procurar a opção “De outras Fontes”, passo 3; que por sua vez, abrirá o segundo submenu, onde por fim, estará a opção necessária, “Da Web”, passo 4, na qual se deve clicar.
Ao clicar nesta opção, todos os menus se fecharão, e a seguinte janela se abrirá ao centro da tela:
Deve-se clicar dentro da barra de texto, como indicado na imagem, com o botão esquerdo do mouse ou do touchpad, para habilitar o cursor.
Em seguida, basta utilizar o comando combinado “Ctrl+V”, para colar dentro desta barra, a URL alterada, que foi obtida no passo anterior:
https://onedrive.live.com/download?cid=CFD63A5F9117B312&resid=CFD63A5F9117B312!961742&authkey=!AOpJjfuOv0LCm94&wdo=2&app=ExcelEm seguida, basta clicar no botão “OK”, no rodapé da janela, para acionar o processamento da conexão. Esta janela será fechada, em um ou dois segundos, e uma nova se abrirá.
Nesta segunda janela pede-se a autenticação da conexão, que, como já esclarecido anteriormente, pode, neste caso, ser pelo modo “Anônimo”. Basta clicar no botão “Conectar” no canto direito inferior do rodapé da janela, para concluir o processo.
Se esta autenticação já estiver em cache na aplicação, esta janela não aparecerá, e o processo conduzirá diretamente a janela de navegação, que identifica os objetos contidos no arquivo conectado:
Esta janela mostrará todo o conteúdo do arquivo, que pode ser extremamente simples, contendo somente uma planilha, ou complexo, como neste exemplo, contendo diversas planilhas e Tabelas.
Neste ponto, deve-se definir qual objeto é o desejado para conexão e selecioná-lo, como mostra o passo 1; para que o respectivo preview dos dados apareça no lado direito da tela, mostrado no passo 2.
Por fim, deve-se clicar no botão “Transformar Dados”, indicado no passo 3 da imagem anterior, para finalizar a integração, e dar início ao processo de Preparação dos Dados, dentro da interface do Editor de Consultas do Power Query.
A partir deste ponto, e que não estará ilustrado neste tutorial, cada caso será diferente, demandando múltiplas e diversas etapas de tratamento até que se chegue ao volume de dados correto. Estas possibilidades serão exploradas de forma mais adequada em outro tutorial futuro, centrado na Preparação de Dados. Por ora, a integração, propósito deste tutorial, está consolidada.
Utilizando o Power BI Desktop:
No Power BI Desktop, o procedimento é basicamente o mesmo, uma vez que ele compartilha quase a totalidade da interface com o Excel.
No menu “Página Inicial”, mostrado no passo 1 da imagem anterior, deve-se buscar o grupo de comandos “Dados” – passo 2, e nele, o comando “Obter Dados” onde se deve clicar na parte inferior que contém o nome do comando. Dentre as opções que se abrirão abaixo, deve-se buscar pelo conector “WEB”, como mostrado no passo 3.
Isto abrirá ao centro da tela, a janela de conexão, onde, fazendo uso do comando combinado “Ctrl+V”, deve-se colar a URL tratada:
Deve-se finalizar clicando no botão “OK”, no rodapé da janela, para fechar a conexão, e abrir a janela de navegação dos objetos do arquivo fonte.
A única diferença notável em relação à janela do Microsoft Excel, é a presença de uma caixa de seleção à esquerda do nome do objeto escolhido, como mostrado no passo 1, da imagem anterior. Todo o restante do procedimento sucede-se da mesma forma, valendo as mesmas orientações e observações.
Utilizando o Fluxo de Dados do Serviço do Power BI (Power BI DataFlows):
Também é possível estabelecer esta conexão diretamente em um Fluxo de Dados do Serviço do Power BI.
Para criar um Fluxo de Dados, deve-se escolher o Workspace que irá abrigá-lo, e então, na parte superior esquerda da tela, clicar no botão “ + Novo Item”, destacado em vermelho na próxima imagem.
Em seguida, ao lado direito da tela, abrirá a janela de definição do recurso desejado, onde se deve escolher um “Fluxo de Dados Gen1”, como destacado na próxima imagem, que atenderá plenamente esta necessidade.
Isto fechará este assistente, e mostrará a tela da seguinte forma:
Observando em destaque a ilustração no centro da tela, deve-se clicar em “Definir novas tabelas”, destacado em vermelho, para acessar aos conectores.
O nome do conector, aqui, nesta interface, é diferente do Excel e do Power BI Desktop, o que pode, eventualmente, levar a alguma confusão por parte dos menos experientes; fato que motivou a inclusão desta rotina neste tutorial.
Aqui, o conector a ser escolhido chama-se “API Web”, como mostra em destaque, a imagem anterior.
Uma vez que se clique no botão do conector, a tela se modificará, apresentando o formato seguinte:
Aqui, onde está indicado em vermelho, deve-se colar a URL tratada, obtida no início.
Apenas o ato de aplicar o comando combinado “Ctrl+V” dentro da caixa de texto, modificará o layout da janela, para o que mostra a próxima imagem:
Note que foi determinado o modo anônimo como Tipo de Autenticação, e o botão “Próximo”, em verde, aparece habilitado no rodapé da tela. Basta clicar nele, para prosseguir.
A janela de navegação do Power Query Online, é praticamente idêntica à do Power BI Desktop, mostrando também a caixa de seleção à esquerda dos nomes dos objetos contidos no arquivo fonte de dados.
Deste modo, basta clicar no botão “Transformar Dados”, como indicado no passo 3 da imagem anterior, para prosseguir e adentrar a interface do Editor de Consultas com o volume de dados para a etapa de preparação.
2. Conectando o Power Query a um arquivo armazenado no OneDrive for Business ou no SharePoint.
Neste método, adequado aos pilares de segurança e governança de dados, uma vez que a versão corporativa do OneDrive, o OneDrive for Business, exige a autenticação do usuário no ato da conexão, serão observadas diversas diferenças no procedimento de conexão ao arquivo. Tudo o que se aplica ao OneDrive for Business, neste contexto, também se aplica ao SharePoint, portanto, tudo o que segue pode ser compreendido como aplicável à ambos.
Novamente, o que se faz necessário em primeiro lugar, é a obtenção do link de conexão ao arquivo que se deseja tornar uma fonte de dados. Isto pode se dar de duas formas diferentes.
A) Acessando o arquivo pelo navegador.
À esquerda da tela, procure e então clique no ícone de pasta, como mostrado no passo 1 da imagem anterior, para acessar seus arquivos. Navegue pela estrutura de pastas até encontrar o arquivo desejado, como mostra o passo 2.
Em seguida, ao centro da tela, selecione o arquivo desejado, como mostra o passo 3, e no canto superior direito procure o ícone de “Detalhes”, mostrado no passo 4. Ao clicar neste ícone, se abrirá do lado direito da tela, uma janela de informações onde se deverá identificar, na parte inferior da mesma, outro botão, “Mais detalhes”, mostrado no passo 5.
Este clique expandirá as informações de baixo para cima, mostrando o botão “Caminho”, destacado no passo 6, no qual se deve clicar para se obter a URL do arquivo. Ao clicar, tal informação será inserida na Área de Transferência do Windows, e se poderá colá-la em qualquer lugar para edição, como o Bloco de Notas, por exemplo:
A tela do SharePoint Online, pouco se diferenciará da tela do OneDrive for Business, como pode ser observado na próxima imagem:
Link obtido, em ambos os repositórios:
https://hlvjb-my.sharepoint.com/personal/contato_hugoventurini_com_br/Documents/DataBases/Comercial/Cadastro_de_Geografia/d_GeoBrasil_APIs.xlsx
Por este meio, não há edição a ser feita, basta aplicar o link diretamente na janela de conexão da tecnologia escolhida para tal: o Excel, o Power BI Desktop ou um Fluxo de Dados do Serviço Online do Power BI.
B) Acessando o arquivo pelo Desktop
Quando se tem o OneDrive for Business ou o SharePoint mapeados no Explorador de Arquivos do Windows, para acesso local, também é possível obter o link de conexão.
Em primeiro lugar, deve-se identificar o arquivo e abri-lo na aplicação Desktop respectiva:
Neste exemplo, trata-se de um arquivo Excel, de extensão “XLSX”, que é o mais comumente utilizado em rotinas como esta, objeto deste tutorial.
Com o arquivo aberto, clique no menu “Arquivo”, como sinalizado na imagem abaixo:
Isto conduzirá à janela de background da aplicação, onde se deve buscar, do lado esquerdo, a opção “Informações”, destacada no passo 1, na próxima imagem, e então clicar no botão “Copiar caminho”, indicado no passo 2.
Novamente, este clique inserirá na Área de Transferência do Windows o caminho para o arquivo, que neste caso específico, necessitará de um pequeno tratamento, antes de ser aplicado para conexão:
https://hlvjb-my.sharepoint.com/personal/contato_hugoventurini_com_br/Documents/DataBases/Comercial/Cadastro_de_Geografia/d_GeoBrasil_APIs.xlsx?web=1
A parte final do endereço, destacado em negrito e amarelo na sentença acima, precisa ser removida, para permitir o estabelecimento da conexão, como mostrado anteriormente.
https://hlvjb-my.sharepoint.com/personal/contato_hugoventurini_com_br/Documents/DataBases/Comercial/Cadastro_de_Geografia/d_GeoBrasil_APIs.xlsx
Uma vez que se obteve o LINK de conexão, por qualquer um dos dois meios mostrados até aqui, basta prosseguir.
No Excel proceda como na imagem:
Em seguida, na janela do conector, cole a URL e clique em “OK”:
Em seguida o procedimento se difere do que foi exposto anteriormente, em relação ao OneDrive Personal, pois a versão corporativa da aplicação, NÃO permite a conexão com autenticação do tipo anônima, e exigirá que se forneça as credenciais corporativas para prosseguir com ação:
Caso o processo de autenticação falhe de imediato, clique na opção “Entrar como um usuário diferente”, destacada no passo 2 da imagem anterior, para repetir ou confirmar o login, em uma outra janela auxiliar, como abaixo, ou pelo navegador, e conseguir, desta forma, estabelecer a conexão com o arquivo.
Feita a autenticação do usuário, basta clicar no botão “Conectar”, no rodapé da janela.
É importante destacar que, eventualmente, pode ser necessária a intervenção de um profissional da equipe de infraestrutura neste ponto, pois pode haver algum tipo de permissão boqueando a integração.
Daqui em diante, tudo o que foi estabelecido anteriormente como procedimento permanece válido.
Deve-se finalizar clicando no botão “Transformar Dados”, no rodapé da janela, e acessar a interface do Editor de Consultas do Power Query para os procedimentos de preparação dos dados.
O mesmo se aplica às conexões com o Power BI Desktop e com o Fluxo de Dados, em relação ao descrito anteriormente, quando explorada a conexão com o OneDrive Personal: todo o procedimento de integração é igual, mudando, somente, o link do arquivo, que possui outro formato, e a obrigatoriedade da autenticação de usuário do serviço do OneDrive.
2ª Parte: Transformando subpastas do OneDrive for Business ou SharePoint Online em repositórios de dados para o Power Query – Excel, Power BI Desktop e DataFlows.
Quando se trata de pequenos e médios negócios, principalmente, mas destacando que isto também pode ser observado em empresas de grande porte, é comum que arquivos em Excel ou do tipo CSV/TXT sejam utilizados como fontes de dados.
Para isto, pode-se transformar uma estrutura de diretórios do armazenamento online (OneDrive ou SharePoint), em repositórios, servindo desta forma, ao processo de atualização dos dados, seja pela substituição do arquivo no interior da pasta, ou pelo simples acréscimo de novos arquivos, que serão consolidados através de um proceso de combinação, compondo uma única tabela base, desde que estes obedeçam ao formato de layout e estrutura dos dados.
Com isto, tem-se uma solução acessível, de simples manutenção e baixo custo, e que contempla aspectos de segurança e governança de dados, uma vez que isto só é possível para contas corporativas.
A exemplo do que foi observado neste tutorial anteriormente, em relação à integração de arquivos no OneDrive Personal e no SharePoint, o que muda de fato, neste novo cenário, é o link utilizado para acesso, pois a partir de então, todo o procedimento é idêntico, tanto para o OneDrive for Business quanto para o SharePoint.
Em primeiro lugar, é necessário compreender que a conexão se dá com acesso WEB, e não local, e a primeira vantagem que se obtém aqui, é a dispensa da necessidade de um DataGateway para agendamento das atualizações pelo Serviço do Power BI, em caso de escolha desta interface. Se a conexão é com o Excel, para uso local, a atualização fica ao alcance de um único clique, muito simples e eficiente.
O primeiro passo, que determina o êxito ou fracasso da solução, é a correta identificação da parte da URL que de fato serve à integração.
Observando a tela padrão do OneDrive for Business:
A URL que aparece incialmente nesta tela, indicada na imagem anterior pela seta vermelha, NÃO serve à conexão e sua aplicação resultará em erro.
É necessário navegar até uma pasta ou subpasta específica, para que a barra de endereços mostre a URL mais longa, de onde se pode extrair o necessário. Deve-se clicar dentro da barra de endereços, e copiar tudo o que estiver lá:
https://hlvjb-my.sharepoint.com/my?id=%2Fpersonal%2Fcontato%5Fhugoventurini%5Fcom%5Fbr%2FDocuments%2FDataBases%2FComercial
Então, usando o Bloco de Notas, ou outra aplicação para textos, é necessário colar o resultado e tratá-lo para se obter algo como o mostrado a seguir:
https://hlvjb-my.sharepoint.com/personal/contato_hugoventurini_com_br/
Neste ponto, o SharePoint Online se mostra mais “amigável”, pois a obtenção do link é mais simples:
Na tela inicial, procure o site que servirá como repositório, no caso deste estudo, o “Sharefile”, indicado na imagem anterior. Uma vez identificado, deve-se clicar duas vezes para acessá-lo.
Neste cenário, o link desejado está pronto na barra de endereços do navegador. Basta clicar dentro dela, para selecionar, e então aplicar o comando combinado “Ctrl+C” para copiá-lo para a Área de Transferência do Windows. Em caso de já estar em outra pasta, ou em outro ponto de navegação dentro do SharePoint, basta clicar no ícone sinalizado no passo 2 da imagem anterior, para retornar ao ponto inicial.
O mais importante a se destacar aqui, é que a conexão se dá com a raíz do SharePoint ou OneDrive for Business, e não com alguma pasta dentro de sua estrutura. Em virtude desta característica, é fundamental ter atenção ao processo para evitar erros.
Até este momento, não há outro meio de conexão, que permita a conexão diretamente à uma subpasta dentro da estrutura.
Uma vez que se dispõe do link correto, deve se escolher onde se deseja estabelecer a conexão.
A conexão com o Microsoft Excel só está disponível, diretamente, em pacotes corporativos da assinatura do Microsoft 365, como o Microsoft 365 para Grandes Empresas. As assinaturas para estudantes e familiar, não possuem o conector necessário.
Utilizando o Excel do Microsoft 365 para Grandes Empresas, partindo de um arquivo em branco, deve-se acessar ao menu “Dados”, buscar o grupo de comandos “Obter e Transformar Dados”, e nele, o comando “Obter Dados”, no qual se deve clicar para abrir o submenu auxiliar.
Neste submenu auxiliar, deve-se buscar pela opção “De Arquivo”, apontar o cursor, e, então, na janela que se abrirá à direita, navegar até a opção “Da Pasta SharePoint”, na qual se deve clicar para abrir a janela de conexão.
Se a opção for pela conexão com o Power BI Desktop, o processo é um pouco diferente, como segue:
Partindo de um arquivo em branco, deve-se acessar ao menu “Página Inicial”, e buscar pelo grupo de comandos “Dados”, e nele, pelo comando “Obter Dados”, no qual se deve clicar diretamente sobre o ícone, como mostrado no passo 2, da próxima imagem:
Clicar diretamente sobre o ícone, automaticamente abrirá ao centro da tela, o menu completo de conectores, como ilustrado no passo 3. Neste menu, deve-se procurar a opção “Pasta do SharePoint”, indicada no passo 4 da imagem anterior.
Em ambas as aplicações, Excel e Power BI Desktop, as janelas que seguem ao processo de escolha do conector são praticamente idênticas:
E, em ambos os casos, basta colar o endereço no formato correto, na caixa de texto disponível, e finalizar clicando no botão “OK”, localizado no rodapé, à direita.
Eventualmente, poderá ser necessário autenticar o usuário logado novamente, para atender às normas de segurança:
Basta clicar em “Entrar como um usuário diferente”, para tal. Com esta etapa ultrapassada, ressaltando que novamente pode ser necessária aqui, a intervenção de alguém da infraestrutura para liberação das permissões necessárias, chega-se à etapa mais crítica do procedimento, que requer o máximo de atenção:
As janelas de navegação de objetos em ambas as tecnologias, são também, muito parecidas:
O ponto de atenção fundamental, já previamente mencionado, é que a conexão se estabelece com a raiz completa do SharePoint ou OneDrive for Business, o que implica que são todos os arquivos armazenados que estão disponíveis.
Neste ponto, o mau hábito de muitos usuários, de clicar direto nos botões “Carregar” ou “Combinar”, não somente resultará em erro da conexão, como, muito provavelmente causará severos problemas, dependendo da capacidade do computador onde se estiver executando o procedimento.
Aqui deve imperar a boa prática de desenvolvedor, e sempre, em qualquer hipótese, clicar na opção “Transformar Dados”, que fornece o total controle do que acontece nos passos seguintes.
Feita esta escolha, esta janela de navegação se fechará e o processo prossegue para a interface do Editor de Consultas do Power Query.
Em ambas as tecnologias, o primeiro passo aqui, para otimização completa da rotina, será a substituição da função da Linguagem M utilizada.
O conector utiliza a função “SharePoint.Files”, que, como o nome sugere, nos mostra todos os ARQUIVOS contidos no repositório, o que pode ser melhor constatado com a observação da coluna “Extension”, em ambas as últimas imagens.
Isto, a rigor, levaria o desenvolvedor a estabelecer uma complexa camada de filtros para se chegar a uma pasta específica, o que tornaria a rotina mais lenta, dado o volume de processamento.
Ao substituir a função padrão, pela função “SharePoint.Contents”, a visão do conteúdo muda de arquivos, para diretórios internos (subpastas), o que permite que se navegue de forma muito mais rápida e otimizada até o ponto em que se deseja estabelecer a conexão, além de permitir que esta, seja feita diretamente com uma subpasta, organizada dentro de uma estrutura, e que pode ser abastecida periodicamente com novos arquivos, otimizando a atualização dos dados para o modelo final.
O passo seguinte, é o de acessar a partição “Documentos Compartilhados” – ressaltando que esta nomenclatura pode mudar de acordo com o ambiente e a organização da empresa.
Isto pode ser feito de várias formas diferentes, e a mais simples e prática, é clicando sobre o termo “Table”, na coluna “Content”, à esquerda do nome da partição, e indicada na imagem anterior pela seta verde.
Neste exemplo, ilustrativo da funcionalidade, foi realizada uma navegação, usando sempre este método mencionado acima, em quatro níveis: acessando os “Documentos”, então a subpasta “DataBases”, depois “Comercial”, e por fim “Vendas” – no que se desenhou uma estrutura de armazenamento à qual pode-se facilmente aplicar regras de segurança de acesso.
Como mostra a imagem anterior, a subpasta “Vendas”, último nível da hierarquia criada, contém vários arquivos, todos em Excel, de extensão “XLSX”, com dados que obedecem ao mesmo padrão de layout e natureza.
Deste modo, para finalizar de forma adequada este tutorial, será mostrada, para os usuários de nível básico e intermediário, a forma mais eficiente de transformar todos estes arquivos, em uma única base de dados. Usuários avançados poderão explorar, posteriormente, outras formas de combinar as funções e passos, como exercício de otimização.
Constatando que todos os arquivos estão disponíveis na janela do Power Query, como mostram as próximas imagens, o método mais eficiente a ser aplicado é o de alternância de filtros e funções para manter a rotina livre de possíveis erros futuros.
Deve-se clicar com o botão direito do mouse ou do touchpad sobre o cabeçalho da coluna “Content”, e no menu que se abrirá em seguida, escolher a opção “Remover outras colunas”
A coluna “Content”, em virtude da forma utilizada aqui para conexão, indireta, contém os arquivos que estão no interior da pasta, em formato binário, que, por sua vez, não servem como fonte de dados, e precisam, portanto, ser convertidos novamente para o formato tabular. Isto será feito com a aplicação de uma função da Linguagem M, que corresponde à extensão destes arquivos. Neste caso específico, a função “Excel.Workbook”.
A exclusão das demais colunas, preservando somente a coluna “Content”, é um artifício para evitar a redundância desnecessária de dados que teriam que ser excluídos posteriormente, quando estariam em maior volume. Excluir estas colunas agora, torna o passo mais simples, e leve, o que proporciona um melhor entendimento da dinâmica de operação do Editor de Consultas do Power Query.
Depois, já com o layout limpo, deve-se acessar ao menu “Adicionar Coluna”, na parte superior da tela, e, no grupo de comandos “Geral”, buscar o comando “Coluna Personalizada”, que deve ser acionado para que a respectiva janela possa ser utilizada.
Novamente, em ambas as aplicações, esta janela é similar, quase idêntica:
Ela permite que se aplique, de forma mais prática e individual, as funções da Linguagem M diretamente sobre a base de dados, sem que se tenha que acessar a janela do Editor Avançado, para manipular o código completo que é gerado pelo mecanismo do Power Query, como resultado de todos os comandos que são aplicados.
Como já mencionado, aqui deve-se utilizar a função “Excel.Workbook”, em virtude da extensão dos arquivos contidos na pasta. Como se trata de arquivos de Excel, com extensão “XLSX” (poderia ser “XLS” também), esta função fará a conversão, transformando-os novamente em formato tabular.
Por analogia, deve-se destacar que ao se desenvolver outras rotinas similares, deve-se sempre observar a extensão dos arquivos que serão utilizados com fonte de dados, pois cada extensão possui sua própria função da Linguagem M.
Se estiver utilizando arquivos de extensão “TXT” ou “CSV”, mais comum, deve-se utilizar a função “Csv.Document”.
Para arquivos de extensão “XML”, deve-se utilizar a função “Xml.Tables”.
Para arquivos do tipo “PDF”, deve-se utilizar a função “Pdf.Tables”, ressaltando que arquivos como estes, só devem ser utilizados em último caso, quando não houver mais nenhuma opção, pois qualquer rotina desenvolvida sobre eles, se torna lenta e pesada, em virtude de sua natureza. Arquivos PDF não foram, definitivamente, concebidos para servir como fontes de dados.
Voltando ao exemplo deste tutorial, as próximas imagens mostram como fica a janela de edição da função, não sem antes, deixar dicas de usabilidade que podem melhorar a produtividade.
Esta janela possui o auxílio do Intellisense, que auxilia na correta digitação das funções, respeitando sua sintaxe e o padrão case sensitive do Editor de Consultas do Power Query.
Ao utilizar o intellisense deve-se observar, livre de ansiedade e pressa, seu comportamento – iniciando a digitação do nome da função, ele começa a funcionar logo nos primeiros caracteres digitados:
Apenas os caracteres “E” e “X” foram digitados, e a janela do Intellisense, já apareceu.
O que se deve observar é que a função desejada se encontra na terceira linha da janela do Intellisense, e pode-se navegar dentro dela utilizando as setas do teclado, o que proporciona maior assertividade. Uma vez que a tarja azul claro estiver sobre a função desejada, basta clicar “TAB” ou “ENTER” para concluir a digitação da função. Usuários que preferem o mouse ou o touchpad, podem clicar sobre a função também.
Deve-se observar o padrão exigido pela Linguagem M, explícito aqui: o ponto separa o elemento do objeto da função: Excel é o elemento, e o “Workbook” (Pasta de Trabalho, em português), é o objeto manipulado; e ambos os termos estão grafados com a inicial maiúscula.
O passo seguinte, é abrir os parênteses, que conterão o argumento a ser passado para função tratar, que, neste caso, é a indicação da coluna “Content”.
As próximas imagens trazem como fica o preenchimento final desta janela assistente, e trata de outros aspectos:
O passo 1, mostra que o campo “Nome da nova coluna”, permite a utilização de um termo próprio, e mais adequado à documentação da etapa de tratamento.
O título no campo “Fórmula de coluna personalizada”, traz em seguida um ícone de mais informações, e abaixo, um link “Saiba mais sobre fórmulas do Power Query”, que dá acesso à documentação. Recomenda-se fortemente que se use estes recursos.
A função “Excel.Workbook”, possui outros argumentos que podem ser aplicados, mas neste tutorial será explorado somente o principal, pois os demais serão explorados de forma mais adequada em um texto próprio e específico ao processo de Preparação de Dados. Aqui, o foco será mantido no processo de Integração.
Se, ao observar o rodapé da janela, estiver evidente a frase “Nenhum erro de sintaxe detectado”, e o botão “OK” estiver habilitado, deve-se finalizar a rotina, clicando no mesmo.
Isto fechará a janela assistente, e mostrará o novo layout da tela, onde se observará agora, duas colunas, e não somente uma. Uma nova coluna, com o título “Converter”, surgiu, mostrando um elemento diferente em suas linhas: o termo “Table”, em lugar do termo “Binary”, mostrado na coluna “Content”.
Isto significa que a rotina de tratamento foi bem-sucedida, e os arquivos binários foram corretamente convertidos ao formato tabular.
A partir deste ponto, a coluna “Content”, deixa de ser necessária, e pode ser excluída das rotinas futuras, para evitar redundância.
Novamente, para isto, basta clicar com o botão direito no mouse ou do touchpad, sobre o cabeçalho da nova coluna, “Converter”, e escolher a opção “Remover outras colunas”, como feito anteriormente.
O passo seguinte, já na tela limpa, será clicar no ícone de expansão, localizado no canto direito do cabeçalho da coluna restante, para abrir todos os itens
No menu que se abrirá, clique no botão “OK” no rodapé:
A tela seguinte, mostrará todos os objetos contidos em cada arquivo que foi convertido:
Novamente se faz necessário observar que este panorama será diferente, e demandará diferentes tipos de tratamento, a cada novo projeto, por razões óbvias. Por este motivo, o processo de Preparação dos Dados será explorado a parte.
Neste caso específico, temos arquivos que contém dados em suas planilhas, que por sua vez, estão formatados como Tabelas (elemento do MS Excel), o que os torna objetos detectáveis pelo Power Query, por possuir suas propriedades específicas.
Isto facilita o processo de filtro, uma vez que na presença de dois objetos, observáveis na coluna “Kind” da imagem anterior, sendo um como “Sheet”, que corresponde à planilha, e outro como “Table”, que corresponde à Tabela, recomenda-se que se escolha o objeto “Table”.
Para isto, basta aplicarmos um filtro na referida coluna “Kind”, mantendo apenas os objetos “Table” visíveis e tratáveis:
Clique no botão de seta, na extremidade direita do cabeçalho da coluna “Kind”, como mostra o passo 1, e no menu suspenso, desmarcar a caixa de seleção referente ao objeto “Sheet” – passo 2; e então finalizar clicando no botão “OK”, como mostra o passo 3.
O efeito disto será a redução das linhas da tabela, com a manutenção somente dos objetos válidos:
Neste novo cenário, novamente é necessário limpar o layout para evitar dados redundantes, portanto, deve-se aplicar novamente o recurso de manter somente a coluna necessária visível, que neste caso, é a coluna “Data”, que contém, finalmente, os dados tabulares.
Observando novamente o layout limpo, basta clicar no botão de expansão, na extremidade direita do cabeçalho da coluna restante, para abrir o menu contextual que mostrará, desta vez, os nomes dos cabeçalhos definitivos das colunas da base de dados de exemplo.
Deve-se finalizar, clicando em “OK”.
A partir deste ponto o processo de Integração pode ser considerado finalizado, pois as etapas seguintes envolvem somente tratamentos e transformações que serão executadas nas mais variadas formas e com os mais variados recursos, a depender de cada caso em que se estiver trabalhando.
Em relação à integração utilizando um Fluxo de Dados do Serviço do Power BI, há uma pequena diferença nos primeiros passos, que serão mostrados a seguir.
Novamente, partindo de um Fluxo de Dados novo, uma vez escolhido o Workspace que o abrigará:
Deve-se clicar no botão “ + Novo Item” e na janela auxiliar, escolher um Fluxo de Dados Gen1, como já feito anteriormente.
E então clicar em “Adicionar novas tabelas”, na primeira ilustração que aparecerá na tela principal, do lado esquerdo, “Definir novas tabelas”, conseguindo assim, acessar os conectores:
Na janela seguinte, o conector a ser escolhido, é o da “Pasta do SharePoint” também:
Em seguida, na janela de conexão, deve-se inserir, no campo indicado na próxima imagem, e de forma semelhante a que foi feita em outros exemplos aqui ilustrados, a URL própria da raíz do SharePoint ou OneDrive for Business que se deseja conectar:
Dada a compatibilidade natural de todo o ambiente que se está explorando, o simples ato de colar a URL, modificará a tela, uma vez que esta deve ser reconhecida e associada à uma conexão padrão, bastando para prosseguir, clicar no botão “Próximo” em verde, no rodapé:
Se, eventualmente, for a primeira conexão deste tipo que estiver sendo estabelecida, bastará alguns cliques para configurá-la, posto que o usuário já estará logado:
Note, no passo 1, que não é necessário DataGateway para esta conexão, e que basta confirmar o usuário logado da conta organizacional, como mostra o passo 2. No passo 3, recomenda-se fixar a opção “Organizacional” como Nível de Privacidade dos dados.
A partir deste ponto, todas as etapas seguintes são idênticas às já descritas acima, para o Power BI, dada a similaridade do Power Query Online, e o Editor de Consultas da aplicação desktop:
O processo só voltará a ficar diferente, em sua finalização, uma vez que aqui, os dados serão “descarregados” para um Fluxo de Dados, ficando assim disponíveis para consulta tanto por arquivos do Power BI Desktop, pelo próprio Serviço Online do Power BI, e também pelo Microsoft Excel, via Power Query.
Basta clicar no botão “Transformar Dados”, destacado em verde no rodapé desta janela, para acessar a interface padrão do Power Query Online e repetir as etapas de conversão, extração e tratamento mínimo dos dados, como já exposto.
Ao final, deve-se finalizar clicando no botão “Salvar e Fechar”, indicado na próxima imagem:
E em seguida, nomear o Fluxo de Dados para que ele possa ser reconhecido e utilizado sempre que necessário, em projetos futuros.
Para efeito didático, foi decidido concentrar as instruções deste tutorial nos processos de Integração das Fontes de Dados nas tecnologias aqui abordadas, como explicado no início do mesmo, para otimizar o processo de revisão. A expectativa é de que este conteúdo seja útil ao maior número de desenvolvedores e estudantes possível. Sinta-se a vontade para utilzar a janela de comentários, para manifestar suas idéias, críticas e sugestões.
Bom dia, Excelente material, parabéns!