En este tutorial, use el Editor de Power Query de Power Query para importar datos de un archivo de Excel local que contenga información del producto y de una fuente OData que contenga información del pedido del producto. Realice pasos de transformación y agregación, y combine datos de ambos orígenes para crear un informe de ventas totales por producto y año .
Para completar este tutorial, necesita el libro Productos . En el cuadro de diálogo Guardar como, póngale al archivo el nombre Productos y Pedidos.xlsx.
Tarea 1: Importar productos a un libro de Excel
En esta tarea, se importan los productos del archivo Products and Orders.xlsx (descargado y renombrado en la sección anterior) a un libro de Excel. A continuación, promueve las filas a los encabezados de columna, quita algunas columnas y carga la consulta en una hoja de cálculo.
Paso 1: Conectarse a un libro de Excel
- Cree un libro de Excel.
- Seleccione Datos>Obtener datos>de un archivo>del libro.
- En el cuadro de diálogo Importar datos , busque y busque el archivo Products.xlsx que descargó y, después, haga clic en Abrir.
- En el panel Navegador , haga doble clic en la tabla Productos . Aparece el Editor de Power Query.
Paso 2: Examinar los pasos de la consulta
De forma predeterminada, Power Query agrega automáticamente varios pasos para su comodidad. Para obtener más información, examine cada paso en Pasos aplicados en el panel Configuración de la consulta .
- Haga clic con el botón derecho en el paso Origen y seleccione Editar configuración. Este paso se creó al importar el libro.
- Haga clic con el botón derecho en el paso de navegación y seleccione Editar configuración. Este paso se creó al seleccionar la tabla en el cuadro de diálogo Navegación .
- Haga clic con el botón derecho en el paso Tipo cambiado y seleccione Editar configuración. Este paso lo creó Power Query, que dedujo los tipos de datos de cada columna. Seleccione la flecha abajo situada a la derecha de la barra de fórmulas para ver la fórmula completa.
Paso 3: Quitar otras columnas para mostrar solo las columnas de interés
En este paso, quitará todas las columnas excepto ProductID,ProductName, CategoryID y QuantityPerUnit.
- En Vista previa de datos, seleccione las columnas ProductID,ProductName, CategoryID y QuantityPerUnit (use Ctrl+Clic o Mayús+Clic).
- 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 .
- Selecciona Inicio>Cerrar & Cargar. La consulta aparece en una nueva hoja de cálculo de Excel.
Resumen: pasos de Power Query creados en la tarea 1
A medida que realiza actividades de consulta en Power Query, crea pasos de consulta y los enumera 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 las fórmulas de Power Query, consulte la documentación de Power Query.
| Tarea | Paso de consulta | Fórmula |
|---|---|---|
| Importar un libro de Excel | Origen | = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true) |
| Seleccione 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,{{"ProductID", Int64.Type}, {"ProductName", 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,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"}) |
Tarea 2: Importar datos de pedidos desde una fuente de OData
En esta tarea, se importan datos al libro de Excel desde la fuente de OData Northwind de muestra en http://services.odata.org/Northwind/Northwind.svc, se expande la tabla Order_Details, se quitan columnas, se calcula un total de líneas, se transforma un OrderDate, se agrupan filas por ProductID y Year, se cambia el nombre de la consulta y se deshabilita la descarga de consultas en el libro de Excel.
Paso 1: conectarse a una fuente de OData
- Seleccione Obtener>datos>de otros orígenes>de la fuente OData.
- En el cuadro de diálogo Fuente de OData, escriba la dirección URL de la fuente de OData
- Seleccione Aceptar.
- En el panel Navegador , haga doble clic en la tabla Pedidos .
Paso 2: Expandir una tabla Order_Details
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. El registro indica un único registro relacionado y representa una relación uno a uno con los datos actuales o la tabla principal. La tabla indica una tabla relacionada y representa una relación de 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 Order_Details , se agregan tres nuevas columnas y filas adicionales a la tabla Pedidos , una para cada fila de la tabla anidada o relacionada.
En Vista previa de datos, desplácese horizontalmente a la columna Order_Details .
En la columna Order_Details , selecciona el icono de expandir (
).En el menú despegable Expandir:
Seleccione (Seleccionar todas las columnas) para borrar todas las columnas.
Seleccione ProductID,UnitPrice y Quantity.
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 acerca de cómo realizar operaciones de agregado, vea Agregar datos desde una columna (Power Query).
Paso 3: Quitar otras columnas para mostrar solo las columnas de interés
En este paso, quita todas las columnas excepto las columnas FechaPedido, Id. de producto, Precio unitario y Cantidad .
En Vista previa de datos, seleccione las columnas siguientes:
- Seleccione la primera columna, IdDePedido.
- Mayús+Haga clic en la última columna, Cargador.
- Con la tecla Ctrl presionada, haga clic en las columnas FechaPedido, Detalles_Pedido.IdProducto, Detalles_Pedido.PrecioUnidad y Detalles_Pedido.Cantidad.
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íneas de cada fila de Order_Details
En este paso, creará una columna personalizada para calcular el total de línea de cada fila de Detalles_Pedido.
- En Vista previa de datos, seleccione el icono de tabla (
) en la esquina superior izquierda de la vista previa. - Seleccione Agregar columna personalizada.
- En el cuadro de diálogo Columna personalizada , en el cuadro de fórmula Columna personalizada , escriba [Order_Details.PrecioUnidad] * [Order_Details.Cantidad].
- En el cuadro Nuevo nombre de columna , escriba Line Total.
- Seleccione Aceptar.
Paso 5: Transformar una columna FechaPedido y año
En este paso, transformará la columna FechaPedido para mostrar el año de la fecha del pedido.
En Vista previa de datos, haga clic con el botón derecho en la columna FechaPedido y seleccione Transformar>año.
Realice una de las dos acciones siguientes para cambiar el nombre de la columna FechaPedido por Año:
- Haga doble clic en la columna FechaPedido y escriba Year o
- Haga clic con el botón derecho en la columna FechaDePedido , seleccione Cambiar nombre y escriba Año.
Paso 6: Agrupar filas por ProductID y Year
En la versión preliminar de datos, selecciona Year y Order_Details.ProductID.
Haga clic con el botón derecho en uno de los encabezados y seleccione Agrupar por.
En el cuadro de diálogo Agrupar por:
- En el cuadro de texto Nuevo nombre de columna, escriba Ventas totales.
- En el menú desplegable Operación, seleccione Suma.
- En el menú desplegable Columna, seleccione Total de línea.
Seleccione Aceptar.
Paso 7: Cambiar el nombre de una consulta
Antes de importar los datos de ventas en Excel, cambie el nombre de la consulta:
- En el panel Configuración de la consulta , en el cuadro Nombre , escriba Ventas totales.
Resultados: Consulta final para la tarea 2
Después de realizar cada paso, tendrá una consulta de ventas totales sobre la fuente OData de Northwind.
Resumen: pasos de Power Query creados en la tarea 2
A medida que realiza actividades de consulta en Power Query, crea pasos de consulta y los enumera 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 las fórmulas de Power Query, consulte la documentación 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", {"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(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"}) |
| 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(#"Order_Details expandido", "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",{{"Year", Date.Year, Int64.Type}}) |
| Cambiar a nombres más significativos, FechaPedido y Año |
Renamed Columns 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]), type number}}) |
Tarea 3: Combinar las consultas de productos y ventas totales
Power Query permite combinar varias consultas fusionándolas o anexándolas. Puede realizar la operación Combinar en cualquier consulta de Power Query con una forma tabular, independientemente del origen de datos. Para obtener más información sobre la combinación de orígenes de datos, consulte Combinar varias consultas (Power Query).
En esta tarea, se combinan las consultas Productos y Ventas totales mediante una consulta Combinar y expandir y, después, se carga la consulta Ventas totales por producto en el modelo de datos de Excel.
Paso 1: Combinar ProductID en una consulta de ventas totales
En el libro de Excel, vaya a la consulta Productos de la pestaña Hoja de cálculo Productos .
Seleccione una celda de la consulta y seleccione Combinar consultas>.
En el cuadro de diálogo Combinar , seleccione Productos como tabla principal y seleccione Ventas totales como consulta secundaria o relacionada para combinar. Ventas totales se convierte en una nueva columna estructurada con un icono de expansión.
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.
En el cuadro de diálogo Niveles de privacidad:
- Seleccione Organizativo como nivel de aislamiento de privacidad de dos orígenes de datos.
- Selecciona Guardar.
Seleccione Aceptar.
Nota
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 acerca de los niveles de privacidad, consulte Establecer niveles de privacidad (Power Query).
Resultado
La operación de combinación crea una consulta. El resultado de la consulta contiene todas las columnas de la tabla principal (Productos) y una sola columna estructurada de tabla para 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, expande la columna combinada con el nombre NewColumn para crear dos nuevas columnas en la consulta Productos : Year y Total Sales.
En Vista previa de datos, seleccione el icono Expandir (
) junto a NewColumn.En la lista desplegable Expandir :
- Seleccione (Seleccionar todas las columnas) para borrar todas las columnas.
- Seleccione Year and Total Sales.
- Seleccione Aceptar.
Cambiar el nombre de estos dos columnas por Año y Ventas totales.
Para averiguar qué productos y en qué años los productos obtuvieron el mayor volumen de ventas, selecciona Orden descendente por Ventas totales.
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 poder crear un informe conectado al resultado de la consulta. Después de cargar los datos en el modelo de datos de Excel, puede usar Power Pivot para ampliar el análisis de datos.
- Selecciona Inicio>Cerrar & Cargar.
- 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 acerca de cómo usar este cuadro de diálogo, seleccione el signo de interrogación (?).
Resultado
Tiene una consulta de ventas totales por producto que combina datos del archivo de 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 actividades de combinar consultas en Power Query, los pasos de consulta se crean y se enumeran en el panel Configuración de la 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 las fórmulas de Power Query, consulte la documentación 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"}, "Total Sales", JoinKind.LeftOuter) |
| Expandir una columna combinada | Expanded Total Sales | = Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"}) |
| 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 las ventas totales en orden ascendente | Sorted Rows | = Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}}) |