Aprenda a combinar varios orígenes de datos (Power Query)

En esta tarea, importará productos del archivo Productos y Orders.xlsx (descargado y cuyo nombre se ha cambiado anteriormente) a un libro de Excel, promoverá filas a encabezados de columna, quitará algunas columnas y cargará la consulta en una hoja de cálculo.

Paso 1: Conectar con un libro de Excel

1. Cree un libro de Excel.

2. Seleccione Datos > Obtener > de datosdel > de archivo del libro.

3. En el cuadro de diálogo Importar datos, busque y busque el archivo de Products.xlsx que ha descargado y, después, seleccione Abrir.

4. En el panel Navegador , haga doble clic en la tabla Productos . Aparece el Editor de Power Query de inicio/ apagado.

Paso 2: Examinar los pasos de la consulta

De forma predeterminada, Power Query agrega automáticamente varios pasos para que le resulte más conveniente. Examine cada paso en Pasos aplicados en el panel Configuración de consulta para obtener más información.

1. Haga clic con el botón derecho en el paso Origen y seleccione Editar configuración. Este paso se creó al importar el libro.

2. Haga clic con el botón derecho en el paso Navegación y seleccione Editar configuración. Este paso se creó al seleccionar la tabla en el cuadro de diálogo Navegación .

3. Haga clic con el botón derecho en el paso Tipo cambiado y seleccione Editar configuración. Este paso lo creó Power Query que infería los tipos de datos de cada columna. Seleccione la flecha abajo a la derecha de la barra de fórmulas para ver la fórmula completa.

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 Vista previa de datos, seleccione las columnas IdProducto, NombreProducto, IdCategoría y CantidadUnidad (use Ctrl+Clic o Mayús+Clic).

2. Seleccione Quitar columnas > Quitar otras columnas.

   

Paso 4: Cargar la consulta de productos

En este paso, cargará la consulta Productos en una hoja de cálculo de Excel.

• Seleccione Inicio > Cerrar & Cargar. La consulta aparece en una nueva hoja de cálculo de Excel.

Resumen: pasos Power Query creados en la tarea 1

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 Power Query fórmulas, vea Crear fórmulas de Power Query en Excel.

Tarea	Paso de consulta	Fórmula
Importar un libro de Excel	Origen	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Seleccionar la tabla Productos	Explorar	= Source{[Item="Products",Kind="Table"]}[Data]
Power Query detecta automáticamente los tipos de datos de columna	Tipo cambiado	= Table.TransformColumnTypes(Products_Table,{{"IdProducto", Int64.Type}, {"NombreProducto", type text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}})
Eliminar otras columnas para mostrar únicamente las columnas de interés	Otras columnas quitadas	= Table.SelectColumns(FirstRowAsHeader,{"IdProducto", "NombreProducto", "IdCategoría", "CantidadUnidad"})

En esta tarea, importará datos al libro de Excel desde la fuente de OData de ejemplo de Northwind en http://services.odata.org/Northwind/Northwind.svc,expandirá la tabla Order_Details, quitará columnas, calculará un total de línea, transformará una FechaPedido, agrupará filas por IdProducto y Año, cambiará el nombre de la consulta y deshabilitará la descarga de consultas en el libro de Excel.

Paso 1: Conectarse a una fuente de OData

1. Seleccione Datos > Obtener > de datos de otros orígenes > desde una fuente de OData.

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

3. Seleccione Aceptar.

4. En el panel Navegador , haga doble clic en la tabla Pedidos .

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 ejecuta la consulta, las filas de la tabla relacionada (Order_Details) se combinan en filas con la tabla principal (Pedidos).

En Power Query, una columna que contiene una tabla relacionada tiene el valor Registro o Tabla en la celda. Se denominan columnas estructuradas. Registro indica un único registro relacionado y representa unarelación uno a uno con los datos actuales o la tabla principal. Tabla indica una tabla relacionada y representa una relación uno a varios con la tabla actual o principal. Una columna estructurada representa una relación en un origen de datos que tiene un modelo relacional. Por ejemplo, una columna estructurada indica una entidad con una asociación de clave externa en una fuente de OData o una relación de clave externa en una base de datos de SQL Server.

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 Vista previa de datos, desplácese horizontalmente hasta la columna Order_Details .

2. En la columna Order_Details , seleccione el icono de expandir ().

3. En el menú despegable Expandir:

   1. Seleccione (Seleccionar todas las columnas) para borrar todas las columnas.

   2. Seleccione IdProducto, PrecioUnidad y Cantidad.

   3. Seleccione Aceptar.

      

      Nota: En Power Query, puede expandir las tablas vinculadas desde una columna y agregar 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. 

1. En Vista previade datos, seleccione las columnas siguientes: 

   1. Seleccione la primera columna, IdDePedido.

   2. Mayús+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 derecho en un encabezado de columna seleccionado y seleccione Quitar otras 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.

1. En Vista previa de datos, seleccione el icono de tabla () en la esquina superior izquierda de la vista previa.

2. Haga clic en Agregar columna personalizada.

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

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

5. Seleccione Aceptar.

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 Vista previa de datos, haga clic con el botón derecho en la columna FechaPedido y seleccione 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. Right-Click en la columna FechaPedido , seleccione Cambiar nombre y escriba Año.

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

1. En Vista previa de datos, seleccione Año y Order_Details.IdProducto.

2. Right-Click uno de los encabezados y seleccione 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. Seleccione Aceptar.

   

Paso 7: Importar una consulta de productos

Antes de importar los datos de ventas a Excel, cambie el nombre de la consulta:

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

Resultados: Consulta final de la tarea 2

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

Resumen: pasos Power Query creados en la tarea 2 

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 Power Query fórmulas, vea Obtener más información sobre las fórmulas de Power Query.

Tarea	Paso de consulta	Fórmula
Conectarse a una fuente de OData	Origen	= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])
Seleccionar una tabla	Navegación	= Source{[Name="Orders"]}[Data]
Expandir la tabla Detalles_Pedido	Expandir Detalles_Pedido	= Table.ExpandTableColumn(Orders, "Order_Details", {"IdProducto", "PrecioUnidad", "Cantidad"}, {"Order_Details.IdProducto", "Order_Details.PrecioUnidad", "Order_Details.Cantidad"})
Eliminar otras columnas para mostrar únicamente las columnas de interés	RemovedColumns	= Table.RemoveColumns(#"Expand Order_Details",{"IdDePedido", "IdCliente", "IdDeEnvío", "FechaObligatoria", "ShipVia", "Transporte", "NombreDeEnvío", "DirecciónDeEnvío", "RegiónDeEnvío", "CódigoDeEnvío", "RecuentoDeEnvío", "Cliente", "Empleado", "Transportista"})
Calcular el total de línea de cada fila de Detalles_Pedido	Personalizada agregada	= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])
Cambiar a un nombre más significativo, Lne Total	Columnas con nombre cambiado	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Transformar la columna FechaPedido para mostrar el año	Año extraído	= Table.TransformColumns(#"Filas agrupadas",{{"Año", Fecha.Año, Int64.Type}})
Cambiar a  nombres más significativos, FechaPedido y Año	Columnas 1 cuyo nombre se ha cambiado	Table.RenameColumns (TransformedColumn,{{"FechaPedido", "Año"}})
Agrupar las filas por Id. de producto y año	GroupedRows	= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Ventas totales", cada List.Sum([Total de línea]), tipo number}})

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 con una consulta Combinar y la operación Expandir y, después, cargará la consulta Ventas totales por producto en el modelo de datos de Excel.

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 la pestaña de la hoja de cálculo Productos .

2. Seleccione una celda de la consulta y, después, seleccione Consulta > Combinar.

3. En el cuadro de diálogo Combinar , seleccione Productos como tabla principal y Ventas totales como la consulta secundaria o relacionada que se va a combinar. Ventas totales se convertirá en una nueva columna estructurada con un icono de expandir.

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. Selecciona Guardar.

6. Seleccione 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, vea Establecer niveles de privacidad.

   

Resultado

La operación Combinar crea una consulta. El resultado de la consulta contiene todas las columnas de la tabla principal (Productos) y una sola columna estructurada De tabla a la tabla relacionada (Ventas totales). Seleccione el icono Expandir para agregar nuevas columnas a la tabla principal desde la tabla secundaria o relacionada.

Paso 2: Expandir una columna combinada

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

1. En Vista previa de datos, seleccione Expandir icono () junto a NewColumn.

2. En la lista desplegable Expandir :

   1. Seleccione (Seleccionar todas las columnas) para borrar todas las columnas.

   2. Seleccione Año y Ventas totales.

   3. Seleccione Aceptar.

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

4. Para averiguar qué productos y en qué años obtuvieron el mayor volumen de ventas, seleccione Orden descendente por ventas totales.

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

Resultado

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

En este paso, cargará una consulta en un modelo de datos de Excel para crear un informe conectado al resultado de la consulta. Después de cargar datos en el modelo de datos de Excel, puede usar Power Pivot para profundizar el análisis de datos.

1. Seleccione Inicio > Cerrar & Cargar.

2. En el cuadro de diálogo Importar datos , asegúrese de seleccionar Agregar estos datos al modelo de datos. Para obtener más información sobre el uso de este cuadro de diálogo, seleccione el signo de interrogación (?).

Resultado

Tiene una consulta Ventas totales por producto que combina datos del archivo Products.xlsx y la fuente de OData de Northwind. Esta consulta se aplica a un modelo de Power Pivot. Además, los cambios realizados en la consulta modifican y actualizan la tabla resultante en el modelo de datos.

Resumen: pasos Power Query creados en la tarea 3

A medida que realiza las actividades de consulta Combinar en Power Query, se crean pasos de consulta 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 Power Query fórmulas, vea Obtener más información sobre las 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(Products, {"ProductID"}, #"Ventas totales", {"Order_Details.ProductID"}, "Ventas totales", JoinKind.LeftOuter)
Expandir una columna combinada	Ventas totales expandido	= Table.ExpandTableColumn(Source, "Ventas totales", {"Año", "Ventas totales"}, {"Ventas totales.Año", "Ventas totales.Ventas totales"})
Cambiar el nombre de dos columnas	Columnas con nombre cambiado	= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Ordenar ventas totales en orden ascendente	Sorted Rows	= Table.Sort(#"Columnas con nombre cambiado";{{"Ventas totales"; Order.Ascending}})