Cómo introducir datos en Power Query: 5 fuentes de datos comunes
Bien, comencemos con Power Query. Lo primero que debemos hacer es introducir datos en Power Query. En esta serie, es nuestro primer vistazo real a Power Query; También vamos a utilizar esto como una oportunidad para explorar la interfaz de usuario.
Este post se estructura en dos partes principales:
- Usar datos en el mismo libro de trabajo: esto incluye una tabla de Excel, un rango con nombre, una matriz dinámica o un rango de hoja de cálculo
- Usar datos de archivos externos y libros de trabajo externos: esto incluye archivos CSV, archivos de texto o libros de Excel
A medida que avancemos en los ejemplos, espero que vea que obtener datos en Power Query es fácil.
Hay muchos otros conectores para cargar datos en Power Query, los conectores clave que cubriremos en secciones posteriores. Los principios para cargar datos serán relevantes para cualquier conector que no incluyamos.
Tenga en cuenta que Microsoft actualiza constantemente Excel y Power Query. Por lo tanto, dependiendo de su versión de compilación específica de Excel, los nombres, ubicaciones y opciones de varias acciones pueden diferir ligeramente de los ejemplos que se muestran en esta publicación.
Tabla de contenido
- Usar datos en el mismo libro de trabajo
- Importar datos de tablas
- Importar datos de rangos con nombre
- Importar datos de matrices dinámicas
- ¿Es mejor utilizar rangos o tablas con nombre?
- Importar datos de archivos externos y libros de Excel
- Archivo CSV
- Archivo de texto
- hojas de libro de excel
- Envolver
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre de archivo: 0089 Obtener datos en Power Query.zip
Tener acceso
La descarga incluye 5 archivos:
- Ejemplo 1: Tabla.xlsx
- Ejemplo 2: Rango con nombre.xlsx
- Ejemplo 3: CSV.csv
- Ejemplo 4: Texto.txt
- Ejemplo 5: Libro de trabajo de Excel.xlsx
Usar datos en el mismo libro de trabajo
El lugar más sencillo para comenzar es obtener datos del mismo libro. Power Query reconoce dos orígenes del mismo libro, tablas de Excel y rangos con nombre. También podemos obtener datos de derrames de matriz dinámica y rangos estándar utilizando estas dos fuentes.
Importar datos de tablas
Comencemos mirando las tablas de Excel; son los más fáciles de utilizar, ya que Power Query lo ve como un formato estructurado.
Es importante tener en cuenta que estoy usando "Tablas" para referirme específicamente a la función Tabla de Excel y no a los datos contenidos en celdas normales. Las tablas son un elemento vital de cómo le gusta a Excel almacenar datos y funcionan increíblemente bien con Power Query.
A continuación se utiliza el Ejemplo 1: Table.xlsx de las descargas .
Para obtener una tabla de Excel en Power Query:
- Seleccione cualquier celda de la tabla
- Haga clic en Datos de tabla/rango
Se abrirá la ventana de Power Query. Como este es nuestro primer vistazo a la interfaz de usuario, dedicaremos algún tiempo a comprender qué hay aquí.
- Cinta: similar a Excel, la cinta contiene los comandos principales agrupados en pestañas separadas. Usaremos la cinta a lo largo de esta serie. Aunque ahora parezca un poco extraño, podrás ver los comandos más comunes en acción.
- Panel de consultas : enumera todas las consultas dentro del libro. Se puede hacer clic en la pequeña flecha en la parte superior para expandir u ocultar el panel. Dependiendo de su versión de Excel, esto puede estar oculto o expandido de forma predeterminada.
- Barra de fórmulas : la barra de fórmulas muestra el código de cada paso de transformación. Dependiendo de su versión de Excel, esto puede estar oculto de forma predeterminada. Haga clic en Ver barra de fórmulas para hacerla visible.
- Vista previa: esta área muestra una vista previa de nuestros datos según el paso seleccionado dentro del cuadro de pasos aplicados. Podemos acceder a muchas de las transformaciones específicas de las columnas haciendo clic derecho en los encabezados de las columnas dentro de esta sección.
- Propiedades: aquí es donde nombramos la consulta. No hay nada útil en el nombre Consulta 1, por lo que es importante darle a su consulta un nombre significativo. Desea utilizar un nombre que describa lo que hace sin tener que mirar los pasos.
- Pasos aplicados: cada acción de transformación que realice se enumera en los Pasos aplicados. En este cuadro es posible agregar, eliminar, editar y cambiar el nombre de pasos.
Comprender los pasos aplicados
La ventana de pasos aplicados ya contiene una pequeña lista de elementos. Estos pasos son el intento de Excel de transformar automáticamente los datos en función de lo que cree que queremos hacer.
Fuente : identifica los datos de origen (por ejemplo, la tabla en la hoja de trabajo).
Tipo modificado : Excel analizó los datos e intentó aplicar el tipo de datos correcto a cada columna.
Estos pasos han sido aplicados automáticamente por Excel. Tenga cuidado, los pasos automáticos no siempre son correctos. Descubrí que el paso Cambiar tipo a menudo puede causar errores frustrantes más adelante, que son increíblemente difíciles de encontrar. A veces es más fácil eliminar el paso del tipo de cambio y cambiar los tipos de datos columna por columna.
Cuando llevamos a cabo cualquier acción de transformación, todas ellas se agregan a la lista de pasos aplicados.
Al hacer clic en cada paso dentro del cuadro de pasos aplicados, se cambia la ventana de vista previa; nos permite ver cómo cada paso cambia los datos.
Código M
M es el lenguaje que utiliza Power Query para registrar cada transformación que se produce. Es un lenguaje basado en funciones, por lo que tiene algunas similitudes con Excel. Sin embargo, dado que el propósito de Power Query es transformar datos en lugar de calcular valores, las funciones son muy diferentes a las de Excel.
Echemos un vistazo breve al código M para ver cómo Power Query ha registrado los pasos. No se preocupe, no dedicaremos mucho tiempo a analizar el código M, pero nos ayudará a comprender cómo funciona Power Query y cómo realizar ediciones más avanzadas en las consultas cuando tengamos más experiencia.
Haga clic en Inicio Editor avanzado (un método alternativo es Ver editor avanzado ).
Aparece la ventana del Editor avanzado, que muestra el código de los pasos aplicados.
Examinaremos brevemente el código y lo compararemos con los pasos aplicados. No dedicaremos mucho tiempo a analizar el código M, pero vale la pena echarle un vistazo breve para comprender qué está haciendo Power Query.
dejar
let es una palabra clave para identificar el comienzo de un segmento de código
Fuente = Excel.CurrentWorkbook(){[Name="tblSalesData"]}[Contenido]
La fuente determina qué tabla de Excel usar como datos de origen. Si queremos cambiar la Consulta para ver una Tabla diferente, podemos cambiar el texto "tblSalesData" por el nombre de la nueva Tabla de Excel.
#"Tipo modificado" = Table.TransformColumnTypes(Fuente,{{"Fecha", escriba fecha y hora}, {"Cliente", escriba texto}, {"Producto", escriba texto}, {"Vendido por", escriba texto}, {"Valor", Int64.Type}})
# El paso “Tipo cambiado” cambia los tipos de datos. Por ejemplo, la columna "Fecha" se cambió a un tipo de Fecha/Hora, y Cliente se cambió a un tipo de texto.
en#"Tipo cambiado"
in es una palabra clave para identificar el final del segmento de código. El texto que sigue a la declaración in es el nombre del paso devuelto en la consulta final.
Haga clic en Listo para cerrar el Editor avanzado. Eso es código M más que suficiente por ahora.
Agregar pasos de transformación
Realicemos algunas transformaciones básicas y veamos cómo se agregan a la lista de pasos aplicados.
Haga doble clic en la columna "Valor" y cambie el nombre a "Cantidad".
A continuación, haga clic en el icono Fecha/Hora y cámbielo a Fecha.
La columna Fecha ahora mostrará el ícono de fecha, no el ícono de fecha y hora.
Mire la ventana de Pasos Aplicados; Se suman dos nuevos pasos para nuestras acciones.
Eche un vistazo al código en el Editor avanzado nuevamente. Ahora podemos ver el código Power Query creado para las transformaciones adicionales.
Editando los pasos
Podemos pulsar en el símbolo X al lado del nombre para eliminar un paso. También podemos hacer clic derecho en un paso para ver todas las opciones de edición disponibles.
Estas opciones generalmente se explican por sí solas. Si hay alguno que no comprende, pruébelo con algunos datos de muestra... ¿qué es lo peor que puede pasar?
Cambiar nombre es probablemente el comando más útil aquí y el menos utilizado. A medida que la lista de pasos aplicados se hace más larga, puede resultar complicado recordar qué hace cada paso. Por lo tanto, es una buena práctica cambiar el nombre de los pasos cuando las consultas van más allá de unos pocos pasos (consulte la captura de pantalla a continuación).
Cargue los datos en Excel
Para completar el proceso. Volvemos a cargar los datos en el libro de Excel.
Haga clic en Inicio Cerrar carga (menú desplegable) Cerrar carga en… (nota: al hacer clic en Inicio Cerrar carga se aplicará la configuración predeterminada).
Se abre el cuadro de diálogo Importar datos. Seleccione las opciones para cargar los datos en una tabla en una nueva hoja de trabajo y luego haga clic en Aceptar .
Se creará una nueva hoja de trabajo con una tabla que muestra los datos transformados.
Probablemente aún no estés impresionado. Todo lo que hemos hecho es cargar una tabla de Excel en Power Query, realizar algunos cambios menores y luego volver a cargar esos datos en Excel. Recuerde, este es un ejemplo sencillo para mostrarle las operaciones básicas de la herramienta. A medida que avancemos en esta serie, convertiremos estos sencillos pasos en algo sorprendente... lo prometo.
Importar datos de rangos estándar
También podemos importar datos de un rango estándar a Power Query. Por ejemplo, si seleccionamos un rango normal (por ejemplo, Celdas A4:E54) y hacemos clic en Datos de tabla/rango , antes de importar, Excel cambiará automáticamente esas celdas a una tabla. Todo lo demás después de ese punto es idéntico a los pasos anteriores.
Vale la pena señalar que este proceso convertirá el rango en una tabla incluso si usted no lo desea. Excel también aplicará un nombre de tabla predeterminado, por lo que puede llamarse "Tabla1", lo cual no es particularmente útil. Sin embargo, como hemos visto anteriormente, si cambiamos el nombre de nuestra tabla más adelante, también podemos cambiar el nombre en el paso de origen de la consulta.
Importar datos de rangos con nombre
Hay otra forma de indicarle a Power Query el rango de datos que se va a importar; mediante el uso de rangos con nombre.
A continuación se utiliza el Ejemplo 2: Range.xlsx con nombre de las descargas.
La forma más sencilla de crear un rango con nombre es seleccionar todas las celdas , luego escribir el nombre en el cuadro Nombre y presionar Retorno .
En el archivo de ejemplo, ya se creó un rango con nombre denominado SalesData.
Importemos los datos del rango nombrado a Power Query. Los pasos son similares a importar datos de una Tabla, por lo que no repetiré toda la información anterior, pero resaltaré las diferencias.
Seleccione el rango con nombre en la lista desplegable Cuadro de nombre. El rango de celdas se seleccionará en la hoja de trabajo.
Haga clic en Datos de tabla/rango en la cinta.
Los datos importados tendrán un aspecto similar a cuando se utiliza una tabla. Sin embargo, puede haber un paso adicional aplicado.
En este ejemplo, Excel agrega un paso adicional de Promocionar encabezados automáticamente. Haga clic en los pasos aplicados para ver qué cambios se han aplicado.
El icono del engranaje
¿Notaste el pequeño ícono de ajustes al lado del paso Encabezados promocionados? Esto es para indicar dónde Power Query nos permite cambiar la configuración.
Haga clic en el ícono de ajustes.
Se abrirá la ventana Usar primera fila como encabezados. La ventana que se abre variará según el paso de transformación.
En nuestro ejemplo, todavía queremos la opción Promocionar todos los tipos escalares; por lo tanto, podemos hacer clic en Aceptar sin realizar ningún cambio.
Mostrar la barra de fórmulas
En lugar de mirar todo el código M en el Editor avanzado como lo hicimos antes, podemos ver el código paso a paso en la barra de fórmulas.
Si la barra de fórmulas no está visible, haga clic en Ver barra de fórmulas
A medida que hacemos clic en los pasos, la barra de fórmulas cambia y muestra el código M utilizado para cada paso. Inicialmente, no necesitamos preocuparnos por las funciones ya que la interfaz de usuario es excelente. A medida que tengamos más experiencia, aprenderá algunas funciones y cambios a lo largo del camino.
Importar datos de matrices dinámicas
Si tiene una versión de Excel habilitada para matrices dinámicas (Microsoft 365 2021 más adelante), puede usarla como origen de la consulta.
Haga clic en la primera celda del rango de derrame. Esto rodeará el rango de derrame con un cuadro azul. Haga clic en Datos de tabla/rango para cargar los datos en Power Query.
En segundo plano, Excel:
- crea un rango con nombre vinculado al rango de derrame
- crea una consulta basada en ese rango con nombre
Como la consulta se basa en un rango con nombre, todo lo demás continúa igual que cualquier otra consulta basada en un rango con nombre.
En este punto, vale la pena señalar que Excel ha creado un rango con nombre para nosotros y, por lo tanto, el nombre puede no ser muy útil; probablemente sea algo así como "FromArray_1". Si cambiamos el nombre del rango nombrado, debemos regresar a Power Query y editar el paso de origen en el Editor avanzado o en la barra de fórmulas.
¿Es mejor utilizar rangos o tablas con nombre?
Habiendo analizado dos opciones, rangos nombrados y Tablas, ¿cuál es la mejor? Desafortunadamente, no existe una respuesta sencilla, ya que depende de los datos de origen y del escenario.
Mesas
- Puede expandirse automáticamente a medida que se agregan nuevos datos en la parte inferior o derecha de la tabla.
- Debe estar en un formato de datos estructurados.
- Debe tener una única fila de encabezado definida
- No se pueden tener cálculos en la fila del encabezado
rangos con nombre
- No se expanda automáticamente a medida que se agregan nuevos datos. Por lo tanto, es posible que necesitemos cambiar el tamaño del rango nombrado a medida que se agregan nuevos datos.
- Puede contener cualquier cantidad de filas de encabezado (aunque requerirá más transformaciones en Power Query)
- Puede tener cálculos en la fila del encabezado.
Los rangos con nombres dinámicos también se pueden importar a Power Query; eso está fuera del alcance de esta introducción.
Mi consejo es optar siempre por una Mesa, salvo que haya un motivo concreto para no hacerlo.
Importar datos de archivos externos y libros de Excel
Habiendo analizado los datos almacenados en el mismo libro que la consulta, dirijamos nuestra atención a los datos almacenados fuera del libro.
A menudo, abrimos archivos CSV, archivos de texto o libros de Excel para copiar y pegar los datos en otro libro. Al importar los datos directamente al libro mediante Power Query, ¡nunca más tendremos que copiar y pegar! El enlace a los datos de origen se mantiene y se puede actualizar con solo hacer clic en un botón (más sobre esto en la próxima publicación).
Archivo CSV
CSV es un formato de archivo muy común para exportaciones desde otros sistemas. La buena noticia es que a Power Query le encantan los archivos CSV.
A continuación se utiliza el Ejemplo 3: CSV.csv de las descargas.
Comience con un nuevo libro de trabajo. Haga clic en Datos Obtener datos del archivo Desde texto/CSV
Se abre la ventana Importar datos . Navegue hasta el archivo CSV, selecciónelo y haga clic en Importar . Power Query abre una nueva ventana y muestra una muestra de datos.
Los archivos CSV, por definición, tienen un delimitador de coma, por lo que no debería ser necesario cambiar ninguna de estas configuraciones.
Para cargar los datos directamente en Excel sin ningún cambio podemos hacer clic en Cargar, pero no lo recomiendo. Siempre queremos ver cómo se ven nuestros datos, por lo que es mejor hacer clic en el botón Transformar datos . Todos los pasos a partir de ahora son similares a lo que hemos visto en las secciones anteriores.
Cargar datos en Excel
Una vez que los datos están en Power Query, seguimos los mismos pasos que se indicaron anteriormente para cargar esos datos como una tabla en un libro de Excel. Hay otros destinos en los que podemos cargar los datos y veremos los más populares en publicaciones futuras.
Archivo de texto
Los archivos de texto son similares a los CSV pero pueden tener una variedad más amplia de formatos de datos. Veamos un archivo de texto delimitado por tabulaciones para mostrar en qué se diferencia.
A continuación se utiliza el Ejemplo 4: Texto.txt de las descargas.
Siga los mismos pasos que para un archivo CSV. Sin embargo, en lugar de seleccionar la coma como delimitador, es probable que se seleccione Tabulador de forma predeterminada. Dependiendo del tipo de archivo, hay más opciones para elegir en el cuadro desplegable del delimitador.
Si los datos que está importando están en otro formato de texto, seleccione la opción apropiada de la lista desplegable. Luego, haga clic en Transformar datos para cargar los datos en Power Query.
hojas de libro de excel
Finalmente, para esta sección, importaremos el contenido de un libro de Excel.
A continuación se utiliza el Ejemplo 5: Excel Workbook.xlsx de la descarga.
Haga clic en Datos Obtener datos del archivo Libro de Excel . Navegue hasta su libro de Excel en la ventana Importar datos y haga clic en Importar .
Se abre la ventana Navegador de Power Query. Los archivos CSV y de texto contienen una única fuente. Un libro de Excel es diferente; puede tener varias hojas de trabajo, todas las cuales se tratan como objetos de datos separados.
Para este ejemplo, seleccione la hoja de trabajo de enero y haga clic en Transformar datos . Analizaremos la combinación de varios archivos en una publicación futura.
Ahora se nos presenta un escenario que no habíamos encontrado antes. Pero es un escenario que puede ocurrir en muchas circunstancias: Excel no sabe qué fila contiene los encabezados. Mire la captura de pantalla a continuación, Excel asumió que la fila 1 de la hoja de cálculo contiene los encabezados. Pero los encabezados deben ser Fecha, Cliente, Producto, Vendido por y Valor, que están en la fila 3.
Pero no te preocupes, podemos solucionar este problema rápidamente con algunas transformaciones simples.
Quitemos las 2 filas superiores. Haga clic en Inicio Quitar filas Quitar filas superiores . Se abre la ventana Eliminar filas superiores. Termine 2 en el campo de número de filas, luego haga clic en Aceptar .
A continuación, promocionamos la nueva fila superior para que sea la fila del encabezado haciendo clic en Transformar Usar primera fila como encabezados (nota: la misma transformación también está disponible en Uso doméstico Primera fila como encabezados ).
Eso es todo. Ahora tenemos el mismo formato de datos que en los ejemplos anteriores.
Envolver
Esta publicación nos muestra cómo cargar datos en Power Query utilizando fuentes comunes: CSV, texto, libros, tablas y rangos. Para muchos usuarios, estas son las únicas fuentes de datos que necesitarán utilizar. A través del proceso de importación y carga de Power Query, podemos erradicar muchas acciones de copiar y pegar utilizadas anteriormente.
Lea más publicaciones en la serie Introducción a Power Query
- Introducción a Power Query
- Obtener datos en Power Query: cinco fuentes de datos comunes
- DataRefresh Power Query en Excel: 4 formas de opciones avanzadas
- Utilice el editor de Power Query para actualizar consultas
- Conozca las opciones de Power Query Cerrar Carga
- Parámetros de Power Query: 3 métodos
- Transformaciones comunes de Power Query (más de 50 transformaciones poderosas explicadas)
- Anexar Power Query: combine rápidamente muchas consultas en 1
- Obtenga datos de una carpeta en Power Query: combine archivos rápidamente
- Listar archivos en una carpeta subcarpetas con Power Query
- Cómo obtener datos del libro actual con Power Query
- Cómo desvincular en Excel usando Power Query (3 formas)
- Power Query: valor de búsqueda en otra tabla con combinación
- Cómo cambiar la ubicación de los datos de origen en Power Query (7 formas)
- Fórmulas de Power Query (cómo usarlas y errores que se deben evitar)
- Declaración If de Power Query: condiciones anidadas ifs múltiples
- Cómo utilizar Power Query Group By para resumir datos
- Cómo utilizar las funciones personalizadas de Power Query
- Power Query: errores comunes, cómo solucionarlos
- Power Query: consejos y trucos
Deja una respuesta