Excel

Resumen

Requisitos previos

Para conectarse a un libro heredado (como .xls o .xlsb), se requiere el proveedor OLEDB (o ACE) del motor de base de datos de Access. Para instalar este proveedor, vaya a la página de descarga e instale la versión pertinente (de 32 o 64 bits). Si no lo tiene instalado, verá el siguiente error al conectarse a libros heredados:

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.

ACE no se puede instalar en entornos de servicio en la nube. Por lo tanto, si ve este error en un host de nube (como Power Query Online), deberá usar una puerta de enlace que tenga ACE instalado para conectarse a los archivos heredados de Excel.

Funcionalidades admitidas

• Importar

Conectarse a un libro de Excel desde Power Query Desktop

Establecimiento de la conexión desde Power Query Desktop:

1. Seleccione Libro de Excel en la experiencia de obtención de datos. La experiencia de obtención de datos en Power Query Desktop varía entre las aplicaciones. Para obtener más información sobre la experiencia de obtención de datos de Power Query Desktop para la aplicación, vaya a Dónde obtener datos.

2. Busque y seleccione el libro de Excel que desea cargar. A continuación, seleccione Abrir.

   

   Si el libro de Excel está en línea, use el conector web para conectarse al libro.

3. En Navegador, seleccione la información de libro que desee y después elija Cargar para cargar los datos o Transformar datos para seguir transformando los datos en el editor de Power Query.

   

Conectarse a un libro de Excel desde Power Query Online

Establecimiento de la conexión desde Power Query Online:

1. Seleccione la opción Libro de Excel en la experiencia de obtención de datos. Las distintas aplicaciones tienen diferentes formas de obtener datos en Power Query Online. Para obtener más información sobre cómo acceder a la experiencia de obtención de datos de Power Query Online desde la aplicación, vaya a Dónde obtener datos.

   

2. En el cuadro de diálogo de Excel que aparece, proporcione la ruta de acceso al libro de Excel.

   

3. Si es necesario, seleccione una puerta de enlace de datos local para acceder al libro de Excel.

4. Si es la primera vez que ha accedido a este libro de Excel, seleccione el tipo de autenticación e inicie sesión en su cuenta (si es necesario).

5. En Navegador, seleccione la información del libro que necesite y, a continuación, seleccione Transformar datos para continuar transformando los datos en el Editor de Power Query.

   

Tablas sugeridas

Si se conecta a un libro de Excel que no contiene específicamente una sola tabla, el navegador de Power Query intentará crear una lista sugerida de tablas entre las que puede elegir. Por ejemplo, considere el siguiente ejemplo de libro que contiene datos de A1 a C5, más datos de D8 a E10 y más de C13 a F16.

Al conectarse a los datos de Power Query, el navegador de Power Query crea dos listas. La primera lista contiene toda la hoja de libros y la segunda contiene tres tablas sugeridas.

Si selecciona toda la hoja en el navegador, el libro se muestra tal como apareció en Excel, con todas las celdas en blanco rellenadas con null.

Si selecciona una de las tablas sugeridas, cada tabla individual que Power Query pudo determinar a partir del diseño del libro se muestra en el navegador. Por ejemplo, si selecciona la tabla 3, se muestran los datos que aparecieron originalmente en las celdas C13 a F16.

Solución de problemas

Precisión numérica (o "¿Por qué cambiaron mis números?")

Al importar datos de Excel, puede observar que determinados valores numéricos parecen cambiar ligeramente cuando se importan en Power Query. Por ejemplo, si selecciona una celda que contiene 0,049 en Excel, este número se muestra en la barra de fórmulas como 0,049. Pero si importa la misma celda en Power Query y la selecciona, los detalles de la vista previa lo muestran como 0,049000000000002 (aunque en la tabla de vista previa tenga el formato 0,049). ¿Qué ocurre aquí?

La respuesta es un poco complicada y tiene que ver con cómo Excel almacena los números con algo denominado notación de punto flotante binario. En resumen: hay ciertos números que Excel no puede representar con una precisión del 100 %. Si abre el archivo .xlsx y examina el valor real que se almacena, verá que en el archivo .xlsx, 0,049 está realmente almacenado como 0,0490000000000000002. Este es el valor que Power Query lee de .xlsx y, por tanto, el valor que aparece al seleccionar la celda en Power Query. (Para obtener más información sobre la precisión numérica en Power Query, vaya a las secciones "Número decimal" y "Número decimal fijo" de Tipos de datos en Power Query.)

Conexión a un libro de Excel en línea

Si desea conectarse a un documento de Excel hospedado en SharePoint, puede hacerlo a través del conector Web en Power BI Desktop, Excel y Flujos de datos, y también con el conector de Excel en Flujos de datos. Para obtener el vínculo al archivo:

1. Abra el documento en Excel Desktop.
2. Abra el menú Archivo, seleccione la pestaña Información y, a continuación, seleccione Copiar ruta de acceso.
3. Copie la dirección en el campo Ruta de acceso de archivo o dirección URL y quite ?web=1 del final de la dirección.

Conector ACE heredado

Power Query lee los libros heredados (como .xls o .xlsb) mediante el proveedor OLEDB del motor de base de datos de Access (o ACE). Debido a esto, es posible que encuentre comportamientos inesperados al importar libros heredados que no se producen al importar libros OpenXML (como .xlsx). Estos son algunos ejemplos comunes.

Formato de valor inesperado

Debido a ACE, los valores de un libro heredado de Excel se pueden importar con menos precisión o fidelidad de lo esperado. Por ejemplo, imagine que el archivo de Excel contiene el número 1024,231, que ha formateado para que se muestre como "1024,23". Cuando se importa en Power Query, este valor se representa como el valor de texto "1024,23" en lugar de como el número de fidelidad completa subyacente (1024,231). Esto se debe a que, en este caso, ACE no expone el valor subyacente a Power Query, sino solo el valor que se muestra en Excel.

Valores nulos inesperados

Cuando ACE carga una hoja, examina las ocho primeras filas para determinar los tipos de datos de las columnas. Si las ocho primeras filas no son representativas de las filas posteriores, ACE puede aplicar un tipo incorrecto a esa columna y devolver valores nulos para cualquier valor que no coincida con el tipo. Por ejemplo, si una columna contiene números en las ocho primeras filas (como 1000, 1001, etc.), pero tiene datos no numéricos en filas posteriores (como "100Y" y "100Z"), ACE concluye que la columna contiene números y que los valores no numéricos se devuelven como nulos.

Formato de valor incoherente

En algunos casos, ACE devuelve resultados completamente diferentes en las actualizaciones. Con el ejemplo descrito en la sección de formato, es posible que de repente vea el valor 1024,231 en lugar de "1024,23". Esta diferencia puede deberse a que el libro heredado estaba abierto en Excel cuando se importó en Power Query. Para resolver este problema, cierre el libro.

Datos de Excel que faltan o están incompletos

A veces, Power Query no puede extraer todos los datos de una hoja de cálculo de Excel. Este error suele deberse a que la hoja de cálculo tiene dimensiones incorrectas (por ejemplo, dimensiones de A1:C200 cuando los datos reales ocupan más de tres columnas o 200 filas).

Cómo diagnosticar dimensiones incorrectas

Para ver las dimensiones de una hoja de cálculo:

1. Cambie el nombre del archivo xlsx con la extensión .zip.
2. Abra el archivo en el Explorador de archivos.
3. Vaya a xl\worksheets.
4. Copie el archivo xml de la hoja problemática (por ejemplo, Sheet1.xml) fuera del archivo ZIP en otra ubicación.
5. Inspeccione las primeras líneas del archivo. Si el archivo es lo suficientemente pequeño, ábralo en un editor de texto. Si el archivo es demasiado grande para abrirse en un editor de texto, ejecute el siguiente comando desde un símbolo del sistema: more Sheet1.xml.
6. Busque una etiqueta <dimension .../> (por ejemplo, <dimension ref="A1:C200" />).

Si el archivo tiene un atributo de dimensión que apunta a una sola celda (como <dimension ref="A1" />), Power Query usa este atributo para buscar la fila inicial y la columna de datos de la hoja.

Sin embargo, si el archivo tiene un atributo de dimensión que apunta a varias celdas (como <dimension ref="A1:AJ45000"/>), Power Query usa este intervalo para buscar la fila y la columna iniciales, así como la fila y la columna finales. Si este intervalo no contiene todos los datos de la hoja, algunos de los datos no se cargarán.

Cómo corregir dimensiones incorrectas

Puede corregir problemas causados por dimensiones incorrectas mediante una de las siguientes acciones:

• Abra y vuelva a guardar el documento en Excel. Esta acción sobrescribirá las dimensiones incorrectas almacenadas en el archivo con el valor correcto.

• Asegúrese de que la herramienta que generó el archivo de Excel se ha corregido para generar correctamente las dimensiones.

• Actualice la consulta M para omitir las dimensiones incorrectas. A partir de la versión de diciembre de 2020 de Power Query, Excel.Workbook admite una opción InferSheetDimensions. Cuando es true, esta opción hará que la función ignore las dimensiones almacenadas en el libro y, en su lugar, las determine inspeccionando los datos.

  El siguiente es un ejemplo de cómo se proporciona esta opción:

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

Retrasos o rendimiento lento al cargar datos de Excel

La carga lenta de datos de Excel también puede deberse a dimensiones incorrectas. Sin embargo, en este caso, la lentitud se debe a que las dimensiones son mucho mayores de lo que necesitan ser, en lugar de ser demasiado pequeñas. Las dimensiones demasiado grandes harán que Power Query lea una cantidad mucho mayor de datos del libro de los que realmente se necesitan.

Para corregir este problema, puede consultar Buscar y restablecer la última celda de una hoja de cálculo para obtener instrucciones detalladas.

Rendimiento deficiente al cargar datos desde SharePoint

Al recuperar datos de Excel en la máquina o desde SharePoint, tenga en cuenta tanto el volumen de los datos implicados, como la complejidad del libro.

Observará la degradación del rendimiento al recuperar archivos muy grandes de SharePoint. Sin embargo, esto es solo una parte del problema. Si tiene una lógica de negocios significativa en un archivo de Excel que se recupera de SharePoint, es posible que esta lógica de negocios tenga que ejecutarse al actualizar los datos, lo que podría provocar cálculos complicados. Considere la posibilidad de agregar y calcular previamente los datos o mover una parte mayor de la lógica de negocios fuera de la capa de Excel y a la capa de Power Query.

Errores al usar el conector de Excel para importar archivos CSV

Aunque los archivos CSV se pueden abrir en Excel, no son archivos de Excel. Use el conector Text/CSV en su lugar.

Error al importar libros "Hoja de cálculo Open XML estricta"

Es posible que vea el siguiente error al importar libros guardados en el formato "Hoja de cálculo Open XML estricta" de Excel:

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

Este error se produce cuando el controlador de ACE no está instalado en el equipo host. ACE solo puede leer los libros guardados en el formato "Hoja de cálculo Open XML estricta". Sin embargo, dado que estos libros usan la misma extensión de archivo que los libros Open XML normales (.xlsx), no podemos usar la extensión para mostrar el mensaje de error habitual the Access Database Engine OLEDB provider may be required to read this type of file.

Para resolver el error, instale el controlador de ACE. Si se produce el error en un servicio en la nube, deberá usar una puerta de enlace que se ejecute en un equipo que tenga instalado el controlador de ACE.

Errores de "Archivo contiene datos dañados"

Es posible que vea el siguiente error al importar determinados libros de Excel.

DataFormat.Error: File contains corrupted data.

Normalmente, este error indica que hay un problema con el formato del archivo.

Sin embargo, a veces este error puede producirse cuando un archivo parece ser un archivo Open XML (como .xlsx), pero realmente se necesita el controlador de ACE para procesarlo. Vaya a la sección Conector de ACE heredado para obtener más información sobre cómo procesar archivos que requieren el controlador de ACE.

Problemas y limitaciones conocidos

• Power Query Online no puede acceder a archivos cifrados de Excel. Dado que los archivos de Excel etiquetados con tipos de confidencialidad distintos de "Public" o "Non-Business" están cifrados, no son accesibles a través de Power Query Online.
• Power Query Online no admite archivos de Excel protegidos con contraseña.