Excel

Resumo

Item Descrição
Estado da versão Disponibilidade geral
Produtos Power BI (Conjuntos de dados)
Power BI (Fluxos de dados)
Power Apps (Fluxos de dados)
Excel
Customer Insights do Dynamics 365
Analysis Services
Tipos de autenticação com suporte Anônimo (online)
Básico (online)
Conta organizacional (online)
Documentação de referência de função Excel.Workbook
Excel.CurrentWorkbook

Observação

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

Pré-requisitos

Para se conectar a uma workbook herdada (como .xls ou .xlsb), é necessário ter o provedor access Mecanismo de Banco de Dados OLEDB (ou ACE). Para instalar esse provedor, acesse a página de download e instale a versão relevante (32 bits ou 64 bits). Se você não o tiver instalado, verá o seguinte erro ao se conectar a guias 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), precisará usar um gateway que tenha a ACE instalada para se conectar aos arquivos Excel herdados.

Funcionalidades com suporte

  • Importação

Conexão uma Excel de trabalho do Power Query Desktop

Para fazer a conexão do Power Query Desktop:

  1. Selecione a Excel na seleção do conector.

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

    Selecione a Excel de trabalho de Explorador de Arquivos.

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

  3. No Navegador, selecione as informações da lista de trabalho que você deseja e, em seguida, selecione Carregar para carregar os dados ou Transformar Dados para continuar transformando os dados Editor do Power Query.

    Excel de trabalho importada para Power Query Desktop Navigator.

Conexão uma Excel do Power Query Online

Para fazer a conexão do Power Query Online:

  1. Selecione a Excel na seleção do conector.

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

    Informações de conexão para acessar a Excel de trabalho.

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

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

  5. No Navegador, selecione as informações da lista de trabalho que você deseja e, em seguida, Transformar Dados para continuar transformando os dados Editor do Power Query.

    Excel de trabalho importada para Power Query navegador online.

<a name="troubleshooting">Solução de problemas

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

Ao importar Excel dados, você pode observar que determinados valores de número parecem mudar ligeiramente quando importados para Power Query. Por exemplo, se você selecionar uma célula contendo 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 versão prévia a exibirão como 0,0490000000002 (embora na tabela de visualização ela seja 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 linha inferior é que há determinados números que Excel não podem representar com precisão de 100%. Se você abrir o arquivo .xlsx e olhar para o valor real que está sendo armazenado, verá que, no arquivo .xlsx, 0,049 é realmente armazenado como 0,049000000000000000000000002. Esse é o valor Power Query leituras do .xlsx e, portanto, o valor que aparece quando você seleciona a célula Power Query. (Para obter mais informações sobre a precisão numérica Power Query, vá para as seções "Número decimal" e "Número decimal fixo" de Tipos de dados Power Query .)

Conectando-se a uma Excel online

Se você quiser se conectar a um documento Excel hospedado no Sharepoint, poderá fazer isso por meio do conector da Web no Power BI Desktop, Excel e Fluxos de dados e também com o conector Excel em Fluxos de Dados. Para obter o link para o arquivo:

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

Conector ACE herddo

Power Query ler as workbooks herdadas (como .xls ou .xlsb) usam o provedor OLEDB access Mecanismo de Banco de Dados (ou ACE). Por isso, você pode se deparar com comportamentos inesperados ao importar as guias de trabalho herdado que não ocorrem durante a importação de workbooks OpenXML (como .xlsx). Aqui estão alguns exemplos comuns.

Formatação de valor inesperado

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

Valores nulos inesperados

Quando a ACE carrega uma planilha, ela analisa as oito primeiras linhas para determinar os tipos de dados das colunas. Se as oito primeiras linhas não são representativas das linhas posteriores, ACE pode aplicar um tipo incorreto a essa coluna e retornar nulos para qualquer valor que não corresponder 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"), 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 as atções. Usando o exemplo descrito na seção de formatação, você pode ver repentinamente o valor 1024.231 em vez de "1.024.23". Essa diferença pode ser causada por ter a workbook herdada aberta no Excel ao importá-la para Power Query. Para resolver esse problema, feche a workbook.

Dados de Excel ausentes ou incompletos

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

Como diagnosticar dimensões incorretas

Para exibir as dimensões de uma planilha:

  1. Renomeie o arquivo xlsx com uma .zip de dados.
  2. Abra o arquivo em Explorador de Arquivos.
  3. Navegue até xl\planilhas.
  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: mais Sheet1.xml.
  6. Procure uma <dimension .../> marca (por exemplo, <dimension ref="A1:C200" /> ).

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

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

Como corrigir dimensões incorretas

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

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

  • Verifique se a ferramenta que gerou o arquivo Excel é 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 o agora dá suporte a uma InferSheetDimensions opção. Quando true, essa opção fará com que a função ignore as dimensões armazenadas na Workbook e, em vez disso, determine-as inspecionando os dados.

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

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

Desempenho lento ou lento ao carregar Excel dados

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

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

Baixo desempenho ao carregar dados de SharePoint

Ao recuperar dados de Excel em seu computador ou do SharePoint, considere o volume dos dados envolvidos, bem como a complexidade da workbook.

Você observará a degradação do desempenho ao recuperar arquivos muito grandes de SharePoint. No entanto, essa é apenas uma parte do problema. Se você tiver uma lógica de negócios significativa em um arquivo Excel sendo recuperado do SharePoint, essa lógica de negócios 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 Excel e para a camada Power Query dados.