Este post pretende mostrar los dos complementos gratuitos que ofrece Excel 2013, PowerQuery y PowerPivot como herramientas de creación de informes con tablas relacionadas, no es un tutorial, sólo pretende despertaros la curiosidad … 🙂
Para ello utilizaremos como ejemplo la creación de un informe donde debemos mostrar la Ganancia Bruta en la venta de diferentes productos durante un periodo de tiempo.
Contamos con dos archivos Excel, Ventas que contiene una tabla Ventas por producto y Costes con una tabla Costes por unidad.
Esas dos tablas son las que usaremos para crear nuestro informe.
PowerQuery permite crear un nuevo Excel que contenga las dos tablas y que a su vez estén relacionadas para conocer el coste de cada producto. Además permite crear campos calcula
dos, en este caso necesitaremos el campo GANACIA BRUTA.
Desde la ficha PowerQuery >Obtener datos externos > De archivo traeremos las tablas.
Al hacer clic en el comando Combinar se abrirá una ventana dónde se añaden las tablas, marcando los campos PRODUCTO de ambas se creará la relación. Después añadiremos la columna COSTE y el campo calculado COSTE TOTAL y GANANCIA BRUTA.
Una vez combinadas el resultado será una tabla como la de la imagen de arriba, en la que podemos ver el coste unitario de cada producto y el coste de las unidades vendidas y la ganancia bruta de cada producto según su fecha.
Ahora sólo nos queda crear el informe con los campos creados con PowerQuery, para ello utilizaremos una tabla dinámica.
Para realizar el mismo informe creado con PowerPivot, desde la ficha PowerPivot abre el Administrador para importar las tablas de Ventas y Costes y crear las relaciones entre éstas.
Una vez creadas las relaciones, con PowerPivot creamos los campos calculados, COSTE TOTAL y GANANCIA BRUTA. Para ello utilizaremos las siguientes Funciones DAX :
COSTE TOTAL: =RELATED(‘Coste por unidad'[COSTE])*’Ventas por Producto'[CANTIDAD]
GANANCIA BRUTA: =’Ventas por Producto'[IMPORTE]-‘Ventas por Producto'[COSTE TOTAL]
Ahora ya tenemos los campos necesarios para desde PowerPivot crear nuestro informe con una tabla dinámica.
Enlaces interesantes para este post:
POWERQUERY Combinar datos de fuentes distintas: http://jldexcelsp.blogspot.com.es/2014/02/usos-del-power-query-combinar-datos-de.html
Cómo PowerPivot y PowerQuery trabajan juntos: http://office.microsoft.com/en-001/excel-help/how-power-query-and-power-pivot-work-together-HA104125038.aspx
DESCARGA PowerQuery: http://www.microsoft.com/es-es/download/details.aspx?id=39379
DESCARGA PowerPivot: https://exceltrucos.com/2013/10/16/powerpivot-para-excel-2010-descarga/
Excel Trucos & Power BI Tips 
Como puedo incluir en un campo de la tabla importada a power query en nombre de la consulta, Qué formula utilizar?? En excel es por ejemplo =DERECHA(CELDA(«nombrearchivo»);6)
Me podria ayudar alguien por favor
Hola, Power Query tiene múltiples funciones que puedes usar de forma sencilla y rápida. Para la necesidad que tienes, en la ventana de Power Query una vez importada la tabla, ve a la Ficha > Agregar columna, ahí tienes una opción que es Extraer > Últimos caracteres selecciona la columna que quieras y escribe el número de caracteres. Creará una nueva columna, le puedes cambiar el nombre que da por defecto.
La función en Power Query tiene una sintaxis especial :
= Table.AddColumn(#»Columnas quitadas», «Nueva Columna 6 caracteres», each Text.End([PROVINCIA], 6), type text)
Recuerda tener la versión de Office actualizada ya que han mejorado mucho y hay novedades.