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

En esta tarea, importar productos del archivo Productos y Orders.xlsx (descargados y cambiados de nombre anteriormente) en 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 datos > del archivo > del libro.

3. En el cuadro de diálogo Importar datos, busque y busque el archivo Products.xlsx que descargó y, a continuación, seleccione Abrir.

4. En el panel Navegador, haga doble clic en la tabla Productos. Aparecerá el Editor de Power Query.

Paso 2: Examinar los pasos de la consulta

De forma predeterminada, Power Query agrega automáticamente varios pasos como una comodidad para usted. Examine cada paso en Pasos aplicados en el panel Configuración 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 inferyó 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 dedatos, seleccione las columnas IdDeProbado, NombreDeProbado, IdDeProdidady CantidadPerUnidad (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 Excel hoja de cálculo.

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

Resumen: Pasos de Power Query creados en la tarea 1

A medida que realiza actividades de consulta en Power Query, los pasos de la consulta se crean y se muestran en el panel Consulta Configuración, 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 las fórmulas de Power Query, vea Crear fórmulasde Power Query en Excel .

Tarea	Paso de consulta	Fórmula
Importar un Excel de datos	Origen	= Excel. Libro(File.Contents("C:\Products and Orders.xlsx"), null, true)
Seleccionar la tabla Productos	Explorar	= Origen{[Elemento="Productos",Tipo="Tabla"]}[Datos]
Power Query detecta automáticamente los tipos de datos de columna	Tipo cambiado	= Table.TransformColumnTypes(Products_Table,{{"ProductID", Int64.Type}, {"ProductName", escriba 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 eliminadas	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

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

Paso 1: Conectar a una fuente de OData

1. Seleccione Datos > Obtener datos > de otros orígenes > de la 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 una relació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 SQL Server datos.

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 dedatos, desplácese horizontalmente hasta la Order_Details datos.

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

3. En el menú despegable Expandir:

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

   2. Seleccione Id. deproducto, PrecioUnidady Cantidad.

   3. Seleccione Aceptar.

      

      Nota: En Power Query, puede expandir tablas vinculadas desde una columna y agregar las columnas de la tabla vinculada antes de expandir los datos de la tabla de asunto. 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 previa dedatos, seleccione las siguientes columnas:

   1. Seleccione la primera columna, Id. de pedido.

   2. Mayús+Haga clic en la última columna, Remitente.

   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 dedatos, 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 dedatos, 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 nombrey escriba Año.

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

1. En Vista previa dedatos, seleccione Añoy Order_Details.ProductID.

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 consulta, en el cuadro Nombre escriba Ventas totales.

Resultados: Consulta final para la tarea 2

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

Resumen: Pasos de Power Query creados en la tarea 2 

A medida que realiza actividades de consulta en Power Query, los pasos de la consulta se crean y se muestran en el panel Consulta Configuración, 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 las fórmulas de Power Query, vea 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	= Origen{[Nombre="Pedidos"]}[Datos]
Expandir la tabla Detalles_Pedido	Expandir Detalles_Pedido	= Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})
Eliminar otras columnas para mostrar únicamente las columnas de interés	RemovedColumns	= Table.RemoveColumns(#"Expandir Order_Details",{"IdDePedido", "IdDePedido", "IdDeUsuario", "FechaDePedido", "FechaDeEnvío", "ShipVia", "Flete", "NombreDeEnvío", "ShipCity", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})
Calcular el total de línea de cada fila de Detalles_Pedido	Agregado personalizado	= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details", "Total de línea", cada una [Order_Details.PrecioUnidad] * [Order_Details.Quantity])
Cambiar a un nombre más significativo, Total de Lne	Columnas con nombre cambiado	= Table.RenameColumns(InsertedCustom,{{"Custom", "Total de línea"}})
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 con nombre cambiado 1	Table.RenameColumns (TransformedColumn,{{"FechaPedido", "Año"}})
Agrupar las filas por Id. de producto y año	GroupedRows	= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), escriba 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 mediante una consulta de combinación y una operación Expandir y, después, cargará la consulta Ventas totales por producto en el Excel de datos.

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

1. En el Excel, vaya a la consulta Productos de la pestaña Hoja de cálculo Productos.

2. Seleccione una celda de la consulta y, a continuación, seleccione Consulta > combinar.

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

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. Seleccione 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 tabla a la tabla relacionada(Ventas totales). Seleccione el icono Expandir para agregar nuevas columnas a la tabla principal de 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 dedatos, 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 los productos han conseguido el mayor volumen de ventas, seleccione Ordenar 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 Excelde datos para crear un informe conectado al resultado de la consulta. Después de cargar datos en el Excel dedatos, puede usar Power Pivot para mejorar 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 en la consulta modifican y actualizan la tabla resultante en el modelo de datos.

Resumen: Pasos de Power Query creados en la tarea 3

A medida que realiza las actividades de consulta Combinar en Power Query, los pasos de consulta se crean y se muestran en el panel Consulta Configuración, 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 las fórmulas de Power Query, vea 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"}, #"Total Sales", {"Order_Details.ProductID"}, "Ventas totales", JoinKind.LeftOuter)
Expandir una columna combinada	Ventas totales expandida	= Table.ExpandTableColumn(Origen, "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(#"Ventas totales expandida",{{{"Ventas totales.Año", "Año"}, {"Ventas totales.Ventas totales", "Ventas totales"}})
Ordenar ventas totales en orden ascendente	Filas ordenadas	= Table.Sort(#"Columnas con nombre cambiado",{{{"Ventas totales", Order.Ascending}})