Tablas de consulta DAX en Excel 2010

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Estaba ayudando a un participante en http://powerpivotforum.com.au con un problema. En pocas palabras, ella necesitaba devolver un mesa a Excel desde un modelo de datos, no un Tabla dinámica. Este concepto puede resultar un poco extraño al principio, déjame explicarte.

Tablas, no tablas dinámicas

Una tabla dinámica es una herramienta que agrega datos y le permite cortar y cortar para obtener diferentes totales y subtotales de esos datos.. Esto es genial cuando sus tablas están llenas de números y desea sumar esos números. Pero considere el siguiente escenario. ¿Qué sucede si desea devolver una tabla que enumera todos los nombres y direcciones de los clientes que han comprado más de $ 2,000 en productos de su empresa? Bueno, podrías configurar una tabla dinámica como esta.

pivottableoption_thumb-5847994

En la tabla dinámica anterior, hay 5 columnas diferentes de la tabla de clientes y 1 medida (en realidad, hay muchas más columnas de texto que quiero extraer además de las que se muestran aquí). Las columnas de datos de la tabla de clientes se han colocado en el sección de filas de la tabla dinámica. Cuantas más columnas coloque en filas en su tabla dinámica, más lento será el rendimiento. Qué es peor, si las columnas provienen de diferentes tablas, puede obtener una desaceleración exponencial en el rendimiento. Lea sobre por qué eso está aquí.

Bien, entonces la respuesta a este problema es no use una tabla dinámica, use una tabla de Excel en su lugar. Hay pocos artículos en la web que explican cómo hacer esto en Excel 2013, incluyendo éste de Scott en TinyLizard.com. Puedo escribir mi propia publicación sobre eso en otro momento, pero hoy es simplemente para explicar cómo hacer esto en Excel 2010 ya que el proceso no es el mismo.

Cuando quiero aprender algo sobre las consultas DAX, siempre me dirijo a http://sqlbi.com. Una buena forma de buscar en un sitio específico es utilizar el sitio de Google: palabra clave en su búsqueda como se muestra a continuación.

google_thumb-2698105

El segundo elemento de la lista parecía prometedor, así que leí rápidamente. Se refiere a insertar tablas desde un modelo tabular, pero pensé que podría modificar la técnica para Excel 2010. Lo hice y funcionó. Aquí está el proceso completo.

Tengo una configuración de workbook de muestra usando Adventure Works. Aquí está el modelo de datos (que se muestra a continuación) y puede descargar el workbook aquí.

datamodel_thumb-9266949

  1. Haga clic en una celda en blanco en su workbook
  2. Selecciona el Datos Menú
  3. Hacer clic Conexiones existentes
  4. Seleccione Datos de PowerPivot
  5. Hacer clic Abierto
    dataconnections_thumb-8415664
  6. Seleccione Solo crear conexión
  7. Hacer clic Propiedades.
    importdata_thumb-5946769
  8. Haga clic en el Definición Pestaña
  9. Seleccione Exportar archivo de conexión
    connection_thumb-2177107
  10. Dale un nombre a tu conexión
  11. Hacer clic Salvar
    save_thumb-6627718
  12. Abra el Explorador de Windows y busque la carpeta Fuentes de datos
  13. Haga clic derecho en su nueva conexión
  14. Seleccione Editar en Bloc de notas
    datasources_thumb-2382156
  15. Busque la sección del XML que se muestra en la imagen a continuación
  16. Reemplazar la palabra Cubo con Consulta
  17. Reemplazar la palabra Modelo con cualquier instrucción de consulta DAX válida. Lo más fácil es simplemente EVALUAR AnyTableName. he utilizado EVALUAR CLIENTES
    Entonces comencé con esto
    edit_thumb-7862531
    Y terminé con esto
    edit2_thumb-5652352
  18. Guarde y cierre el Bloc de notas.
  19. Finalmente, regrese a Excel y seleccione una celda en blanco
  20. Navega al Datos menú
  21. Seleccione Conexiones existentes
  22. Seleccione su nueva conexión (del paso 10)
  23. Hacer clic Abierto
    final_thumb-3095036
  24. Verá el mismo diálogo que en el paso 4, pero esta vez tiene una opción para insertar una tabla. Seleccionar tabla de la lista
  25. Haga clic en Aceptar.
    insert_thumb-4244114

Y ahí lo tiene: una nueva tabla en su workbook que se obtiene y se vincula a su modelo de datos.

table_thumb-3865184

Devolver este detalle de texto en una tabla dinámica no sería una experiencia agradable.

Ahora, esta tabla es la tabla Clientes completa, y lo que realmente quería era una lista de clientes que hayan comprado> $ 2,000. Para solucionarlo, deberá escribir algún código de consulta DAX.

  1. Haga clic derecho en cualquier lugar de la tabla
  2. Seleccionar tabla
  3. Seleccione Editar consulta

editquery2_thumb-3965318

Tiene la oportunidad de reemplazar la consulta que agregó en el paso 17 con cualquier consulta DAX válida. No voy a entrar en DAX como lenguaje de consulta aquí, ya que ese es un tema para otro día. Para finalizar este proceso, agregué la siguiente consulta DAX

EVALUATE
FILTER (
    ADDCOLUMNS ( customers, "Cust Sales", [Total Sales] ),
    [Cust Sales] >= 2000
)

Y mi tabla se actualiza con la lista completa de clientes que tienen ventas> $ 2,000

finallist_thumb-6468856

Para obtener más información sobre cómo escribir consultas DAX, le recomiendo que eche un vistazo a este artículo en SQLBI.com. No es específicamente una introducción a las consultas DAX, pero lo encontré muy útil para aprender qué se puede hacer.

Una palabra final

No estoy seguro de qué pasará cuando otros abran este libro. No tengo claro si la nueva conexión que creé se incrusta automáticamente en Excel o si todavía está instalada en mi PC. Me interesaría saber de cualquier usuario de Excel 2010 que abra el workbook de muestra para saber si pueden editar la conexión.

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window,document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘639916389503636’);
fbq(‘track’, ‘PageView’);

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.