Convertir un valor de celda en una columna con Power Query

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

Contenidos

Nivel: Consulta de energía avanzada

Estoy en el proceso de finalizar algunos elementos de acción. después de un excelente curso de capacitación de Power BI, Power Pivot y Power Query en Melbourne. Durante la capacitación, analizamos un escenario para uno de los asistentes en el que necesitaba combinar datos de varios archivos de Excel en una tabla grande. He escrito sobre esto en un blog antes en 3 artículos que comienzan aquí.

En este escenario específico que analizamos en la capacitación, hay un extracto semanal de datos de otro sistema de BI (¿te suena familiar?). Cada semana, alguien debe usar «Exportar a Excel» para tomar los datos de la semana actual y volcarlos en un archivo (una acción típica de un sistema de BI que no es fácil de usar). El formato de archivo de la exportación se parece a esto (indicativo).

image_thumb-19-3167313

Los puntos clave sobre esta estructura de datos son.

  1. Hay una fecha en una celda en el encabezado del archivo.
  2. Los detalles también están en el mismo archivo, pero no hay una columna de fecha en la sección de detalles.

El requisito es terminar con una mesa como esta.

image_thumb-20-3436670

El resto de este artículo describe cómo hacerlo.

Paso 1. Conéctese al archivo usando Power Query

image_thumb-21-5370487

Paso 2. Filtre la tabla para que la fila de fecha esté en la parte superior

Para hacer esto, simplemente hice un «Eliminar filas superiores» para eliminar las 2 filas superiores.

image_thumb-22-8689478

Paso 3. Truco asesino extraer la fecha real

El siguiente paso es un truco asesino. La idea es extraer la fecha de la primera columna de la fila superior en un valor escalar para poder usarla más tarde. Para ejecutar este paso, debe utilizar una fórmula de lenguaje PQL escrita a mano.

Haga clic en el botón Fx para insertar un nuevo paso personalizado

image_thumb-23-9261309

Si no puede ver el botón Fx, significa que no tiene la barra de fórmulas activada. Enciéndelo así

image_thumb-24-4303876

Después de hacer clic en Fx, se agregará un nuevo paso personalizado al panel de consulta (1 a continuación). El paso (de forma predeterminada) asume que desea utilizar el paso anterior como punto de partida (2 a continuación).

image_thumb-25-5687955

Para extraer la fecha como un valor escalar (2 a continuación), debe usar la función Campo de registro como se muestra en 1 a continuación.

image_thumb-26-2139180

Observe cómo he envuelto el paso original # ”Eliminado filas superiores” dentro de la función Record.Field. La parte {0} de la fórmula dice «tomar la primera fila del paso anterior, y la parte» Columna1 «dice tomar los datos en la columna llamada» Columna1 «. La intersección entre la primera fila y esta columna es una sola celda y, por lo tanto, esta fórmula accede al valor real (valor escalar) en esta única celda en el workbook. Puede ver que es un valor escalar (2 arriba) porque no muestra una tabla, sino un valor único.

Estrictamente hablando, no necesitaba forzar la fecha en la primera fila, solo hice esto porque estaba más limpio. Podría hacer referencia a la fila / columna con la misma facilidad sin eliminar las filas superiores con esta fórmula

= Campo de registro (# ”Filas superiores eliminadas” {2}, ”Columna1 ″)

El número dentro de las llaves representa el número de fila, con un índice de base 0. Entonces 0 es la primera fila, 1 es la segunda fila, etc.

Paso 3. Segundo truco asesino: obtenga los datos de un paso anterior

El siguiente truco (poco conocido) es que De hecho, puedo volver atrás y obtener los datos de un paso anterior. De forma predeterminada, Power Query siempre usa el paso anterior como punto de partida para el siguiente: un flujo lógico. Pero no tiene por qué ser así. En este caso, el paso anterior es simplemente el valor escalar = fecha, y eso no es lo que quiero. Necesito el paso anterior al que es «Filas superiores eliminadas».

Haga clic en el botón Fx para crear otro paso personalizado nuevo.

image_thumb-27-4162422

Ahora cambie la fórmula en la barra de fórmulas (1 arriba) para que apunte al paso “Filas superiores eliminadas” en lugar de apuntar al paso “Personalizado1”. Me gusta esto

image_thumb-28-8316874

Así que ahora estoy de vuelta donde estaba antes de extraer el valor de la fecha escalar.

Paso 4. Proceda a dar forma a los datos de la forma que desee

Luego ejecuté algunos pasos para limpiar y dar forma a la tabla de la manera que quiero los datos. Terminé con lo siguiente.

image_thumb-29-5162263

Paso 5. Agregue la fecha como una columna

El último paso es llevar la fecha del paso 2 a una columna de la tabla. Ahora es bastante fácil.

Agregue una columna personalizada y consulte el paso «Personalizado1» que devolvió el valor escalar.

image_thumb-30-7066569

Y termino con esto

image_thumb-31-4643815

Exactamente el tipo de estructura de datos necesaria para cargar en Power Pivot. Simplemente reordené las columnas para poner la columna de fecha primero, y luego se hizo.

Comentarios finales

Si lo desea (o necesita), puede convertir la consulta anterior en una función. Lea mas sobre eso, aqui. Si lo convierte en una función, puede utilícelo para procesar todos los archivos en una carpeta y combinarlos en una sola tabla de datos para cargar en Power Pivot.

Si desea ver el workbook, puede descargarlo aquí => Extraer fecha escalar

!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ú.