Brantwood Catering Supplies construye su informe mensual de ventas en Power Query: el fichero de pedidos, combinado con la lista de precios, combinado con el maestro de clientes, expandido y cargado. El informe de septiembre sumó 37.645,50 £. Los pedidos que había detrás sumaban 28.926,75 £.
El fichero de pedidos tenía doce líneas. El informe salió con trece filas, y nadie cuenta las filas de un informe que se actualiza en cuatro segundos.
Se habían añadido filas y después se habían quitado filas. Los dos fallos estaban en pasos distintos, no tenían nada que ver entre sí y no se anulaban.
Pedidos 12 líneas 28.926,75
tras combinar con precios 15 filas 41.796,75
tras combinar con clientes 13 filas 37.645,50
Tres de las doce líneas llevan el SKU PRD-118. La lista de precios tiene PRD-118 en dos filas: el precio de enero y la subida de agosto, añadida como fila nueva en vez de sustituir a la anterior.
Una combinación casa con todas las filas de la derecha, no con la primera, así que al expandir cada una de esas tres líneas se escribió dos veces.
La segunda combinación empujó en sentido contrario. Su tipo se había quedado en Interna, que solo conserva las filas que casaron por los dos lados. Dos pedidos de septiembre eran de cuentas abiertas ese mes y aún no dadas de alta, así que ambas líneas salieron del informe.
| Lo que se le preguntó al informe | Respondió | La verdad |
|---|---|---|
| Líneas de pedido | 13 | 12 |
| Ventas de septiembre | 37.645,50 £ | 28.926,75 £ |
| Líneas con PRD-118 | 6 | 3 |
| Clientes con ventas | 4 | 6 |
12.870,00 £ del total son una copia de sí mismos. 4.151,25 £ de negocio real de septiembre no están en el informe, y las dos cuentas nuevas a las que pertenecían pasaron un mes sin extracto.
La consulta no falló. Se actualizó en cuatro segundos, todas las columnas traían valor, y el único número que podría haber levantado la mano era un recuento de filas que nadie había apuntado.
01Trece Filas de un Fichero de Doce
Doce Líneas de Pedido, Trece Filas en el Informe
El fichero de pedidos de septiembre tiene doce líneas: la referencia de línea, el SKU, la cuenta del cliente y lo que valía la línea. Las tres últimas columnas son lo que Power Query hizo con cada línea. La lista de precios tiene el SKU PRD-118 en dos filas, una por periodo de precio, así que las tres líneas con ese SKU se escribieron dos veces al expandir la combinación — 12.870,00 £ de ventas contadas por segunda vez. La segunda combinación, contra el maestro de clientes, se quedó en una combinación interna, y las dos líneas de cuentas abiertas en septiembre no casaron con nada, así que se descartaron 4.151,25 £ de negocio real. Todas las cifras están en libras. Las doce líneas suman 28.926,75 £, el informe suma 37.645,50 £, y la diferencia de 8.718,75 £ son dos errores tirando en sentidos opuestos sin llegar a anularse.
fxLas celdas con fórmulas están resaltadas en verde
Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas
Ocho de las doce líneas se comportaron perfectamente. Eso es lo que hace difícil ver un fallo de combinación: el fichero sigue cuadrando línea a línea allá donde mires.
La columna E es la que importa. Una combinación externa izquierda contra una clave única devuelve 1 en todas las filas, y esta devolvió 2 tres veces y 0 dos veces.
Escenario: Abre el último informe que hayas construido con una combinación y compara su número de filas con el de la tabla de origen. Pon =FILAS(Informe) en una celda y =FILAS(Pedidos) al lado. Deberían dar lo mismo, y si no, la combinación es el motivo.
02Una Combinación Añade Filas, una Búsqueda No
Combinar consultas no trae un valor. Añade una columna en la que cada celda contiene una tabla — las filas de la segunda consulta que casaron con la clave de esa fila — y de momento nada ha cambiado de forma.
El paso de expandir es donde aparecen las filas. Expandir escribe una fila de salida por cada fila dentro de esas tablas anidadas, así que una clave que casa dos veces produce dos filas, y una que casa seis veces produce seis.
BUSCARX no puede hacerte eso. Devuelve la primera coincidencia y un valor por fórmula, así que una clave duplicada en la tabla de búsqueda te da un número equivocado en vez de filas de más, que es otro problema y más silencioso.
Qué cubre esto. Combinar consultas está en Excel 2016 y posteriores en Datos ▸ Obtener datos, y en Excel 2010 y 2013 como el complemento gratuito Power Query. Los tipos de combinación, el cuadro de expandir y la combinación aproximada funcionan igual en Power BI.
BUSCARX,FILTRAR,ÚNICOSyLETnecesitan Microsoft 365 o Excel 2021.BUSCARV,INDICE,COINCIDIR,CONTAR.SI,CONTAR.SI.CONJUNTO,SUMAR.SI.CONJUNTO,SUMAPRODUCTOySI.ERRORfuncionan en cualquier versión de este siglo.
Escenario: Combina dos consultas tuyas y párate antes de expandir. Haz clic en el espacio blanco junto a la palabra Table en una de las celdas nuevas y lee la vista previa de abajo. Si muestra más de una fila, esa clave está a punto de convertirse en más de una fila.
Pruébalo en la cuadrícula03Los Seis Tipos de Combinación en Una Tabla
La lista de tipos de combinación está al final del cuadro Combinar y viene por defecto en Externa izquierda. Decide qué filas sobreviven, antes de expandir nada.
| Tipo de combinación | Qué pasa | Filas aquí |
|---|---|---|
| Externa izquierda | Todas las líneas; nulos si no casó | 12 |
| Interna | Solo las líneas que casaron | 10 |
| Externa derecha | Todos los clientes; nulos al revés | 13 |
| Externa completa | Los dos lados, nulos por ambos | 15 |
| Anti izquierda | Solo las líneas que no casaron | 2 |
| Anti derecha | Solo los clientes que no pidieron | 3 |
Los recuentos de arriba son las doce líneas combinadas con un maestro de siete cuentas, cuatro de las cuales pidieron en septiembre. Los recuentos se mueven con las dos tablas, por eso conviene apuntarlos.
Externa izquierda es la que quieres para un informe construido sobre la tabla izquierda. Es el comportamiento de una búsqueda: todas las filas de partida siguen ahí, y las que no encontraron nada llegan como nulos.
Anti izquierda es la lista de excepciones que nadie construye. Combina las mismas dos tablas otra vez con Anti izquierda y el resultado son exactamente las filas que no casaron con nada: aquí, dos líneas de pedido y dos códigos de cuenta que hay que dar de alta.
Escenario: Duplica tu consulta de combinación, cambia el tipo a Anti izquierda y cárgala en una hoja. Si carga vacía, todas las claves casaron. Si carga filas, estás viendo los datos que tu informe está descartando en silencio.
Pruébalo en la cuadrícula04La Combinación Interna Quita Filas sin Decirlo
Una combinación interna es la herramienta correcta cuando no casar significa que la fila no corresponde: un cobro sin factura, un parte sin trabajo. Es la herramienta equivocada cuando no casar significa que alguien aún no ha actualizado un maestro.
En eso consiste todo este fallo. CU-77 y CU-82 son clientes reales con pedidos reales; simplemente son más nuevos que el maestro de clientes, que es un fichero de referencia que alguien mantiene a mano.
Una combinación externa izquierda habría traído las dos líneas con el nombre de cliente en nulo, y un nulo en una columna de nombres se ve desde la otra punta de la oficina. Interna quitó las filas, y una fila que no está no puede parecer rara.
Nada en la actualización avisa de una fila descartada. El número de pasos es el mismo, el tiempo de actualización es el mismo, y el panel de pasos aplicados pone "Consultas combinadas" en ambos casos.
Escenario: Abre el cuadro Combinar de una consulta que ya tengas — el engranaje junto al paso Consultas combinadas — y lee en qué tipo de combinación está de verdad. Si pone Interna y no la elegiste a propósito, cámbiala a Externa izquierda y mira cómo se mueve el recuento de filas.
Pruébalo en la cuadrícula05Por Qué un SKU Casó Dos Veces con los Precios
Una clave casa dos veces porque la tabla de la derecha no es lo que tú crees. Tres motivos cubren casi todos los casos.
Un histórico disfrazado de maestro. La lista de precios lleva una fila por periodo de precio, así que PRD-118 tiene una fila de enero y otra de agosto. Es una tabla perfectamente buena; simplemente no está indexada por SKU.
Un duplicado de verdad. El mismo producto dado de alta dos veces con dos descripciones, o un cliente en el maestro con dos códigos de cuenta tras una fusión. Aquí =CONTAR.SI(Productos[SKU];[@SKU])>1 escrito en la propia lista de precios lo encuentra en una columna.
Una clave que no es única de entrada. Combinar por Región o por Almacén en vez de por un identificador produce una fila por cada emparejamiento, que es como un fichero de 12 filas acaba en 400 y la actualización empieza a tardar un minuto.
Escenario: Antes de combinar, carga la tabla de la derecha sola y agrúpala por la clave con Transformar ▸ Agrupar por ▸ Contar filas. Ordena el recuento de mayor a menor. Todo lo que pase de 1 es una clave que multiplicará filas.
Pruébalo en la cuadrícula06Cuenta las Coincidencias Antes de Expandir
La comprobación ocupa una celda y va en la hoja, no en la consulta:
=SUMAPRODUCTO(--(CONTAR.SI(Productos[SKU];Pedidos[SKU])>1))
Devuelve 3 aquí: tres líneas de pedido cuyo SKU aparece más de una vez en la lista de precios, y por tanto tres líneas que el paso de expandir está a punto de escribir dos veces.
La misma forma sirve para el otro lado. =SUMAPRODUCTO(--(CONTAR.SI(Clientes[Código];Pedidos[Cliente])=0)) devuelve 2: las líneas que una combinación interna quitará y que una externa izquierda rellenará con nulos.
Dos celdas, ejecutadas antes de actualizar, y los dos fallos quedan señalados antes de construir el informe. Ejecútalas sobre las columnas de claves, no sobre los importes, porque los importes están aguas abajo del fallo y van a parecer verosímiles de todos modos.
Si la tabla de la derecha debería tener de verdad una fila por clave, arréglalo ahí. Transformar ▸ Quitar duplicados en la columna de la clave — o Table.Distinct en la barra de fórmulas — y la combinación vuelve a comportarse como una búsqueda.
Escenario: Pon las dos comprobaciones con CONTAR.SI junto a las tablas de origen de tu combinación y dales nombre, ClavesDup y ClavesSin. Después escribe =SI(ClavesDup+ClavesSin=0;"Combinación segura";"Revisa las claves") en la celda de encima del informe.
07Agregar en Vez de Expandir Filas
A veces la tabla de la derecha tiene de verdad muchas filas por clave y no las quieres todas. Un cliente, cuarenta pedidos: expandir te da cuarenta filas y lo que querías era un total.
El cuadro de expandir tiene una segunda pestaña justo para esto. Elige Agregar en vez de Expandir y escoge Suma, Recuento o Promedio de una columna, y el resultado es una fila de salida por fila de entrada con el agregado al lado.
Eso es el equivalente de SUMAR.SI.CONJUNTO en Power Query, y es el paso al que casi todo el mundo llega por el camino largo: expandir a cuarenta filas y agrupar de vuelta a una, que es el doble de trabajo y pierde cualquier columna que no sobreviva a la agrupación.
=SUMAR.SI.CONJUNTO(Pedidos[Valor línea];Pedidos[Cliente];A2)
Usa la fórmula cuando la respuesta va en una hoja que ya existe y el agregado de la combinación cuando va en la tabla cargada. Ninguno de los dos puede cambiarte el número de filas, que es justo el objetivo de ambos.
Escenario: Coge una combinación tuya que expanda una tabla de detalle y vuelve a abrir su cuadro de expandir. Cambia a Agregar, elige Suma de la columna de importes y compara el número de filas antes y después. Después escribe el SUMAR.SI.CONJUNTO que da la misma respuesta.
08La Combinación Distingue Mayúsculas y BUSCARV No
Power Query compara el texto exactamente. prd-104 y PRD-104 son dos claves distintas para una combinación y la misma clave para BUSCARV, BUSCARX y CONTAR.SI, que ignoran las mayúsculas.
Así que un fichero que las búsquedas han tratado bien durante años puede salir de una combinación con nulos en media tabla. El arreglo está aguas arriba: Transformar ▸ Formato ▸ MAYÚSCULAS en las dos columnas de clave antes de combinar, o Text.Upper en la barra de fórmulas.
El tipo importa igual. Un código de cuenta guardado como texto en un lado y como número en el otro no casa con nada, y el cuadro de combinar no te avisará: muestra un recuento de coincidencias bajo la vista previa, y ese recuento en 0 de 12 es el aviso.
Los espacios al final hacen lo mismo. Transformar ▸ Formato ▸ Recortar en las dos columnas de clave cuesta un paso y elimina la categoría entera, que es el mismo trabajo que hace ESPACIOS en una hoja.
Escenario: En el cuadro Combinar, lee la frase que hay bajo la vista previa antes de aceptar: "La selección coincide con N de M filas de la primera tabla". Si N no es M, párate y mira las claves en vez del tipo de combinación.
Pruébalo en la cuadrícula09La Combinación Aproximada Es una Decisión
Marca Usar combinación aproximada para realizar la combinación y Power Query casará claves que solo se parecen — "Rowan Street Deli" con "Rowan St Deli" — según un umbral de similitud que fijas entre 0 y 1.
Es realmente útil con nombres escritos por personas y realmente peligrosa con códigos. En 0,8 casará tan contenta dos clientes distintos con nombres parecidos, y nada aguas abajo dirá nunca qué filas fueron conjeturas.
Dos reglas la hacen segura. Define una tabla de transformación para que las abreviaturas conocidas queden declaradas y no deducidas, y carga la salida con su puntuación de similitud para que una persona pueda leer las bajas.
Nunca combines un identificador de forma aproximada. Un SKU, un código de cuenta o un número de factura casan exactamente o no pintan nada en la misma fila, y ahí un casi es otro producto, no una falta de ortografía.
10Cinco Comprobaciones Que Pillan una Mala Combinación
El recuento de filas. Antes que nada, y la única comprobación que pilla los dos fallos a la vez:
=FILAS(Informe)-FILAS(Pedidos) → 1
En una externa izquierda contra una clave única eso es 0. Aquí es 1, que son tres filas añadidas y dos quitadas con el mismo abrigo.
El recuento de claves duplicadas. =SUMAPRODUCTO(--(CONTAR.SI(Productos[SKU];Pedidos[SKU])>1)) sobre las columnas de clave, antes de expandir, devuelve cuántas filas van a multiplicarse.
El recuento de claves sin coincidencia. =SUMAPRODUCTO(--(CONTAR.SI(Clientes[Código];Pedidos[Cliente])=0)) devuelve las filas que quitará una combinación interna. Los dos recuentos van junto al informe, no en una libreta.
El total. =SUMA(Informe[Valor línea])-SUMA(Pedidos[Valor línea]) compara la tabla cargada con el origen. Es 0 en cualquier combinación honrada, y 8.718,75 £ aquí.
La consulta Anti izquierda. Una segunda combinación de las mismas dos tablas, tipo Anti izquierda, cargada en una hoja. Vacía es un aprobado, y lo que salga es la lista de claves que alguien tiene que dar de alta.
Escenario: Añade la comprobación de filas en la hoja, encima de tu tabla cargada, como =FILAS(Informe)-FILAS(Pedidos), y dale formato condicional en rojo cuando no sea 0. Es la auditoría más barata del libro.
11Ocho Cosas Que Muerden
- Dejar el tipo de combinación en el que se usó la última vez. El cuadro lo recuerda, Interna se parece a Externa izquierda en los pasos aplicados, y solo el recuento de filas lo sabe.
- Expandir un histórico. Una fila por periodo de precio, por cambio de tarifa o por dirección no es un maestro, y combinar por su clave multiplica todas tus filas.
- Mirar un total para comprobar una combinación. Un total alto por un duplicado y bajo por una fila perdida es un número verosímil, y fue el único número que alguien comprobó aquí.
- Combinar códigos de forma aproximada. Una similitud de 0,9 entre dos números de cuenta no significa nada, y la coincidencia que produce es silenciosa.
- Expandir y después agrupar de vuelta. El doble de trabajo y una columna perdida; la pestaña Agregar del cuadro de expandir lo hace en un paso.
- Dar por hecho que la combinación ignora las mayúsculas.
BUSCARVsí,BUSCARXsí, Power Query no, y el mismo fichero se comporta distinto en cada uno. - Combinar una clave de texto contra una numérica. Casa 0 de 12 filas, llena de nulos todas las columnas y no da error nunca.
- Fiarse de una actualización de cuatro segundos. La velocidad es de la consulta, no de los datos, y una actualización limpia no dice nada sobre si las filas son las de partida.
12Mini Ejercicios
- Construye las dos comprobaciones de claves sobre el fichero de ejemplo:
=SUMAPRODUCTO(--(CONTAR.SI(Productos[SKU];Pedidos[SKU])>1))y la versión con=0contra la lista de clientes. Espera 3 y 2. - Combina los pedidos con la lista de precios, expande y cuenta las filas antes y después. Espera 12 y 15, y después localiza las tres líneas que se duplicaron.
- Cambia la combinación de clientes de Interna a Externa izquierda y filtra a nulos la columna de nombre de cliente expandida. Espera dos filas: CU-77 y CU-82.
- Duplica la combinación de clientes, pon su tipo en Anti izquierda y cárgala. Debería devolver esas mismas dos líneas, 4.151,25 £ entre las dos, como una lista que puedes enviar a quien mantiene el maestro.
- Quita duplicados de la lista de precios por SKU, actualiza y comprueba el total. Espera 28.926,75 £ con los dos fallos arreglados.
Lo Que Hay Que Llevarse
Una combinación no es una búsqueda. Una búsqueda responde a una pregunta por fila y como mucho puede darte un valor equivocado; una combinación decide qué filas existen, y el paso de expandir puede devolverte más filas de las que tenías, o menos.
Así que trata el recuento de filas como la primera salida que compruebas. Una externa izquierda contra una clave única devuelve exactamente las filas de las que partió, y cualquier otro número es una clave que no has mirado.
Y comprueba las claves, no las cifras.
Que la lista de precios tuviera PRD-118 dos veces y que al maestro de clientes le faltaran dos códigos se veía con un solo CONTAR.SI antes de combinar nada — que es la diferencia entre 28.926,75 £ y 37.645,50 £ en un informe que se actualizó limpio y cuadraba en todas las líneas que alguien leyó.