Compartilhar via

Excel

  • Artigo

Resumo

Item Descrição
Estado da liberação Disponibilidade geral
Produtos Excel
Power BI (Modelos semânticos)
Power BI (Fluxos de dados)
Malha (fluxo de dados Gen2)
Power Apps (Fluxos de dados)
Dynamics 365 Customer Insights
Analysis Services
Tipos de autenticação compatíveis Anônimo (online)
Basic (online)
Conta organizacional (online)
Documentação de referência da função Excel.Workbook
Excel.CurrentWorkbook

Observação

Alguns recursos podem estar presentes em um produto, mas não em outros devido a cronogramas de implantação e funcionalidades específicas do host.

Pré-requisitos

Para se conectar a uma pasta de trabalho herdada (como .xls ou .xlsb), o provedor OLEDB (ou ACE) do Mecanismo de Banco de Dados do Access é necessário. Para instalar esse provedor, acesse a página de download e instale a versão relevante (32 bits ou 64 bits). Caso não esteja instalado, o seguinte erro será exibido ao se conectar a pastas de trabalho herdadas:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 32-bit (or 64-bit) version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

A ACE não pode ser instalada em ambientes de serviço de nuvem. Portanto, se você estiver vendo esse erro em um host de nuvem (como o Power Query Online), será necessário usar um gateway que tenha a ACE instalada para se conectar aos arquivos herdados do Excel.

Funcionalidades com suporte

  • Importação

Conectar-se a uma pasta de trabalho do Excel por meio do Power Query Desktop

Para fazer a conexão desde o Power Query Desktop:

  1. Selecione Pasta de Trabalho do Excel na experiência para obter dados. A experiência para obter dados no Power Query Desktop varia entre aplicativos. Para saber mais informações sobre a experiência para obter dados no Power Query Desktop para seu aplicativo, acesse Onde obter dados.

  2. Procure e selecione a pasta de trabalho do Excel que você deseja carregar. Em seguida, selecione Abrir.

    Selecione a pasta de trabalho do Excel no Explorador de Arquivos.

    Se a pasta de trabalho do Excel estiver online, use o conector Web para se conectar à pasta de trabalho.

  3. Em Navegador, selecione as informações desejadas da pasta de trabalho e, em seguida, Carregar para carregar os dados ou Transformar dados para continuar transformando os dados no Editor do Power Query.

    Pasta de trabalho do Excel importada no Navegador do Power Query Desktop.

Conectar-se a uma pasta de trabalho do Excel por meio do Power Query Online

Para fazer a conexão desde o Power Query Online:

  1. Selecione a opção Pasta de Trabalho do Excel na experiência para obter dados. Cada aplicativo acessa de uma forma diferente a experiência para obter dados do Power Query Online. Para saber mais informações sobre como acessar a experiência para obter dados do Power Query Online pelo seu aplicativo, acesse Onde obter dados.

    Captura de tela da janela obter dados com a pasta de trabalho do Excel enfatizada.

  2. Na caixa de diálogo do Excel exibida, forneça o caminho para a pasta de trabalho do Excel.

    Captura de tela de informações de conexão para acessar a pasta de trabalho do Excel.

  3. Se necessário, selecione um gateway de dados local para acessar a pasta de trabalho do Excel.

  4. Se essa for a primeira vez que você acessa essa pasta de trabalho do Excel, selecione o tipo de autenticação e entre em sua conta (se necessário).

  5. Em Navegador, selecione as informações da pasta de trabalho desejadas e, em seguida, Transformar os dados para continuar transformando os dados no Editor do Power Query.

    Captura de tela da pasta de trabalho do Excel importada no navegador do Power Query Online.

Tabelas sugeridas

Se você se conectar a uma pasta de trabalho do Excel que não contém especificamente uma única tabela, o navegador do Power Query tentará criar uma lista sugerida de tabelas que você pode escolher. Por exemplo, considere o exemplo de pasta de trabalho a seguir que contém dados de A1 a C5, mais dados de D8 a E10 e mais de C13 a F16.

Captura de tela da pasta de trabalho do Excel com três conjuntos de dados.

Quando você se conecta aos dados no Power Query, o navegador do Power Query cria duas listas. A primeira lista contém toda a planilha da pasta de trabalho e a segunda lista contém três tabelas sugeridas.

Se você selecionar a planilha inteira no navegador, a pasta de trabalho será exibida como aparece no Excel, com todas as células em branco preenchidas com nulo.

Captura de tela do navegador com folha única exibida com nulos nas células vazias. Se você selecionar uma das tabelas sugeridas, cada tabela individual determinada pelo Power Query a partir do layout da pasta de trabalho será exibida no navegador. Por exemplo, se você selecionar a Tabela 3, os dados exibidos originalmente nas células C13 a F16 serão exibidos.

Captura de tela do navegador com a tabela 3 selecionada em Tabelas sugeridas e o conteúdo da tabela 3 exibido.

Observação

Se a planilha for alterada o suficiente, talvez a tabela não seja atualizada corretamente. Você pode corrigir a atualização importando os dados novamente e selecionando uma nova tabela sugerida.

Solução de problemas

Precisão numérica (ou "Por que meus números mudaram?")

Ao importar dados do Excel, você pode observar que determinados valores numéricos parecem mudar ligeiramente quando importados para o Power Query. Por exemplo, se você selecionar uma célula que contém 0,049 no Excel, esse número será exibido na barra de fórmulas como 0,049. Mas se você importar a mesma célula para o Power Query e selecioná-la, os detalhes da visualização a exibirão como 0,049000000000000002 (mesmo que na tabela de visualização esteja formatada como 0,049). O que está acontecendo?

A resposta é um pouco complicada e tem a ver com como o Excel armazena números usando algo chamado notação de ponto flutuante binário. A questão é que o Excel não pode representar determinados números com 100% de precisão. Se você abrir o arquivo .xlsx e examinar o valor real que está sendo armazenado, verá que, no arquivo .xlsx, 0,049 é armazenado como 0,049000000000000002. Esse é o valor que o Power Query lê do .xlsx e, portanto, o valor que aparece ao selecionar a célula no Power Query. (Para obter mais informações sobre precisão numérica no Power Query, acesse as seções "Número decimal" e "Número decimal fixo" de Tipos de dados no Power Query.)

Conexão a uma pasta de trabalho online do Excel

A conexão a um documento do Excel hospedado no Sharepoint pode ser feita por meio do conector da Web no Power BI Desktop, Excel e Fluxos de Dados e também com o conector do Excel em Fluxos de dados. Para obter o link para o arquivo:

  1. Abra o documento na Área de Trabalho do Excel.
  2. Abra o menu Arquivo, selecione a guia Informações e, em seguida, Copiar Caminho.
  3. Copie o endereço no campo Caminho do Arquivo ou URL e remova ?web=1 do final do endereço.

Conector ACE herdado

O Power Query lê pastas de trabalho herdadas (como .xls ou .xlsb) usando o provedor OLEDB do Mecanismo de Banco de Dados do Access (ou ACE). Por isso, você pode encontrar comportamentos inesperados ao importar pastas de trabalho herdadas que não ocorrem ao importar pastas de trabalho OpenXML (como .xlsx). A seguir, temos alguns exemplos comuns.

Formatação de valor inesperada

Devido à ACE, os valores de uma pasta de trabalho herdada do Excel podem ser importados com menos precisão ou fidelidade do que o esperado. Por exemplo, imagine que o arquivo do Excel contenha o número 1024,231, que você formatou para exibição como "1.024,23". Quando importado para o Power Query, esse valor é representado como o valor de texto "1.024,23" em vez de um número de fidelidade completa subjacente (1024,231). Isso ocorre porque, nesse caso, a ACE não apresenta o valor subjacente para o Power Query, mas apenas o valor exibido no Excel.

Valores nulos inesperados

Quando a ACE carrega uma planilha, ela examina as oito primeiras linhas para determinar os tipos de dados das colunas. Se as oito primeiras linhas não forem representativas das linhas posteriores, a ACE poderá aplicar um tipo incorreto a essa coluna e retornar nulos para qualquer valor que não corresponda ao tipo. Por exemplo, se uma coluna contiver números nas oito primeiras linhas (como 1000, 1001 e assim por diante), mas tiver dados não numéricos em linhas posteriores (como "100Y" e "100Z"), a ACE concluirá que a coluna contém números e quaisquer valores não numéricos serão retornados como nulos.

Formatação de valor inconsistente

Em alguns casos, a ACE retorna resultados completamente diferentes entre atualizações. Usando o exemplo descrito na seção de formatação, você pode ver de repente o valor 1024,231 em vez de "1.024,23". Essa diferença pode ser causada por ter a pasta de trabalho herdada aberta no Excel ao importá-la para o Power Query. Para resolver esse problema, feche a pasta de trabalho.

Dados do Excel ausentes ou incompletos

Às vezes, o Power Query falha ao extrair todos os dados de uma planilha do Excel. Essa falha geralmente é causada pela planilha ter dimensões incorretas (por exemplo, ter dimensões de A1:C200 quando os dados reais ocupam mais de três colunas ou 200 linhas).

Como diagnosticar as dimensões incorretas

Para exibir as dimensões de uma planilha:

  1. Renomeie o arquivo xlsx com uma extensão .zip.
  2. Abra o arquivo no Explorador de Arquivos.
  3. Navegue até xl\worksheets.
  4. Copie o arquivo xml para a planilha problemática (por exemplo, Sheet1.xml) do arquivo zip para outro local.
  5. Inspecione as primeiras linhas do arquivo. Se o arquivo for pequeno o suficiente, abra-o em um editor de texto. Se o arquivo for muito grande para ser aberto em um editor de texto, execute o seguinte comando em um Prompt de Comando: more Sheet1.xml.
  6. Procure uma marca <dimension .../> (por exemplo, <dimension ref="A1:C200" />).

Se o arquivo tiver um atributo de dimensão que aponte para uma única célula (como <dimension ref="A1" />), o Power Query usará esse atributo para localizar a linha inicial e a coluna dos dados na planilha.

No entanto, se o arquivo tiver um atributo de dimensão que aponte para várias células (como <dimension ref="A1:AJ45000"/>), o Power Query usará esse intervalo para localizar a linha e a coluna inicial, bem como a linha e a coluna finais. Se esse intervalo não contiver todos os dados na planilha, alguns dados não serão carregados.

Como corrigir dimensões incorretas

Você pode corrigir problemas causados por dimensões incorretas executando uma das seguintes ações:

  • Abra e salve novamente o documento no Excel. Essa ação substituirá as dimensões incorretas armazenadas no arquivo pelo valor correto.

  • Verifique se a ferramenta que gerou o arquivo do Excel foi corrigida para gerar as dimensões corretamente.

  • Atualize sua consulta M para ignorar as dimensões incorretas. A partir da versão de dezembro de 2020 do Power Query, Excel.Workbook dá suporte para uma opção InferSheetDimensions. Quando true, essa opção fará com que a função ignore as dimensões armazenadas na Pasta de Trabalho e, em vez disso, determine-as inspecionando os dados.

    Aqui está um exemplo de como fornecer esta opção:

    Excel.Workbook(File.Contents("C:\MyExcelFile.xlsx"), [DelayTypes = true, InferSheetDimensions = true])

Desempenho lento ao carregar dados do Excel

O carregamento lento de dados do Excel também pode ser causado por dimensões incorretas. No entanto, nesse caso, a lentidão é causada pelo fato das dimensões serem muito maiores do que precisam ser, em vez de serem muito pequenas. Dimensões excessivamente grandes farão com que o Power Query leia uma quantidade muito maior de dados da pasta de trabalho do que o necessário.

Para corrigir esse problema, você pode consultar Localizar e redefinir a última célula em uma planilha para obter as instruções detalhadas.

Desempenho ruim ao carregar dados do SharePoint

Ao recuperar dados do Excel no computador ou no SharePoint, considere o volume dos dados envolvidos, bem como a complexidade da pasta de trabalho.

Você observará a degradação do desempenho ao recuperar arquivos muito grandes do SharePoint. No entanto, essa é apenas uma parte do problema. Se você tiver uma lógica de negócios significativa em um arquivo do Excel sendo recuperado do SharePoint, essa lógica poderá ter que ser executada quando você atualizar seus dados, o que pode causar cálculos complicados. Considere agregar e pré-calcular dados ou mover mais da lógica de negócios para fora da camada do Excel e para a camada do Power Query.

Erros ao usar o conector do Excel para importar arquivos CSV

Embora os arquivos CSV possam ser abertos no Excel, eles não são arquivos do Excel. Em vez disso, use o conector Text/CSV.

Erro ao importar pastas de trabalho "Planilha Open XML estrita"

Você pode ver o seguinte erro ao importar pastas de trabalho salvas no formato "Planilha Open XML estrita" do Excel:

DataFormat.Error: The specified package is invalid. The main part is missing.

Esse erro acontece quando o driver ACE não está instalado no computador host. As pastas de trabalho salvas no formato "Planilha Open XML estrita" só podem ser lidas pelo ACE. No entanto, como essas pastas de trabalho usam a mesma extensão de arquivo que as pastas de trabalho (.xlsx) Open XML regulares, não podemos usar a extensão para exibir a mensagem de erro the Access Database Engine OLEDB provider may be required to read this type of file usual.

Para resolver o erro, instale o driver ACE. Se o erro estiver ocorrendo em um serviço de nuvem, você precisará usar um gateway em execução em um computador que tenha o driver ACE instalado.

Erros de "O arquivo contém dados corrompidos"

Você pode ver o seguinte erro ao importar determinadas pastas de trabalho do Excel.

DataFormat.Error: File contains corrupted data.

Normalmente, esse erro indica que há um problema com o formato do arquivo.

No entanto, às vezes, esse erro pode acontecer quando um arquivo parece ser um arquivo Open XML (como .xlsx), mas o driver ACE é realmente necessário para processar o arquivo. Vá para a seção Conector ACE herdado para obter mais informações sobre como processar arquivos que exigem o driver ACE.

Limitações e problemas conhecidos

  • O Power Query Online não consegue acessar arquivos do Excel criptografados. Como os arquivos do Excel rotulados com tipos de confidencialidade diferentes de "Público" ou "Não Comercial" são criptografados, eles não são acessíveis por meio do Power Query Online.
  • O Power Query Online não tem suporte para arquivos do Excel protegidos por senha.
  • A opção Excel.Workbook useHeaders converte números e datas em texto usando a cultura atual e, portanto, se comporta de maneira diferente quando executada em ambientes com diferentes culturas de sistema operacional definidas. Em vez disso, recomendamos usar Table.PromoteHeaders .