Você pode ver datas nos resultados do Power Query exibidas incorretamente como 31/12/1899. Isso acontece quando o Power Query converte uma célula em branco ou com valor zero em data. O sistema interpreta o zero como o ponto inicial do sistema de número serial de data. Este artigo explica por que esse erro de conversão ocorre e fornece etapas claras para corrigi-lo.
Principais conclusões: Corrigindo o erro de data 31/12/1899
- Alterar o tipo de dados da coluna para Texto: Impede que o Power Query interprete erroneamente valores em branco ou zero como o número serial de data zero.
- Substituir valores nulos ou zero: Use o recurso Substituir Valores para trocar valores problemáticos por um branco verdadeiro antes de converter para data.
- Usar a função Table.TransformColumnTypes: Aplique código M avançado para lidar com erros de conversão e substituí-los por nulo.
Por que o Power Query exibe zero como 31/12/1899
O Excel e o Power Query usam um sistema de número serial de data onde cada data é armazenada como um número. A data base, ou dia zero, neste sistema é 30 de dezembro de 1899. O número 1 representa 31 de dezembro de 1899. Quando o Power Query importa dados, ele tenta detectar o tipo de dados correto para cada coluna. Se uma coluna contém principalmente datas, mas também possui células em branco ou células com valor zero, o Power Query pode atribuir o tipo de dados Data a toda a coluna.
O problema surge porque um branco verdadeiro nos dados de origem é lido como um valor nulo, mas uma célula contendo um zero é lida como o número zero. Quando o Power Query tenta converter o número zero em uma data, ele retorna o serial de data para o dia zero, que é 31/12/1899. Isso não é um bug, mas uma consequência de como o sistema de datas é projetado. A correção envolve limpar os dados antes que a conversão de tipo ocorra.
Etapas para corrigir o erro de data 31/12/1899
Siga estes métodos para limpar seus dados e evitar a conversão de data zero. Comece com o método mais simples primeiro.
Método 1: Alterar o tipo de dados para Texto primeiro
- Carregue seus dados no Editor do Power Query
Selecione sua tabela de dados no Excel, vá para a guia Dados e clique em De Tabela/Intervalo. Isso abre a janela do Editor do Power Query. - Altere a coluna problemática para o tipo Texto
Clique no ícone de tipo de dados ao lado do cabeçalho da coluna. Pode mostrar Data ou Qualquer. No menu suspenso, selecione Texto. Essa ação impede qualquer interpretação numérica automática dos valores. - Limpe os valores zero e em branco
Clique na seta suspensa no cabeçalho da coluna. Desmarque os valores (nulo) e 0 se estiverem listados, ou use os Filtros de Texto para removê-los. Alternativamente, você pode usar a ferramenta Substituir Valores. - Converta a coluna limpa para o tipo Data
Após remover zeros e nulos, clique novamente no ícone de tipo de dados da coluna. Desta vez, selecione Data. Apenas as entradas de texto válidas serão convertidas, evitando o erro de 1899. - Feche e carregue a consulta
Clique em Página Inicial > Fechar e Carregar. Seus dados corrigidos serão carregados em uma nova planilha sem as datas incorretas.
Método 2: Usar Substituir Valores antes de converter
- Abra o Editor do Power Query
Certifique-se de que seus dados com a coluna de data estejam carregados no editor. - Selecione a coluna e abra Substituir Valores
Clique com o botão direito no cabeçalho da coluna de data. Escolha Substituir Valores no menu de contexto. A caixa de diálogo Substituir Valores aparecerá. - Substitua zero por um espaço reservado nulo
No campo Valor a Localizar, digite 0. Deixe o campo Substituir Por vazio. Clique em OK. Isso altera todos os zeros para valores nulos, que o Power Query trata de forma diferente. - Converta a coluna para o tipo Data
Agora, altere o tipo de dados da coluna para Data. Os valores nulos permanecerão em branco e os números válidos serão convertidos para datas corretas.
Se o erro de data persistir após a limpeza
Power Query ainda mostra 1899 após alterar o tipo
Se o erro permanecer, as etapas aplicadas podem estar na ordem errada. No Editor do Power Query, observe o painel Etapas Aplicadas à direita. A etapa Tipo Alterado deve vir após quaisquer etapas de limpeza, como Valor Substituído. Você pode arrastar as etapas neste painel para reordená-las. Certifique-se de que Substituir Valores ou Linhas Filtradas ocorra antes da conversão de tipo para Data.
Dados de origem têm valores zero ocultos ou fórmulas
Sua tabela original do Excel pode conter células que parecem em branco, mas têm uma fórmula retornando uma string vazia ou um zero. O Power Query lê o resultado da fórmula, não a célula exibida. Antes de importar, converta essas fórmulas em valores estáticos. Copie a coluna em sua planilha de origem e use Colar Especial > Valores para remover as fórmulas. Em seguida, atualize sua conexão do Power Query.
Usando o Editor Avançado para tratamento de erros
Para controle total, você pode usar código M no Editor Avançado. Você pode modificar a etapa de conversão de tipo para lidar com erros. Procure uma linha de código como Table.TransformColumnTypes(#"Etapa Anterior",{{"SuaColuna", type date}}). Você pode substituí-la por uma versão mais robusta que substitui erros por nulo: Table.TransformColumnTypes(#"Etapa Anterior",{{ "SuaColuna", type date}}, "pt-BR"). Embora essa função específica não capture todos os erros, ela garante a formatação regional adequada. Para captura de erros complexa, pode ser necessário adicionar uma coluna personalizada com uma expressão try…otherwise.
Métodos de conversão de tipo de dados: Comparação
| Item | Alterar Tipo pela Interface | Substituir Valores Primeiro | Código M Avançado |
|---|---|---|---|
| Melhor para | Conjuntos de dados simples com espaços em branco óbvios | Conjuntos de dados contendo valores zero explícitos | Pipelines de dados complexos e recorrentes |
| Complexidade | Baixa | Média | Alta |
| Tratamento de Erros | Converte zero para 31/12/1899 | Previne o erro substituindo zero por nulo | Pode ser escrito para capturar e gerenciar todos os erros de conversão |
| Dependência de Ordem das Etapas | Crítica – deve limpar os dados primeiro | Crítica – a substituição deve ocorrer antes da alteração de tipo | Flexível – a lógica está incorporada na etapa de código |
| Confiabilidade na Atualização | Pode falhar se novos zeros aparecerem na origem | Confiável se a lógica de substituição cobrir todos os casos | Mais confiável se o código for escrito corretamente |
Agora você pode corrigir o erro de data 31/12/1899 alterando sua coluna para Texto primeiro ou usando a ferramenta Substituir Valores. Sempre certifique-se de que as etapas de limpeza de dados ocorram antes da conversão final de tipo para Data no painel Etapas Aplicadas. Para relatórios automatizados, considere adicionar uma etapa Substituir Valores que tenha como alvo zero para tornar sua consulta robusta contra futuros erros de entrada de dados. Use Ctrl + Clique para selecionar vários cabeçalhos de coluna se precisar aplicar a mesma correção a várias colunas de data de uma só vez.