Power Query: como consolidar Excel e SharePoint

Toda área vive uma pasta cheia de planilhas iguais, e alguém copia e cola todo mês#

Quase todo cliente que a gente atende tem a mesma cena: uma pasta com "Vendas Janeiro.xlsx", "Vendas Fevereiro.xlsx", "Vendas Março.xlsx", e um analista que abre uma por uma, copia o conteúdo e cola numa planilha mestre. Todo mês. Quando a fonte é uma biblioteca do SharePoint, o processo é o mesmo, só que com mais cliques. É aí que Power Query consolidar Excel e SharePoint deixa de ser tarefa manual e vira um processo que se atualiza sozinho.

Neste artigo mostro o passo a passo que a gente usa em projeto real para juntar várias planilhas de uma pasta local ou de uma biblioteca do SharePoint numa única tabela: o conector Pasta, o conector SharePoint Folder, a função de exemplo que o Power Query gera para combinar os arquivos, como filtrar por extensão e por nome, e os cuidados com layout inconsistente, a maior fonte de dor de cabeça desse tipo de solução.

O conector Pasta lê o diretório inteiro, não um arquivo só#

A primeira mudança de cabeça é parar de pensar em "abrir um arquivo" e passar a pensar em "apontar para uma pasta". O conector Pasta (Folder) não lê o conteúdo das planilhas de imediato. Ele lista os arquivos daquele diretório e devolve uma tabela de metadados: nome do arquivo, extensão, data de modificação, caminho da pasta e uma coluna especial chamada Content, que carrega o binário de cada arquivo.

No Power BI Desktop, o caminho é Obter dados, opção Pasta. Você informa o caminho do diretório e o Power Query mostra a lista de arquivos. A partir daí o trabalho é sempre o mesmo: filtrar os arquivos que interessam e mandar o Power Query combinar todos eles.

Vale conhecer a diferença entre os conectores de pasta antes de escolher:

Conector	O que faz	Quando usar
Pasta (Folder)	Lê os arquivos de um diretório local ou de rede	Planilhas numa pasta do computador ou num compartilhamento de rede
SharePoint Folder	Lê os arquivos de um site do SharePoint inteiro	Bibliotecas de documentos hospedadas no SharePoint Online
Pasta do OneDrive/SharePoint	Variações que apontam para bibliotecas específicas	Cenários com biblioteca única e sincronizada

O ponto importante: os três devolvem a mesma estrutura de tabela, com a coluna Content. O que muda é a origem. Depois de conectar, o passo a passo de consolidação é idêntico.

Filtrar antes de combinar evita que um arquivo errado quebre tudo#

Assim que a lista de arquivos aparece, resista à tentação de clicar em "Combinar" na hora. Numa pasta de produção quase nunca existem só as planilhas que interessam. Tem o arquivo temporário do Excel (aquele que começa com ~$), tem o PDF que alguém salvou junto, tem a versão antiga guardada numa subpasta. Se você mandar combinar tudo, o Power Query tenta ler um binário que não é planilha e a consulta quebra.

Por isso o primeiro passo depois de conectar é sempre filtrar. Os filtros que a gente mais usa:

1. Filtrar por extensão. Na coluna Extension, mantenha apenas .xlsx (ou .xls, conforme o caso). Isso já elimina PDF, CSV solto e outros formatos.
2. Filtrar por nome. Na coluna Name, use "não começa com" ~$ para descartar os arquivos temporários que o Excel cria enquanto alguém está com a planilha aberta.
3. Filtrar por pasta. Se o conector traz subpastas que você não quer, filtre a coluna Folder Path para manter só o diretório certo.
4. Filtrar por data. Em cenários de atualização incremental, dá para usar Date modified para limitar o volume, embora isso mereça um tratamento próprio.

O código M desses filtros é direto e vale entender, porque a interface esconde a lógica:

let
    Origem = Folder.Files("C:\Dados\Vendas"),
    FiltraExtensao = Table.SelectRows(
        Origem,
        each [Extension] = ".xlsx"
    ),
    FiltraTemporario = Table.SelectRows(
        FiltraExtensao,
        each not Text.StartsWith([Name], "~$")
    )
in
    FiltraTemporario

Um detalhe que economiza retrabalho: confira sempre a coluna Name depois de filtrar. Em pasta compartilhada é comum aparecer "Vendas Janeiro (cópia).xlsx" ou "Vendas Janeiro final.xlsx". Se essas cópias entrarem na consolidação, você duplica linhas sem perceber.

O Transform Sample File é a mágica, e também o ponto de atenção#

Aqui está o coração da consolidação. Depois de filtrar, você clica no botão de combinar arquivos, o ícone de duas setas para baixo no topo da coluna Content. O Power Query faz algo esperto: ele pega o primeiro arquivo da lista, usa como amostra e gera automaticamente uma função de exemplo (Transform Sample File, ou Transformar Arquivo de Amostra). Essa função descreve como abrir e transformar aquele arquivo, e o Power Query aplica exatamente os mesmos passos em todos os outros arquivos da pasta.

Quando você combina, o Power Query cria quatro objetos no painel de consultas:

Objeto	Função
Arquivo de Exemplo (Sample File)	O primeiro arquivo, usado como modelo
Parâmetro (Parameter)	A referência que aponta para o arquivo de amostra
Consulta de Função (Transform Sample File)	Os passos de transformação aplicados a cada arquivo
Consulta Final	A tabela consolidada com todos os arquivos combinados

Entender essa separação muda tudo na manutenção. Se você precisa ajustar como cada planilha é lida, por exemplo remover as três primeiras linhas ou promover a linha certa como título, você não mexe na consulta final. Você edita o Transform Sample File, e o ajuste se propaga para todos os arquivos automaticamente. Muita gente tenta corrigir na consulta final, se perde e culpa a ferramenta. O lugar certo é a função de exemplo.

Na hora de combinar, o Power Query pergunta qual objeto dentro do Excel você quer usar. Se cada arquivo tem uma aba chamada "Planilha1", escolha a aba. Mas atenção: se os nomes das abas variam entre os arquivos (um tem "Jan", outro tem "Janeiro"), selecionar pelo nome da aba vai falhar nos arquivos que não têm aquele nome. Nesses casos a gente costuma navegar pelo Kind = "Sheet" de forma mais genérica ou padronizar os nomes na origem antes.

Layout inconsistente entre planilhas é a maior fonte de erro#

Se este artigo tivesse uma única frase para levar, seria esta: a consolidação não quebra pela ferramenta, quebra pelo layout. O Power Query combina arquivos assumindo que todos têm a mesma estrutura. No mundo real, quem preenche as planilhas é gente, e gente muda coisa.

Os problemas de layout que mais aparecem em projeto:

• Colunas em ordem diferente. Em fevereiro alguém trocou "Produto" e "Quantidade" de lugar. O Power Query combina por posição na etapa bruta, então os dados entram na coluna errada.
• Nome de coluna diferente. "Vlr Total" num arquivo, "Valor Total" em outro, "Total" num terceiro. Depois de promover cabeçalhos, viram colunas distintas e os valores se espalham.
• Linhas de cabeçalho a mais. Um arquivo tem um título e uma linha em branco antes da tabela; outro já começa na tabela. Remover "as 2 primeiras linhas" de forma fixa funciona num e destrói o outro.
• Abas com nomes variados. Já citado, e um clássico.
• Colunas extras em alguns arquivos. Uma unidade acrescentou "Observação" no meio da tabela. Isso desloca tudo à direita.

A estratégia que a gente adota para blindar contra isso:

1. Padronize a origem sempre que possível. Um template de planilha único, com colunas fixas e trancadas, resolve boa parte dos problemas antes de virar problema. É a solução mais barata, mesmo que exija combinar com quem preenche.
2. Promova cabeçalhos dentro do Transform Sample File, não na consulta final, e pelo nome, não pela posição.
3. Padronize nomes de coluna com uma etapa de renomear ou, em casos difíceis, use Table.SelectColumns para forçar só as colunas que interessam.
4. Não confie em "remover N linhas" fixo. Se o número de linhas de cabeçalho varia, filtre por conteúdo (por exemplo, remover linhas onde a coluna de data é nula) em vez de contar posições.

Um exemplo de blindagem simples: em vez de deixar o Power Query trazer todas as colunas na ordem que vierem, force o conjunto que você espera.

let
    Combinado = Table.Combine(ListaDeArquivos),
    SoColunasCertas = Table.SelectColumns(
        Combinado,
        {"Data", "Produto", "Quantidade", "Valor Total"},
        MissingField.UseNull
    )
in
    SoColunasCertas

O parâmetro MissingField.UseNull faz o Power Query preencher com nulo quando uma coluna esperada não existe naquele arquivo, em vez de derrubar a consulta. É um respiro, mas não substitui a padronização da origem.

Para saber quem trouxe cada linha, mantenha a coluna Source.Name que o Power Query cria automaticamente. Ela guarda o nome do arquivo de origem e é ouro na hora de rastrear um valor estranho até a planilha certa. Esse cuidado com estrutura de dados é o mesmo que aplicamos em projetos de engenharia de dados mais robustos.

SharePoint Folder é igual, com um detalhe: as credenciais#

Consolidar de uma biblioteca do SharePoint segue exatamente a mesma lógica: conectar, filtrar, combinar com o Transform Sample File. A diferença mora em dois pontos práticos.

O primeiro é o endereço. No conector SharePoint Folder você não informa o caminho de uma pasta específica, e sim a URL raiz do site (algo como https://suaempresa.sharepoint.com/sites/Comercial). O Power Query lista todos os arquivos daquele site, de todas as bibliotecas. Por isso, no SharePoint, filtrar pela coluna Folder Path deixa de ser opcional e vira obrigatório: é assim que você isola a biblioteca e a pasta certas.

O segundo ponto, e o que mais gera chamado de suporte, é a autenticação. Ao conectar pela primeira vez, o Power Query pede as credenciais. Escolha Conta organizacional e faça login com o usuário Microsoft 365 que tem acesso à biblioteca. Aqui vale um alerta que a gente repete em todo projeto:

• No Desktop, funciona com a sua conta. Você está logado, tem acesso, a consulta atualiza.
• No Serviço do Power BI, a atualização agendada precisa de credenciais próprias. Depois de publicar, você configura as credenciais do conjunto de dados nas configurações do workspace, novamente como conta organizacional. Se pular esse passo, a atualização falha com erro de credencial, e o relatório fica congelado no último refresh do Desktop.

A tabela abaixo resume onde cada configuração acontece:

Etapa	Onde	O que configurar
Conectar e desenvolver	Power BI Desktop	Login com conta organizacional
Publicar	Power BI Desktop	Publicar no workspace
Atualização agendada	Serviço (nuvem)	Credenciais organizacionais do conjunto de dados
Fonte local (conector Pasta)	Serviço	Gateway de dados local instalado e configurado

Repare na última linha: se a origem for uma pasta local ou de rede, a atualização na nuvem exige um gateway de dados instalado numa máquina que enxergue aquele caminho. O SharePoint Online, por ser nuvem, não precisa de gateway, só das credenciais certas. Essa diferença costuma decidir a arquitetura do cliente. Se você quer se aprofundar em como o gateway funciona, temos um material específico sobre Power BI para empresas no Brasil.

Power Query consolidar Excel e SharePoint vira um fluxo que se atualiza sozinho#

O grande ganho não é a tabela consolidada de hoje. É que, no mês que vem, quando alguém jogar "Vendas Abril.xlsx" na pasta ou na biblioteca, o relatório vai incorporar aquele arquivo sozinho, sem ninguém copiar e colar nada. Basta atualizar. Você montou uma vez e o processo se sustenta.

Para escalar isso na organização toda, muita gente move essa lógica para um dataflow, centralizando a consolidação num lugar só e reaproveitando em vários relatórios. Quando o volume cresce ou a governança aperta, é hora de pensar em arquitetura de dados de verdade, que é onde a nossa equipe costuma entrar.

Perguntas frequentes#

Qual a diferença entre o conector Pasta e o SharePoint Folder? O conector Pasta lê os arquivos de um diretório local ou de rede. O SharePoint Folder lê os arquivos de um site do SharePoint Online inteiro, a partir da URL raiz do site. A mecânica de consolidação é idêntica: os dois devolvem uma tabela com a coluna Content e você combina os arquivos da mesma forma. A diferença prática está na origem e na forma de autenticar.

O Power Query combina arquivos de formatos diferentes na mesma pasta? Ele tenta, e é por isso que quebra. A função de exemplo é gerada a partir do primeiro arquivo e assume que os demais têm a mesma estrutura. Se você misturar .xlsx com .csv ou .pdf, a consulta falha. Por isso o primeiro passo é sempre filtrar pela coluna de extensão antes de combinar.

Preciso alterar cada arquivo quando a leitura está errada? Não. Você edita a consulta de função chamada Transform Sample File, aquela que o Power Query cria automaticamente ao combinar. Qualquer ajuste feito lá, como promover cabeçalhos ou remover linhas de topo, se propaga para todos os arquivos de uma vez. Mexer na consulta final arquivo por arquivo é o erro mais comum de quem está começando.

Por que a atualização funciona no Desktop e falha no Serviço? Porque são contextos de autenticação diferentes. No Desktop você usa a sua sessão logada. No Serviço, a atualização agendada precisa de credenciais próprias configuradas no conjunto de dados, normalmente conta organizacional para o SharePoint. Além disso, se a fonte for uma pasta local, o Serviço exige um gateway de dados instalado numa máquina que enxergue aquele caminho.

Como sei de qual arquivo veio cada linha depois de consolidar? Mantenha a coluna Source.Name, que o Power Query cria sozinho ao combinar arquivos. Ela guarda o nome do arquivo de origem de cada linha. É a forma mais rápida de rastrear um número estranho até a planilha exata que o gerou, o que economiza muito tempo na hora de investigar inconsistências.

Vale mais consolidar direto no relatório ou usar um dataflow? Para um relatório único, consolidar direto no Power Query resolve. Quando várias equipes precisam da mesma base, um dataflow centraliza a lógica num lugar só, evita retrabalho e melhora a governança. A escolha depende de quantos relatórios vão reaproveitar a consolidação e de quão crítica é a padronização.

Monte uma vez e deixe o processo trabalhar por você#

Consolidar Excel e SharePoint no Power Query não é difícil quando você separa as três coisas que importam: filtrar antes de combinar, tratar a leitura no Transform Sample File e blindar contra layout inconsistente na origem. O resto o próprio Power Query faz. Se a sua área ainda vive de copiar e colar planilhas todo mês, ou se a atualização no Serviço vive quebrando, fale com a gente. A gente monta o fluxo certo com você e deixa ele rodando sozinho.