Unión cruzada con Power Query

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

Contenidos

Esta semana tuve una situación en la que necesito crear una tabla de presupuesto en Power Pivot. Tuve la opción de cargar un presupuesto semanal como un solo registro por tienda y usando DAX para calcular los presupuestos del año hasta la fecha, o cargar un conjunto de registros (uno para cada semana) en una tabla de presupuesto. Elegí este último en este caso como me dio la oportunidad de probar nuevas habilidades en Power Query. He recreado el escenario a continuación.

Presupuestos semanales por tienda

Tengo una hoja de cálculo que contiene el presupuesto por tienda por semana. El presupuesto es el mismo para cada semana para cada tienda durante las próximas 15 semanas. La tabla de presupuestos se ve así: es fácil ingresar los datos pero no es óptimo para Power Pivot. En mi ejemplo de la vida real, había muchas más columnas para Contracción, GP y algunas otras métricas en la página.

image_thumb26-8443936

Las tablas largas y estrechas suelen ser mejores para Power Pivot, por lo que utilicé Power Query para remodelar esta tabla para que se parezca a la siguiente, usando una transformación simple de columnas sin pivote de Power Query.

image_thumb27-1275834

Una vez que se creó esta tabla en Power Query (importar desde la hoja de cálculo y luego transformar), llamé a la consulta «Presupuesto» y la configuré como «solo crear conexión».

image_thumb28-5172844

Esto lo hace disponible para su uso en otras consultas. sin almacenar los datos en ningún lugar de Excel. Cada vez que ‘materializa’ una consulta agregándola como una tabla en Excel o Power Pivot, ocupa espacio. Solo haga esto si necesita la vista materializada de la tabla.

Números de identificación de la semana

Necesitaba establecer el presupuesto cada semana para las próximas 15 semanas. Estaba usando un calendario semanal en este ejemplo (no un calendario diario), por lo que tenía ID de semana como se muestra a continuación en el formato YYWW. Tenga en cuenta el salto no contiguo al final del año calendario en 2016.

image_thumb29-3910213

Creación de la lista de ID de semana en Power Query

Esta fue mi primera oportunidad de aprender una nueva habilidad.. Sabía que era posible crear una lista nativa en Power Query en lugar de importar la lista desde Excel (leí esto en alguna parte, probablemente algo de Chris Webb) pero no estaba muy seguro de cómo. Busqué en Google y probé List.Generate () pero estaba mal. Luego, un flashback: todo lo que tenía que hacer era usar la siguiente sintaxis con llaves.

= {1..5}

Lo anterior genera una lista de números del 1 al 5. Con esto en mente, hice lo siguiente:

  • Creé una nueva consulta ejecutando los siguientes pasos del menú:
    • Consulta de energía
    • De otras fuentes
    • Consulta en blanco
  • Se cambió el nombre de la consulta a «Semanas».
  • Escribí una línea de código como esta en la barra de fórmulas = {1545..1552,1601..1607} y me dio exactamente lo que necesitaba. No sabía que la sintaxis de la coma funcionaría para crear rangos no contiguos cuando escribí la fórmula, pero funcionó. Después de que adquieras algo de práctica y experiencia con un nuevo lenguaje como este, comienzas a ver los patrones en la sintaxis y obtienes tiros libres como este. (Nota: si no puede ver la barra de fórmulas, actívela desde el menú Ver)

image_thumb30-6305124

  • Tenga en cuenta que en este punto es «solo» una lista, no una tabla. Para convertir la lista en una tabla, hice clic en Transformar en tabla, luego le di a la nueva tabla un nombre de columna.

image_thumb31-2661402

  • También guardé la consulta como «solo crear conexión» para que no materializara la tabla, solo creé las instrucciones sobre cómo crear la tabla en una nueva consulta.

En este punto estaba muy feliz e impresionado con mis nuevas habilidades, pero todavía había problemas por delante. El siguiente paso fue la unión cruzada de las mesas.

Unir en cruz las dos tablas

La tabla de semanas anterior tiene 15 filas y la tabla de presupuesto tiene 10 filas. Cuando digo «combinación cruzada», lo que quiero decir es que necesito duplicar la tabla de presupuesto completa para cada semana, agregando la columna de la semana en la tabla de presupuesto. Entonces necesito algo como se muestra a continuación, pero para cada combinación posible. es decir, 15 x 10 = 150 filas en mi nueva tabla.

crossjoin_thumb-2983799

Busqué rápidamente en Google Cross Join Power Query y encontré algunas referencias a Full Outer Joins, pero esto no es lo que necesitaba. Entonces tuve una idea: tal vez podría escribir una función ficticia para simular el proceso Cross Join. Probé esto y funcionó bien.

Editar 31/10/15

Después de publicar, recibí algunas buenas sugerencias de Imke Feldman; parece que la función no es necesaria en absoluto; en realidad tiene sentido, pero no me había dado cuenta. Chris Webb también publicó un enfoque más eficaz (lo cual es importante si tiene tablas grandes), así que aquí está el enfoque actualizado.

Agregue una columna personalizada para crear una unión cruzada

  1. Abra la tabla Semanas y agregue una nueva columna personalizada (que se muestra a continuación). La fórmula para la nueva columna es simplemente el nombre de la Consulta de presupuesto.
    newaddcolumn_thumb-2056261
  2. Después de hacer clic en Aceptar, obtiene una nueva columna que «contiene» la tabla de Presupuesto completa como un objeto en cada fila de la tabla de Semanas (como se muestra a continuación).
    image_thumb33-3577700
  3. Todo lo que tenía que hacer entonces era expandir la nueva columna haciendo clic en el botón expandir (que se muestra arriba) para crear todas las combinaciones posibles (que se muestran a continuación)

image_thumb34-1040534

Configuré los tipos de datos de las columnas numéricas y cargué la tabla directamente en mi modelo de datos para poder usarla en Power Pivot.

También puede leer la sugerencia de Chris Webb en los comentarios a continuación)

Suscribite a nuestro Newsletter

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