Combinar datos de varios orígenes de datos (Power Query)

Importante:  Este artículo se ha traducido con traducción automática; vea la declinación de responsabilidades. Para su referencia, puede encontrar la versión en inglés de este artículo aquí.

Nota: Power Query se conoce como Obtener y transformar en Excel 2016. La información que se proporciona aquí se aplica a ambos. Para obtener más información, vea Obtener y transformar en Excel 2016.

En este tutorial, usará el Editor de consultas de Power Query para importar datos desde un archivo local de Excel que contiene información del producto y que fuente de OData contiene información de pedido de producto. Siga los pasos de transformación y agregación y combinar datos de ambos orígenes para generar un informe de Ventas totales por producto y año.

Para realizar este tutorial, necesita el libro Productos y Pedidos. En el cuadro de diálogo Guardar como, póngale al archivo el nombre Productos y Pedidos.xlsx.

En este tutorial

Tarea 1: Importar productos a un libro de Excel

Paso 1: Conectar con un libro de Excel

Paso 2: Subir la primera fila a los encabezados de columna de una tabla

Paso 3: Eliminar otras columnas para mostrar únicamente las columnas de interés

Pasos de Power Query creados

Paso 4: Importar una consulta de productos

Tarea 2: Importar datos de pedidos desde una fuente de OData

Paso 1: Conectar con una fuente de OData

Paso 2: Expandir una tabla Detalles_Pedido

Vínculo Expandir la tabla Detalles_Pedido

Paso 3: Eliminar otras columnas para mostrar únicamente las columnas de interés

Eliminar columnas seleccionadas

Paso 4: Calcular el total de línea de cada fila de Detalles_Pedido

Calcular el total de línea de cada fila de Detalles_Pedido

Paso 5: Transformar una columna de año FechaPedido

Paso 6: Agrupar las filas por Id. de producto y año

Paso 7: Importar una consulta de productos

Resultados de la consulta final

Pasos de Power Query creados

Paso 8: Deshabilitar descargas de consultas a un libro de Excel

Deshabilitar la descarga de una consulta

Tarea 3: Combinar las consultas de productos y ventas totales

Paso 1: Combinar el Id. de producto con una consulta de ventas totales

Paso 2: Expandir una columna combinada

Vínculo Expandir la tabla NuevaColumna

Pasos de Power Query creados

Paso 3: Cargar una consulta de ventas totales por producto en un modelo de datos de Excel

Cargar la consulta de ventas totales por producto en el modelo de datos de Excel

Consulta final de ventas totales por producto

Tarea 1: Importar productos a un libro de Excel

En esta tarea, importa productos del archivo Productos y Pedidos.xlsx en un libro de Excel.

Paso 1: Conectar con un libro de Excel

  1. Cree un libro de Excel.

  2. En la pestaña POWER QUERY de la cinta de opciones, haga clic en Desde archivo > Desde Excel.

  3. En el cuadro de diálogo Examinar de Excel, busque o escriba la ruta de Productos y pedidos.xlsx para importar o vincular a un archivo.

  4. En el panel Navegador, haga doble clic en la hoja de cálculo Productos o haga clic en Productos y luego en Editar consulta. Al editar una consulta o conectarse a un nuevo origen de datos, aparece la ventana Editor de consultas.

    Nota: Para ver un vídeo muy breve sobre cómo mostrar el Editor de consultas, vea la parte final de este artículo.

Paso 2: Subir la primera fila a los encabezados de columna de una tabla

En la cuadrícula Vista previa de consulta, la primera fila de la tabla no contiene los nombres de columna de la tabla. Para subir la primera fila a los encabezados de columna de la tabla:

  1. Haga clic en el icono de tabla ( icono de tabla ) en la esquina superior izquierda de la vista previa de los datos.

  2. Haga clic en Usar primera fila como encabezados.

Subir la primera fila a los encabezados de columna de una tabla

Paso 3: Eliminar otras columnas para mostrar únicamente las columnas de interés

En este paso, eliminará todas las columnas excepto IdProducto, NombreProducto, IdCategoría y CantidadUnidad.

  1. En la cuadrícula Vista previa de consulta, seleccione las columnas IdProducto, NombreProducto, IdCategoría y CantidadUnidad (mediante Ctrl+clic o Mayús+clic).

  2. En la cinta del Editor de consultas, haga clic en Quitar columnas > Quitar otras columnas o haga clic con el botón secundario en un encabezado de columna y haga clic en Quitar otras columnas.

    Ocultar otras columnas

Pasos de Power Query creados

A medida que realiza actividades de consulta en Power Query, se crean pasos que aparecen en el panel Configuración de consulta, en la lista PASOS APLICADOS. A cada paso de consulta le corresponde una fórmula de Power Query, también conocida como lenguaje "M". Para obtener más información sobre el lenguaje de formulación de Power Query, consulte Obtener información sobre fórmulas de Power Query.

Tarea

Paso de consulta

Fórmula

Conectarse a un libro de Excel

Origen

Origen{[Nombre="Productos"]}[Fecha]

Subir la primera fila a los encabezados de columna de una tabla

FirstRowAsHeader

Table.PromoteHeaders

(Productos)

Eliminar otras columnas para mostrar únicamente las columnas de interés

RemovedOtherColumns

Table.SelectColumns

(FirstRowAsHeader,{"IdProducto", "NombreProducto", "IdCategoría", "CantidadUnidad"})

Paso 4: Importar una consulta de productos

En este paso, debe importar la consulta Productos en el libro de Excel.

  1. En la cinta de opciones Editor de consultas, haga clic en Aplicar y cerrar. Los resultados aparecen en una nueva hoja de cálculo de Excel.

Principio de página

Tarea 2: Importar datos de pedidos desde una fuente de OData

En esta tarea, importa datos en el libro de Excel desde la fuente de OData de ejemplo Northwind en http://services.odata.org/Northwind/Northwind.svc.

Paso 1: Conectar con una fuente de OData

  1. En la ficha de cinta de POWER QUERY, haga clic en Desde otros orígenes > Desde fuente de OData.

  2. En el cuadro de diálogo Fuente de OData, escriba la dirección URL de la fuente de OData

  3. Haga clic en Aceptar.

  4. En el panel Navegador, haga doble clic en la tabla Pedidos o haga clic en Pedidos y luego en Editar.

Nota: Al pasar el mouse sobre una tabla, se muestra un control flotante de vista previa de la tabla.

Pasar el mouse sobre un origen de datos

Paso 2: Expandir una tabla Detalles_Pedido

En este paso, expandirá la tabla Detalles_Pedido relacionada con la tabla Pedidos, para combinar las columnas IdProducto, PrecioUnidad y Cantidad de la tabla Detalles_Pedido en la tabla Pedidos. La operación Expandir combina las columnas de una tabla relacionada en una tabla de asuntos. Cuando se ejecute la consulta, las filas de la tabla relacionada (Detalles_Pedido) se combinan en las filas de la tabla de asuntos (Pedidos).

En Power Query, una columna que contiene un vínculo a una tabla relacionada tiene un vínculo Entrada o un vínculo Tabla. Un vínculo Entrada lleva a un único registro relacionado y representa una relación uno a uno con una tabla de asuntos.Un vínculo Tabla lleva a una tabla relacionada y representa una relación uno a varios con una tabla de asuntos. Un vínculo representa las propiedades de navegación de un origen de datos dentro de un modelo relacional. En el caso de una fuente de OData, las propiedades de navegación representan una entidad con una asociación de clave externa. En una base de datos, como SQL Server, las propiedades de navegación representan las relaciones de clave externa de la base de datos.

Vínculo Expandir la tabla Detalles_Pedido

Después de expandir la tabla Detalles_Pedido, se agregan tres nuevas columnas y más filas a la tabla Pedidos, una por cada fila de la tabla relacionada o anidada.

  1. En el panel Vista previa de consulta, desplácese hasta la columna Detalles_Pedido.

  2. En la columna Detalles_Pedido, haga clic en el icono de expandir ( Expanda ).

  3. En el menú despegable Expandir:

    1. Haga clic en (Seleccionar todas las columnas) para borrar todas las columnas.

    2. Haga clic en IdProducto, PrecioUnidad y Cantidad.

    3. Haga clic en Aceptar.

      Vínculo Expandir la tabla Detalles_Pedido

      Nota: En Power Query, puede expandir las tablas vinculadas a una columna y realizar operaciones de agregado en las columnas de la tabla vinculada antes de expandir los datos en la tabla de asuntos. Para obtener más información sobre cómo realizar operaciones de agregado, consulte Agregar datos de una columna.

Paso 3: Eliminar otras columnas para mostrar únicamente las columnas de interés

En este paso, eliminará todas las columnas excepto FechaPedido, IdProducto, PrecioUnidad y Cantidad. En la tarea anterior, usted ha usado Eliminar otras columnas. En esta tarea, eliminará las columnas seleccionadas.

Eliminar columnas seleccionadas

  1. En el panel Vista previa de consulta, seleccione todas las columnas:

    1. Haga clic en la primera columna (IdPedido).

    2. Con la tecla Mayús presionada, haga clic en la última columna (Transportista).

    3. Con la tecla Ctrl presionada, haga clic en las columnas FechaPedido, Detalles_Pedido.IdProducto, Detalles_Pedido.PrecioUnidad y Detalles_Pedido.Cantidad.

  2. Haga clic con el botón secundario en el encabezado de una columna seleccionada y haga clic en Eliminar columnas.

Paso 4: Calcular el total de línea de cada fila de Detalles_Pedido

En este paso, creará una columna personalizada para calcular el total de línea de cada fila de Detalles_Pedido.

Calcular el total de línea de cada fila de Detalles_Pedido

  1. En el panel Vista previa de consulta, haga clic en el icono de tabla ( icono de tabla ) en la esquina superior izquierda de la vista previa.

  2. Haga clic en Insertar columna > Personalizada.

  3. En el cuadro de diálogo Insertar columna personalizada, en el cuadro de texto Fórmula de columna personalizada, escriba [Detalles_Pedido.PrecioUnidad] * [Detalles_Pedido.Cantidad].

  4. En el cuadro de texto Nuevo nombre de columna, escriba Total de línea.

  5. Haga clic en Aceptar.

Calcular el total de línea de cada fila de Detalles_Pedido

Paso 5: Transformar una columna de año FechaPedido

En este paso, transformará la columna FechaPedido para mostrar el año de la fecha del pedido.

  1. En la cuadrícula Vista previa, haga clic con el botón secundario en la columna FechaPedido y haga clic en Transformar > Año.

  2. Realice una de las dos acciones siguientes para cambiar el nombre de la columna FechaPedido por Año:

    1. Haga doble clic en la columna FechaPedido y escriba Año.

    2. Haga clic con el botón secundario en la columna FechaPedido, haga clic en Cambiar nombre y escriba Año.

Paso 6: Agrupar las filas por Id. de producto y año

  1. En la cuadrícula Vista previa de consulta, seleccione Año y Detalles_Pedido.IdProducto.

  2. Haga clic con el botón secundario en uno de los encabezados y haga clic en Agrupar por.

  3. En el cuadro de diálogo Agrupar por:

    1. En el cuadro de texto Nuevo nombre de columna, escriba Ventas totales.

    2. En el menú desplegable Operación, seleccione Suma.

    3. En el menú desplegable Columna, seleccione Total de línea.

  4. Haga clic en Aceptar.

    Cuadro de diálogo Agrupar por en las operaciones de agregado

Paso 7: Importar una consulta de productos

Antes de importar los datos de ventas a Excel, asigne a la consulta el nombre Ventas totales:

  1. En el panel Configuración de consulta, en el cuadro de texto Nombre, escriba Ventas totales.

Resultados de la consulta final

Después de realizar cada paso, tendrá una consulta Ventas totales sobre la fuente de OData de Northwind.

Ventas totales

Pasos de Power Query creados

A medida que realiza actividades de consulta en Power Query, se crean pasos que aparecen en el panel Configuración de consulta, en la lista PASOS APLICADOS. A cada paso de consulta le corresponde una fórmula de Power Query, también conocida como lenguaje "M". Para obtener más información sobre el lenguaje de formulación de Power Query, consulte Obtener información sobre fórmulas de Power Query.

Tarea

Paso de consulta

Fórmula

Conectarse a una fuente de OData

Origen

Origen{[Nombre="Pedidos"]}[Fecha]

Expandir la tabla Detalles_Pedido

Expandir Detalles_Pedido

Table.ExpandTableColumn

(Pedidos, "Detalles_Pedido", {"IdProducto", "PrecioUnidad", "Cantidad"}, {"Detalles_Pedido.IdProducto", "Detalles_Pedido.PrecioUnidad", "Detalles_Pedido.Cantidad"})

Eliminar otras columnas para mostrar únicamente las columnas de interés

RemovedColumns

Table.RemoveColumns

(#"Expand Detalles_Pedido",{"IdPedido", "IdCliente", "IdEmpleado", "FechaRequerida", "FechaEnvío", "EnvíoVía", "Flete", "NombreEnvío", "DirecciónEnvío", "CiudadEnvío", "RegiónEnvío", "CódigoPostalEnvío", "PaísEnvío", "Cliente", "Empleado", "Transportista"})

Calcular el total de línea de cada fila de Detalles_Pedido

InsertedColumns

Table.AddColumn

(RemovedColumns, "Personalizada", each [Detalles_Pedido.PrecioUnidad] * [Detalles_Pedido.Cantidad])

Transformar la columna FechaPedido para mostrar el año

RenamedColumns

Table.RenameColumns

(InsertedCustom,{{"Personalizada", "Total de línea"}})

TransformedColumn

Table.TransformColumns

(RenamedColumns,{{"FechaPedido", Fecha.Año}})

RenamedColumns1

Table.RenameColumns

(TransformedColumn,{{"FechaPedido", "Año"}})

Agrupar las filas por Id. de producto y año

GroupedRows

Table.Group
(RenamedColumns1, {"Año", "Detalles_Pedido.IdProducto"}, {{"Ventas totales", each List.Sum([Total de línea]), type number}})

Paso 8: Deshabilitar descargas de consultas a un libro de Excel

Dado que la consulta Ventas totales no representa el informe final Ventas totales por producto y año, deshabilite la descarga de consultas en el libro de Excel. Cuando la opción Cargar en hoja de cálculo está desactivada en el panel Configuración de consulta, los datos resultantes de esta consulta no se descargan, pero la consulta puede combinarse con otras consultas para obtener el resultado deseado. En la siguiente tarea aprenderá a combinar esta consulta con la consulta Productos.

Deshabilitar la descarga de una consulta

  1. En el panel Configuración de consulta, desactive Cargar en hoja de cálculo.

  2. En la cinta de opciones Editor de consultas, haga clic en Aplicar y cerrar. En el panel Consultas de libro, la consulta Ventas totales muestra Carga deshabilitada.

    Deshabilitar la descarga de una consulta

Principio de página

Tarea 3: Combinar las consultas de productos y ventas totales

Power Query permite combinar varias consultas mediante las operaciones Combinar y Anexar. La operación Combinar se lleva a cabo en cualquier consulta de Power Query con formato tabular, con independencia del origen de los datos. Para más información sobre cómo combinar orígenes de datos, vea Combinar varias consultas.

En esta tarea, combinará las consultas Productos y Ventas totales mediante los pasos Combinar y Expandir.

Paso 1: Combinar el Id. de producto con una consulta de ventas totales

  1. En el libro de Excel, vaya a la consulta Productos en Hoja2.

  2. En la ficha de cinta de CONSULTA, haga clic en Combinar.

  3. En el cuadro de diálogo Combinar, seleccione Productos como tabla principal y Ventas totales como segunda consulta o consulta relacionada para combinar. Ventas totales se convertirá en una nueva columna expandible.

  4. Para que coincida Ventas totales con Productos por IdProducto, seleccione la columna IdProducto en la tabla Productos y la columna Detalles_Pedido.IdProducto en la tabla Ventas totales.

  5. En el cuadro de diálogo Niveles de privacidad:

    1. Seleccione Organizativo como nivel de aislamiento de privacidad de dos orígenes de datos.

    2. Haga clic en Guardar.

  6. Haga clic en Aceptar.

    Nota de seguridad : Los niveles de privacidad impiden que un usuario combine sin darse cuenta datos de varios orígenes, que pueden ser privados o de la organización. En función de la consulta, un usuario podría enviar sin darse cuenta datos desde el origen de datos privado a otro origen de datos que pudiere ser malicioso. Power Query analiza cada origen de datos y lo clasifica en el nivel de privacidad definido: Público, Organizativo y Privado. Para obtener más información sobre los niveles de privacidad, consulte Niveles de privacidad.

    Cuadro de diálogo Combinar

Después de hacer clic en Aceptar, la operación Combinar crea una consulta. El resultado de la consulta contiene todas las columnas de la tabla principal (Productos) y una única columna que contiene un vínculo de navegación a la tabla relacionada (Ventas totales). Una operación Expandir agrega columnas nuevas a la tabla principal o la tabla de asuntos de la tabla relacionada.

Combinación final

Paso 2: Expandir una columna combinada

En este paso, expandirá la columna combinada con el nombre NewColumn para crear dos columnas en la consulta Productos: Año y Ventas totales.

Vínculo Expandir la tabla NuevaColumna

  1. En la cuadrícula Vista previa de consulta, haga clic en el icono de expandir de NewColumn ( Expanda ).

  2. En el menú despegable Expandir:

    1. Haga clic en (Seleccionar todas las columnas) para borrar todas las columnas.

    2. Haga clic en Año y Ventas totales.

    3. Haga clic en Aceptar.

  3. Cambiar el nombre de estos dos columnas por Año y Ventas totales.

  4. Ordene de forma descendente por ventas totales para averiguar qué productos obtuvieron el mayor volumen de ventas y en qué años.

  5. Cambie el nombre de la consulta a Ventas totales por producto.

Vínculo Expandir tabla

Pasos de Power Query creados

A medida que realiza actividades de consulta Combinar en Power Query, se crean pasos que aparecen en el panel Configuración de consulta , en la lista PASOS APLICADOS. A cada paso de consulta le corresponde una fórmula de Power Query, también conocida como lenguaje "M". Para obtener más información sobre el lenguaje de formulación de Power Query, consulte Obtener información sobre fórmulas de Power Query.

Tarea

Paso de consulta

Fórmula

Combinar IdProducto con la consulta Ventas totales

Origen (origen de datos de la operación Combinar)

Table.NestedJoin

(Productos,{"IdProducto"},#"Ventas totales",{"Detalles_Pedido.IdProducto"},"NuevaColumna")

Expandir una columna combinada

ExpandNewColumn

Table.ExpandTableColumn

(Source, "NuevaColumna", {"Año", "Ventas totales"}, {"NuevaColumna.Año", "NuevaColumna.Ventas totales"})

RenamedColumns

Table.RenameColumns

(#"Expand NewColumn",{{"NuevaColumna.Año", "Año"}, {"NuevaColumna.Ventas totales", "Ventas totales"}})

SortedRows

Table.Sort

(RenamedColumns,{{"Ventas totales", Order.Descending}})

Paso 3: Cargar una consulta de ventas totales por producto en un modelo de datos de Excel

En este paso, deshabilitará la opción Cargar en hoja de cálculo y cargará una consulta en el modelo de datos de Excel para generar un informe asociado al resultado de la consulta. Además de cargar los resultados de la consulta en una hoja de cálculo de Excel, Power Query permite cargar el resultado de una consulta en un modelo de datos de Excel. Después de cargar los datos en el modelo de datos de Excel, puede usar Power Pivot y Power View para analizar los datos con mayor profundidad.

Cargar la consulta de ventas totales por producto en el modelo de datos de Excel

  1. En el panel Configuración de consulta, desactive Cargar en hoja de cálculo y active Cargar en modelo de datos.

  2. Para cargar la consulta en el modelo de datos de Excel, haga clic en Aplicar y cerrar.

Cargar modelo de datos de Excel

Consulta final de ventas totales por producto

Después de realizar cada paso, tendrá una consulta Ventas totales por producto que combina datos del archivo Productos y pedidos.xlsx y de la fuente de OData de Northwind. Esta consulta se puede aplicar a un modelo de Power Pivot. Además, los cambios realizados en la consulta en Power Query modifican y actualizan la tabla resultante en el modelo de Power Pivot.

Principio de página

Nota: El Editor de consultas solo aparece al cargar, editar o crear una consulta nueva con Power Query. En el siguiente vídeo se muestra la ventana del Editor de consultas, que aparece tras editar una consulta de un libro de Excel. Para ver el Editor de consultas sin cargar o editar una consulta de libro existente, vaya a la sección Obtener datos externos de la ficha de cinta Power Query y seleccione Desde otros orígenes > Consulta en blanco. En el siguiente vídeo se muestra una manera de mostrar el Editor de consultas.

Cómo ver el Editor de consultas en Excel

Nota: Declinación de responsabilidades de traducción automática: Este artículo se ha traducido con un sistema informático sin intervención humana. Microsoft ofrece estas traducciones automáticas para que los hablantes de otros idiomas distintos del inglés puedan disfrutar del contenido sobre los productos, los servicios y las tecnologías de Microsoft. Puesto que este artículo se ha traducido con traducción automática, es posible que contenga errores de vocabulario, sintaxis o gramática.

Ampliar sus conocimientos
Explorar los cursos
Obtener nuevas características primero
Únase a los participantes de Office Insider

¿Le ha sido útil esta información?

¡Gracias por sus comentarios!

Gracias por sus comentarios. Quizá le interese ponerse en contacto con uno de nuestros agentes de soporte de Office.

×