Secretos para destacar en tu empresa

Power Query

hace 2 semanas

Columna personalizada

Buenas tardes, una consulta...

Tengo una consulta en mi modelo de datos en la que cada registro se le llama "Subposición".
Esta subposición hace referencia a cada entrega de mercadería de una "Posición" de una "Orden de compra", la "Posición" vendría a hacer referencia a cada item dentro de la orden de compra.
Por lo tanto cada subposición tendra asociada una posición de una orden de compra, este valor se ve en la tabla en una concatenación llamada OC-Pos.

Por ejemplo:
Orden de compra: 4500100000
Posición: 10
OC-Pos: 4500100000-10

Y cada entrada de mercadería (subposición) será 1, 2, 3...

Si para el caso del ejemplo, se entregó mercadería 3 veces tendré 3 registros con los datos:

Orden de compra: 4500100000
Posición: 10
Subposición: 1

Orden de compra: 4500100000
Posición: 10
Subposición: 2

Orden de compra: 4500100000
Posición: 10
Subposición: 3

Cada OC-Pos tiene una cantidad de mercadería comprada, la cual se ve en la columna "Cantidad OC", lo cual es el total a ser entregado, dicho valor se repetirá para cada registro con la misma OC-Pos.

Por otro lado, para cada registro (es decir, subposición), tengo la cantidad que entrego el proveedor para esa subposición, cuyo valor se ve en la columna llamada "Cant. Recibida".

Siguiendo el ejemplo de arriba digamos que en la posición 10 de la orden de compra 4500100000 la "Cantidad OC" es igual a 100 unidades. En la primer entrega (subposicion = 1) la "Cant. Recibida" es igual a 40, en la segunda entrega (subposicion = 2) la "Cant. Recibida" es igual a 50, y en la tercer entrega (subposicion = 3) la "Cant. Recibida" es igual a 10... dando un total entregado de 100.

Quiero crear una nueva columna llamada "Por entregar" que debe calcular para cada registro la cantidad pendiente por entregar según la cantidad total (Cantidad OC) y la cantidad entregada hasta ese momento.

Siguiendo el ejemplo de arriba:

Orden de compra: 4500100000
Posición: 10
Subposición: 1
Cantidad OC: 100
Cant. Recibida: 40
Por entregar: 60

Orden de compra: 4500100000
Posición: 10
Subposición: 2
Cantidad OC: 100
Cant. Recibida: 50
Por entregar: 10

Orden de compra: 4500100000
Posición: 10
Subposición: 3
Cantidad OC: 100
Cant. Recibida: 10
Por entregar: 0

Además es posible que la suma de Cant. Recibida sea mayor a la Cantidad OC, cuando se de dicho caso, en la columna Por entregar debe mostrarse 0.

Ejemplo de este caso: En la subposición 3 se entregaron 20 unidades en lugar de 10. Por lo que Cantidad OC = 100 y la suma de Cant. Recibida para esa OC-Pos = 110. Para la subposición en la que se alcanzo el total de Cantidad OC se debe mostrar 0, y no -10.

Orden de compra: 4500100000
Posición: 10
Subposición: 1
Cantidad OC: 100
Cant. Recibida: 40
Por entregar: 60

Orden de compra: 4500100000
Posición: 10
Subposición: 2
Cantidad OC: 100
Cant. Recibida: 50
Por entregar: 10

Orden de compra: 4500100000
Posición: 10
Subposición: 3
Cantidad OC: 100
Cant. Recibida: 20
Por entregar: 0

¿Cómo puedo resolver esto para crear esta columna en Power Query?

Respuestas

AsistenteVirtualDatdata

hace 2 semanas

🤖 Esta respuesta fue generada automáticamente por nuestra inteligencia artificial local. Es un intento único de ayudarte al instante. No responderá a mensajes posteriores en este hilo.

Hola, ¡gracias por tu consulta!

Excelente pregunta. Entiendo que tienes una tabla con registros de entregas de mercadería (Subposiciones), donde cada una está asociada a un ítem de una Orden de Compra (OC-Pos). Necesitas calcular una columna que muestre la cantidad pendiente por entregar para cada registro, basándose en la "Cantidad OC" total para esa OC-Pos y la suma de las cantidades ya recibidas ("Cant. Recibida") hasta ese registro.

Para lograr esto en Power Query, necesitamos calcular un total acumulado de la "Cant. Recibida" dentro de cada grupo "OC-Pos" y luego restar ese acumulado de la "Cantidad OC". Aquí te presento la lógica y el código M para hacerlo:

Primero, agruparemos los datos por la columna "OC-Pos".
Dentro de cada grupo, añadiremos una columna de índice para determinar el orden de las subposiciones y poder calcular el acumulado correctamente.
Luego, dentro de cada grupo, calcularemos el total acumulado de "Cant. Recibida" para cada fila, sumando las cantidades de las filas anteriores y la actual dentro de ese grupo.
Finalmente, expandiremos los datos y calcularemos la columna "Por entregar" restando el total acumulado a la "Cantidad OC", asegurándonos de que el resultado no sea negativo.

Aquí tienes el código M. Deberás reemplazar tu_paso_anterior con el nombre del paso previo en tu consulta (por ejemplo, #"Tipo Cambiado") y listar todas tus columnas originales en el paso de expansión.

let
    Source = tu_paso_anterior,
    
    // Agrupar por OC-Pos y mantener las filas agrupadas
    #"Agrupar por OC-Pos" = Table.Group(Source, {"OC-Pos"}, {{"DatosAgrupados", each
        (let
            // Añadir índice dentro de cada grupo para ordenar las subposiciones
            TablaConIndice = Table.AddIndexColumn(_, "Subposicion_Calculada", 1, 1),
            // Calcular el total acumulado de Cant. Recibida dentro del grupo
            TablaConAcumulado = Table.AddColumn(TablaConIndice, "Cant_Recibida_Acumulada", each
                List.Sum(
                    Table.SelectRows(TablaConIndice, (filaInterior) => filaInterior["Subposicion_Calculada"] <= _["Subposicion_Calculada"])["Cant. Recibida"]
                )
            ),
            // Calcular "Por entregar", asegurando que no sea negativo
            TablaFinalGrupo = Table.AddColumn(TablaConAcumulado, "Por entregar", each Number.Max(0, [Cantidad OC] - [Cant_Recibida_Acumulada]), type number)
        in
            TablaFinalGrupo
        )}}),
    
    // Expandir la columna con los datos agrupados y transformados.
    // ¡IMPORTANTE! Reemplaza {"Columna1", "Columna2", ...} con los nombres de TODAS
    // tus columnas originales (excepto "OC-Pos", que ya está fuera del grupo).
    // Asegúrate de incluir las nuevas columnas "Subposicion_Calculada", "Cant_Recibida_Acumulada" y "Por entregar".
    // Ejemplo: {"Orden de compra", "Posicion", "Cantidad OC", "Cant. Recibida", "Subposicion_Calculada", "Cant_Recibida_Acumulada", "Por entregar"}
    #"Expandir Datos Agrupados" = Table.ExpandTableColumn(#"Agrupar por OC-Pos", "DatosAgrupados", {"Columna1Original", "Columna2Original", "Cantidad OC", "Cant. Recibida", "Subposicion_Calculada", "Cant_Recibida_Acumulada", "Por entregar"}),

    // Opcional: Reordenar columnas si deseas una disposición específica
    // #"Columnas Reordenadas" = Table.ReorderColumns(#"Expandir Datos Agrupados",{"OC-Pos", "Subposicion_Calculada", ...})

    ResultadoFinal = #"Expandir Datos Agrupados"
in
    ResultadoFinal

Notas importantes:

Ajusta tu_paso_anterior al nombre correcto.
En el paso #"Expandir Datos Agrupados", modifica la lista entre llaves {...} para incluir todas las columnas originales de tu tabla (excepto "OC-Pos") y asegúrate de que las nuevas columnas "Subposicion_Calculada", "Cant_Recibida_Acumulada" y "Por entregar" también estén listadas.
La columna "Subposicion_Calculada" es el consecutivo que buscabas (1, 2, 3...). Puedes usarla si la necesitas para mostrar o verificar el orden. La columna "Cant_Recibida_Acumulada" muestra el total recibido hasta esa fila dentro del grupo.
La función Number.Max(0, ...) asegura que la columna "Por entregar" nunca muestre un valor negativo, incluso si la suma de "Cant. Recibida" excede la "Cantidad OC".