miércoles, 8 de febrero de 2023

Power Query: Reagrupar por fila

Recientemente me topé con un problema curioso, fácil de resolver en la hoja de cálculo, pero no tan sencillo (diría yo) de hacerlo con Power Query.
Se trata de recorrer cada fila de nuestra tabla e ir agrupando conceptos iguales.

El ejemplo sobre el que voy a basar la explicación es bastante gráfico.
Tenemeos un listado de facturas, donde cada una de ellas tiene distintos tipos de IVA aplicados... pero en posiciones/columnas dispares.
Lo vemos en la imagen siguiente:
Power Query: Reagrupar por fila

Si nos fijamos en la imagen, en concreto en la segunda factura, veremos que devuelve registros con dos tipos de IVA en tres movimientos, colocados 'aleatoriamente' en las distintas columnas... una base imponible al 10% y otras dos al 21%.
El objetivo es distribuir y ubicar, de manera homogénea, todas las bases al 4% en la misma columna, las bases al 10% en su propia columna, e igual con las bases al 21%.., esto es, reagrupar los elementos de la misma fila...

Como siempre cargaremos nuestro origen y editaremos la consulta con el siguiente código M:
let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
 
    lstTipos={"Tipo_1","Tipo_2","Tipo_3"},
    lstCamposCrear={"BI4","BI10","BI21"},
    lstPorc={0.04,0.10,0.21,0.00},
    
    //definimos el límite con un valor
    Contador=List.Count(lstCamposCrear),

    //creamos una función recursiva que recorra cada fila/registro
    RepartoBases=(TablaTemp as table, n as number)=> 
            let TablaFinal=Table.AddColumn(
                TablaTemp, //trabajamos siempre sobre la tabla modificada
                lstCamposCrear{n},  //asignamos un nombre de columna asociada a las listas previas
                each let rw=_ in    //sobre el registro que representa cada fila
                                    //aplicamos ciertas transformaciones
                                    //que nos permiten obtener el dato de la columna buscada,,,
                   try List.Sum(List.Select( 
                            List.Transform(lstTipos, 
                                each 
                                let 
                                    i=Text.End(_,1)
                                in 
                                    if Record.FieldOrDefault(rw,_)=lstPorc{n} then 
                                            Record.FieldOrDefault(rw,"Base_" & i) 
                                    else 0),
                        each _<>0))
                    otherwise 0)
        in 
            //controlamos la finalización del proceso cuando lleguemos al último reemplazamiento
            if n+1=Contador then TablaFinal // terminamos
                else @RepartoBases(TablaFinal,n+1),   // mantenemos el ciclo de sustitución
            
    //aplicamos nuestra función recursiva anterior sobre la Tabla Original
    Resultado=RepartoBases(Origen, 0)

in
    Resultado

El código es algo largo, pero efectivo, ya que nos devuelve lo deseado...
De manera recursiva se ha procedido a crear las tres columnas necesarias (una por cada tipo de IVA) e ir incorporando como dato, el aacumulado/suma de las distintas bases coincidentes con dicho tipo.

En este ejemplo me he apoyado en la función Record.FieldOrDefault que permite recuperar el dato de un registro para un campo concreto... interesante cunado trabajamos por 'filas'.
Espero te resulte de utilidad, y por supuesto, si conoces alguna forma alternativa... será un placer compartirla.

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.