Excel

Resumo

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 este provedor, vá para 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 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.

O ACE não pode ser instalado 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 o ACE instalado para se conectar aos arquivos herdados do Excel.

Capacidades suportadas

• Importar

Ligar a um livro do Excel a partir do Power Query Desktop

Para estabelecer a ligação a partir do Power Query Desktop:

1. Selecione Pasta de Trabalho do Excel na experiência de obter dados. A experiência de obtenção de dados no Power Query Desktop varia entre aplicações. Para obter mais informações sobre a experiência de obter dados do Power Query Desktop para a sua aplicação, aceda a Onde obter dados.

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

   

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

3. No Navegador, selecione as informações do livro que pretende e, em seguida, selecione Carregar para carregar os dados ou Transformar Dados para continuar a transformar os dados no Editor do Power Query.

   

Ligar a um livro do Excel a partir do Power Query Online

Para estabelecer a ligação a partir do Power Query Online:

1. Selecione a opção de pasta de trabalho do Excel na experiência de obter dados. Diferentes aplicações têm formas diferentes de aceder à experiência de obtenção de dados do Power Query Online. Para obter mais informações sobre como aceder à experiência de obter dados do Power Query Online a partir da sua aplicação, aceda a Onde obter dados.

   

2. Na caixa de diálogo do Excel exibida, forneça o caminho para 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 esta for a primeira vez que acede a este livro do Excel, selecione o tipo de autenticação e inicie sessão na sua conta (se necessário).

5. No Navegador, selecione as informações do livro que pretende e, em seguida , Transformar Dados para continuar a transformar os dados no Editor do Power Query.

   

Tabelas sugeridas

Se ligar a um Livro do Excel que não contenha especificamente uma única tabela, o navegador do Power Query tentará criar uma lista sugerida de tabelas entre as quais 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.

Quando se liga aos dados no Power Query, o navegador do Power Query cria duas listas. A primeira lista contém toda a folha de 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 apareceu no Excel, com todas as células em branco preenchidas com null.

Se selecionar uma das tabelas sugeridas, cada tabela individual que o Power Query conseguiu determinar a partir do esquema do livro é apresentada no navegador. Por exemplo, se você selecionar Tabela 3, os dados que apareceram originalmente nas células C13 a F16 serão exibidos.

Resolução de Problemas

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

Ao importar dados do Excel, poderá notar que determinados valores numéricos parecem mudar ligeiramente quando importados para o 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 importar a mesma célula para o Power Query e a selecionar, os detalhes da pré-visualização apresentam-na como 0,04900000000000002 (apesar de na tabela de pré-visualização estar formatada como 0,049). O que se passa aqui?

A resposta é um pouco complicada e tem a ver com a forma como o Excel armazena números usando algo chamado notação binária de vírgula flutuante. A conclusão é que existem certos números que o Excel não consegue representar com 100% de precisão. Se você abrir o arquivo .xlsx e olhar para o valor real que está sendo armazenado, você verá que no arquivo .xlsx, 0.049 é realmente armazenado como 0.049000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 Este é o valor que o Power Query lê do .xlsx e, portanto, o valor que aparece quando seleciona a célula no Power Query. (Para obter mais informações sobre a precisão numérica no Power Query, vá para as secções "Número decimal" e "Número decimal fixo" de Tipos de dados no Power Query.)

Ligar a um livro online do Excel

Se você quiser se conectar a um documento do Excel hospedado no Sharepoint, poderá fazê-lo 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 no Excel Desktop.
2. Abra o menu Arquivo , selecione a guia Informações e, em seguida, selecione Copiar caminho.
3. Copie o endereço para o campo Caminho do arquivo ou URL e remova o ?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 se deparar com comportamentos inesperados ao importar pastas de trabalho herdadas que não ocorrem ao importar pastas de trabalho OpenXML (como .xlsx). Aqui estão 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 você espera. Por exemplo, imagine que seu arquivo do Excel contém o número 1024.231, que você formatou para exibição como "1.024.23". Quando importado para o Power Query, este 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 apresenta o valor subjacente ao Power Query, mas apenas o valor como ele é exibido no Excel.

Valores nulos inesperados

Quando a ACE carrega uma folha, examina as primeiras oito 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 contém números nas oito primeiras linhas (como 1000, 1001 e assim por diante), mas tem dados não numéricos em linhas posteriores (como "100Y" e "100Z"), o ACE conclui que a coluna contém números e quaisquer valores não numéricos são retornados como nulos.

Formatação de valores inconsistente

Em alguns casos, a ACE retorna resultados completamente diferentes entre as 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

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

Como diagnosticar dimensões incorretas

Para exibir as dimensões de uma planilha:

1. Renomeie o arquivo xlsx com uma extensão .zip.
2. Abra o ficheiro no Explorador de Ficheiros.
3. Navegue até xl\worksheets.
4. Copie o arquivo xml da 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 .../> tag (por exemplo, <dimension ref="A1:C200" />).

Se o ficheiro tiver um atributo de dimensão que aponte para uma única célula (como <dimension ref="A1" />), o Power Query utiliza este atributo para localizar a linha e coluna iniciais dos dados na folha.

No entanto, se o ficheiro tiver um atributo de dimensão que aponte para várias células (como <dimension ref="A1:AJ45000"/>), o Power Query utiliza este intervalo para localizar a linha e coluna iniciais, bem como a linha e coluna finais. Se esse intervalo não contiver 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 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 está corrigida para produzir 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 agora suporta uma InferSheetDimensions opção. Quando true, essa opção fará com que a função ignore as dimensões armazenadas na pasta de trabalho e, em vez disso, determiná-las 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 dados do Excel

O carregamento lento de dados do Excel também pode ser causado por dimensões incorretas. No entanto, neste caso, a lentidão é causada pelas dimensões serem muito maiores do que precisam ser, em vez de serem muito pequenas. Dimensões demasiado grandes farão com que o Power Query leia uma quantidade muito maior de dados do Livro 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 do SharePoint

Ao recuperar dados do Excel em sua máquina ou do SharePoint, considere o volume dos dados envolvidos, bem como a complexidade da pasta de trabalho.

Você notará degradação do desempenho ao recuperar arquivos muito grandes do SharePoint. No entanto, esta é 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 de negócios pode 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 da camada do Excel para a camada do Power Query.

Erros ao usar o conector do Excel para importar arquivos CSV

Embora os ficheiros CSV possam ser abertos no Excel, não são ficheiros Excel. Em vez disso, use o conector Texto/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.

Este 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 pela ACE. No entanto, como essas pastas de trabalho usam a mesma extensão de arquivo que as pastas de trabalho Open XML normais (.xlsx), não podemos usar a extensão para exibir a mensagem de erro usual the Access Database Engine OLEDB provider may be required to read this type of file .

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 "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.

Problemas e limitações conhecidos

• O Power Query Online não consegue aceder a ficheiros Excel encriptados. Uma vez que os ficheiros do Excel rotulados com tipos de sensibilidade diferentes de "Público" ou "Não Comercial" são encriptados, não são acessíveis através do Power Query Online.
• O Power Query Online não suporta ficheiros Excel protegidos por palavra-passe.