Tablas dinámicas: Diferencia con el dato previo y más

Como continuación del post anterior, donde exponíamos como obtener para cada registro la diferencia respecto a su dato previo, y recordaremos como llegar al mismo resultado empleando tablas dinámicas.
Curiosamente, hace muchos años (en el 2014) publiqué un artículo sobre este tema precisamente...
Este método nos permitirá comparar técnicas.

Partiremos de la misma tabla trabajada en el artículo anterior comentado:

Sobre la tabla creamos un informe de tabla dinámica donde llevaremos el campo 'Importe' al área de valores (DOS veces!); el campo 'Fecha' agrupado por Fecha al área de filas, pero subordinado al campo 'país' también al área de filas.

Sobre el segundo de los campos de 'Suma de Importes' haremos clic derecho, y sobre la opción de 'Mostrar valores como...' buscaremos el cálculo de Diferencia de..., lo que abrirá una ventana diálogo donde configurar este cálculo (revisa el post del 2014 comentado).
Elegiremos como campo base las 'Fechas', y como 'elemento base' el '(Anterior)'

Tras aceptar el resultado es el esperado!!

Comprobamos de qué forma tan simple llegamos a un resultado correcto... punto para las tablas dinámicas ;-)

Seguir leyendo...

Diferencia con dato anterior condicionado

Un clásico de los procesos de cálculo es obtener la diferencia de un valor respecto a su equivalente previo o anterior... este delta o diferencia entre filas nos aporta normalmente variaciones de consumo o sencillamente incrementos/decrementos de una variable.

Hoy veremos cómo conseguir con fórmulas esos datos para cada fila con fórmulas estándar de nuestra hoja de cálculo.
Además, como no, añadiremos un punto de dificultad, ya que no será necesario la ordenación previa de los datos... y para rematar, nuestras diferencias se calcularán teniendo en cuenta una condición de 'País' (de cualquier cosa en realidad).
Un punto de partida importante a tener presente: Las combinaciones de país-fecha sobre las que trabajaremos deben ser únicas!!

Las funciones que vamos a emplear son las ya conocidas:
LET
FILTRAR
ORDENARPOR
y las clásicas:
INDICE
COINCIDIR
y nuestro SI condicional
Funciones todas ellas vistas en el blog (busca en la categoría de funciones)

Sobre nuestra tabla en la hoja de cálculo (sin ningún tipo de orden por 'País' o 'Fecha', añadimos un nuevo campo con la siguiente fórmula:
=LET(TblOrdenado;ORDENARPOR(TblDATOS;[Fecha];1);
oPais;INDICE(TblOrdenado;0;1);
oFecha;INDICE(TblOrdenado;0;2);
oImporte;INDICE(TblOrdenado;0;3);
fImportes;FILTRAR(oImporte;(oPais=[@País]));
fFechas;FILTRAR(oFecha;(oPais=[@País]));
Pos;COINCIDIR($B2;fFechas;0)-1;
SI(Pos=0;"";INDICE(fImportes;Pos+1)-INDICE(fImportes;Pos)))

La explicación de la fórmula es simple... Y más al apoyarnos en el uso de la función LET.
En primer lugar ordenamos en sentido ascendente la Tabla empleando el campo 'Fecha' como criterio...
OJO!!, no es una ordenación física o real sobre la hoja de cálculo... se realiza a nivel interno de la fórmula ;-)
TblOrdenado;ORDENARPOR(TblDATOS;[Fecha];1);
queda asociada a nuestra variable 'tblOrdenado'.

En el segundo paso, sobre la tabla ordenada, asignamos nuevas variables a cada uno de los campos sobre los que vamos a trabajar:
oPais;INDICE(TblOrdenado;0;1);
oFecha;INDICE(TblOrdenado;0;2);
oImporte;INDICE(TblOrdenado;0;3);
con esto recuperamos los tres campos implicados...

En el tercer paso segmentamos, con la función FILTRAR, registros coincidentes con el 'País':
fFechas;FILTRAR(oFecha;(oPais=[@País]));
con lo que obtendríamos un listado de fechas para cada 'país' previamente ordenado en sentido ascendente

El penúltimo paso es clave:
Pos;COINCIDIR($B2;fFechas;0)-1;
nos permite obtener la posición, de entre las fechas del 'país' correspondiente, que ocupa la fecha anterior!!

Posición necesaria para el último paso:
SI(Pos=0;"";INDICE(fImportes;Pos+1)-INDICE(fImportes;Pos))
donde nuestro condicional gestiona que dato, de entre los 'Importes' queremos restar; esto es, el corriente menos el previo (teniendo en cuenta las lfechas y el 'País').

Quizá el resultado obtenido te resulte 'difícil' de analizar... pero si, a efectos de comprobación, ordenas por 'País' y 'Fecha' y restas directamente uno menos el previo comprobarás la exactitud del cálculo.

Para facilitar la comprensión, y basada en la fórmula anterior, he incorporado otra fórmula que devuelve el ordinal de cada registro según 'País' y 'Fecha':
=LET(TblOrdenado;ORDENARPOR(TblDATOS;[Fecha];1);
oPais;INDICE(TblOrdenado;0;1);
oFecha;INDICE(TblOrdenado;0;2);
oImporte;INDICE(TblOrdenado;0;3);
fImportes;FILTRAR(oImporte;(oPais=[@País]));
fFechas;FILTRAR(oFecha;(oPais=[@País]));
Pos;COINCIDIR($B2;fFechas;0)-1;
Pos)


Nuevamente la función LET, FILTRAR, ORDENARPOR... (disponible para versiones 365) nos facilitan y ayudan en cálculos, que de otra forma, se nos antojan algo más elaborados...

Seguir leyendo...

Power Query: Text-BeforeDelimiter y Text-AfterDelimiter

Dicen que no hay dos sin tres.. y en este caso cerraré mi triángulo favorito: VBA-fórmulas-Power Query replicando los algoritmos que nos permitieros extraer o eliminar subcadenas de texto (revisa la entrada que hablamos de VbScript.RegExp y la que expuse con igual solución empleando fórmulas).

Hoy aplicaremos algunas funciones de texto con lenguaje M dentro del editor de Consultas de Power Query:
Text.AfterDelimiter(text as nullable text, delimiter as text, optional index as any) as any
Devuelve la parte de la cadena de texto trabajada después del delimitador especificado. El argumento opcional index indica qué posición del delimitador se debe tener en cuenta.
Además este argumento opcional admite una forma especial de lista, donde podemos definir no solo la posición sino si la indexación debe realizarse desde el principio o el final de la entrada:
{1, RelativePosition.FromEnd}
{2, RelativePosition.FromStart}

Otra función casi idéntica es:
Text.BeforeDelimiter(text as nullable text, delimiter as text, optional index as any) as any
Con significado análogo. Devuelve la parte de la cadena de texto antes del delimitador especificado. El argumento opcional index indica qué posición del delimitador se debe tener en cuenta.
Además este argumento opcional admite una forma especial de lista, donde podemos definir no solo la posición sino si la indexación debe realizarse desde el principio o el final de la entrada:
{1, RelativePosition.FromEnd}
{2, RelativePosition.FromStart}

Una última función M que completa la terna es:
Text.BetweenDelimiters(text as nullable text, startDelimiter as text, endDelimiter as text, optional startIndex as any, optional endIndex as any) as any
Devuelve la parte de la cadena de texto entre los delimitadores especificados: startDelimiter y endDelimiter.
El argumento startIndex indica desde qué posición de startDelimiter se debe tener en cuenta.
Al igual que en las otras dos funciones provias podemos trabajar en los dos argumentos: startIndex y endIndex en forma de lista empleando las funciones RelativePosition.FromStart y RelativePosition.FromEnd

Estas funciones, de alguna manera, potencian el comportamiento de las funciones de hoja de cálculo IZQUIERDA, DERECHA, EXTRAE, LARGO... - homólogas a Text.Start o Text.End

Vamos con nuestro ejemplo... recuerda que vamos a extraer y/o eliminar subcadenas dentro de un texto que cumplan ciertas condiciones, que estén delimitados por un caracter de inicio y otro de fin.

Como siempre fundamental analizar cuáles son los delimitadores!!!.
En la imagen anterior puedes comprobar que he variado los de los ejemplos previos con fórmulas y VBA, y he optado por unas clásicas llaves {}.
A partir de la tabla que contiene las tres cadenas de texto a tratar, y que hemos cargado en nuestro editor de Power Query... generaremos dos funciones personalizadas, que facilitarán nuestra tarea.

La primera función nos permite obtener una lista de coincidencias:

(TextoOrigen as text, DelimitadorIni as text, DelimitadorFin as text)=>
let
    //convertimos a texto (por si acaso) el texto origen aportado
    PasoUNO = Text.From(TextoOrigen),

    //partimos por el primer delimitador
    PrimerSplit = Text.Split(PasoUNO, DelimitadorIni),

    //Eliminamos elementos vacíos que se hayan podido generar tras la separación/split
    FiltroContieneFin = List.Select(PrimerSplit, each Text.Contains(_,DelimitadorFin)),

    //Text.AfterDelimiter Devuelve la parte del texto después del elemento delimitador especificado
    //aplicamos la función elemento a elemento si contiene el DelimitadorFin
    RecuperamosTextoAntesDelimitadorFin = List.Transform(FiltroContieneFin, 
                            each 
                                if Text.Contains(_, DelimitadorFin) then 
                                    Text.BeforeDelimiter(_, DelimitadorFin, 0) 
                                else _),
    
    //nos quedamos solo con los elementos de la lista con datos
    ListaConValor = List.Select(RecuperamosTextoAntesDelimitadorFin, each _<>"" or _<>null),

    //y concluimos concatenando los elementos de la lista final
    ConcatenamosTexto = Text.Combine(List.Transform(ListaConValor, each DelimitadorIni & _ & DelimitadorFin), "|")
in
    ConcatenamosTexto

Esta función replica el método .Execute de RegExp, y por tanto obtendremos un listado de las coincidencias de las subcadenas encontradas entre los delimitadores dados {}.
La clave quizá esté en la transformación de la lista 'partida' conseguida con el PrimerSplit... donde trabajamos solo con aquellos elementos de la lista que contengan el delimitador final...

La segunda función será capaz de eliminar las subcadenas coincidentes, como el método .Replace.

(TextoOrigen as text, DelimitadorIni as text, DelimitadorFin as text)=>
let
    //convertimos a texto (por si acaso) el texto origen aportado
    PasoUNO = Text.From(TextoOrigen),

    //partimos por el primer delimitador
    PrimerSplit = Text.Split(PasoUNO, DelimitadorIni),

    //Eliminamos elementos vacíos que se hayan podido generar tras la separación/split
    LimpiamosVacios = List.Select(PrimerSplit, each _<>"" or _<>null),

    //Text.AfterDelimiter Devuelve la parte del texto después del elemento delimitador especificado
    //aplicamos la función elemento a elemento si contiene el DelimitadorFin
    RecuperamosTextoAntesDelimitadorFin = List.Transform(LimpiamosVacios, 
                            each 
                                if Text.Contains(_, DelimitadorFin) then 
                                    Text.AfterDelimiter(_, DelimitadorFin, 0) 
                                else _),
    
    //nos quedamos solo con los elementos de la lista con datos
    ListaConValor = List.Select(RecuperamosTextoAntesDelimitadorFin, each _<>"" or _<>null),

    //y concluimos concatenando los elementos de la lista final
    ConcatenamosTexto = Text.Combine(ListaConValor, "")
in
    ConcatenamosTexto

OJO con las similitudes!!... y fíjate bien que en la transformación empleamos la función M: Text.AfterDelimeter (a diferencia de la anterior, que empleamos: Text.BeforeDelimeter)...

Con las dos funciones creadas podemos aplicarlas, agregando columnas personalizadas o invocando a funciones personalizadas:

let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla2"]}[Content],

    //extraemos coincidencias con nuestra función
    FunciónPersonalizadaInvocada = Table.AddColumn(Origen, "fxExtrayendoMultiples", each fxExtraeMultiples([campo1], "{", "}")),
    
    //eliminamos coincidencias encontradas con nuestra función
    FunciónPersonalizadaInvocada2 = Table.AddColumn(FunciónPersonalizadaInvocada, "fxEliminaMultiples", each fxEliminaMultiples([campo1], "{", "}")),
    
    //y quitamos la columna traida de la hoja de cálculo
    ColumnaQuitada = Table.RemoveColumns(FunciónPersonalizadaInvocada2,{"campo1"})
in
    ColumnaQuitada

Donde vemos el resultado que queríamos:

Funciones M de texto muy simples pero muy potentes a tener siempre presente ;-)

Seguir leyendo...

Eliminar y Extraer partes de una cadena texto

Al hilo de la entrada anterior sobre VBScript.RegExp donde exponía algunos ejemplos de 'Regular Expressions' donde recuperar/extraer partes de una cadena de texto (con el método .Execute) o eliminar/sustituirlas con el método .Replace del objeto mencionado, algunos lectores me han preguntado cómo se podría llegar al mismo resultado empleando formulación estándar...

Como es bien sabido, me encanta obtener fórmulas 'fáciles' que procesen la información hasta cumplir objetivos... y me puse a trabajar.

Por supuesto la solución propuesta no es única y hay alguna posibilidad más...

Vamos con la primera fórmula, la sencilla, que replicaría el comportamiento de .Execute. Así en mi celda E15 y trabajando sobre la cadena de texto de D15:
=LET(cadena;D15; delimitadorINI;"i"; delimitadorFIN;"f"; elementos;TRANSPONER(XMLFILTRO("<i><f><d>"&SUSTITUIR(cadena;delimitadorINI;"</d><d>")&"</d></f></i>";"//f/d")); recomposicion;delimitadorINI&(IZQUIERDA(elementos;ENCONTRAR(delimitadorFIN;elementos)-1))&delimitadorFIN; FILTRAR(recomposicion;NO(ESERROR(recomposicion))))

Comprobamos como nos aprovechamos del uso de la función LET para construir una secuencia de pasos donde tratamos la cadena de texto original...
A partir de las tres variables:
- cadena
- delimitadorINI
- delimitadorFIN
aplicamos la ya conocida función XMLFILTRO y su funcionalidad para separar textos
Para 'recomponer' con los delimitadores de inicio y fin, y poder recuperar solo los que nos interesan con la función FILTRAR.
. Es decir, con: recomposicion;delimitadorINI&(IZQUIERDA(elementos;ENCONTRAR(delimitadorFIN;elementos)-1))&delimitadorFIN;
obtendríamos un vector del tipo:
#¡VALOR! i100f i110f i200f
haciendos necesario en el paso siguiente quitar el 'error':
FILTRAR(recomposicion;NO(ESERROR(recomposicion))))
Quedándonos exclusivamente con los elementos o partes del texto que cumplen la condición de estar entre los delimitadores...

La segunda fórmula (se asemeja a .Replace) trabaja de manera inversa... queremos mantener el resto de la cadena de texto original, excepto lo que esté entre los delimitadores.
Así en la celda E18 y trabajando sobre la celda que contiene la cadena de texto en D15 :
=LET(cadena;D15; delimitadorINI;"i"; delimitadorFIN;"f"; elementos;TRANSPONER(XMLFILTRO("<i><f><d>"&SUSTITUIR(cadena;delimitadorINI;"</d><d>")&"</d></f></i>";"//f/d")); recomposicion;SI.ERROR(DERECHA(elementos;LARGO(elementos)-ENCONTRAR(delimitadorFIN;elementos)); elementos); eltosAmantener;FILTRAR(recomposicion;NO(ESERROR(elementos))); UNIRCADENAS("";VERDADERO;eltosAmantener))

Nota las diferencias en los últimos pasos con la fórmula previa... donde la recomposición se genera de forma complementaria, y terminamos empleando la función UNIRCADENAS para concatenar el vector resultante
SUMA( ; ; )/

ATENCIÖN!!... un aspecto fundamental será la elección de los delimitadores de inicio y fin...
usar caracteres poco frecuentes como {}, [], <> son siempre muy buena idea.

Sin duda algo más elaborado que el método aplicado con VBA ;-)

En su caso, cuando Microsoft libere o lance finalmente la función LAMBDA para todo el mundo, facilitará algo estos procesos.. al permitirnos y facilitarnos la recursividad.

Seguir leyendo...

VBA: El objeto VbScript.RegExp

Estos días pasados he tenido que lidiar, dura pugna!, con el tratamiento y/o recuperación de ciertas partes de uns textos, o cadenas de textos...
Y por desgracia, aplicar la formulación estándar en la hoja de cálculo se hacía extremadamente complejo :-'(

Me acordé entonces de un objeto que se emplea habitualmente a la hora de construir código HTML y páginas web (seguramente en otros campos también), hablo del objeto VbScript.RegExp

Así pues, te recomiendo en primer lugar te familiarices con las Regular Expressions (RegEx o RegExp).
Hay muchas web explicativas, e incluso web para hacer tus test...
Yo te recomiendo esta página de Microsoft.
Allí podrás encontrar bastante documentación y ejemplos para empezar...
Pero mucho más importante.. nos habla de las propiedades, métodos, colecciones del objeto dentro de VBA para Excel. En el post de hoy te proporcionaré dos ejemplos, aplicados en VBA mediante UDF (funciones personalizadas.

En el primer ejemplo limpiaremos de un código HTML básico todas aquellas etiquetas que NO tienen cierre...
Seguramente sabes que el HTML es un 'lenguaje' (muchos dicen que no lo es) basado en nodos, definidos por inicios y fin de etiquetas.. aunque hay una lista más o menos corta de etiquetas que no se cierran. A modo de ejemplo:
img, br, hr, input, meta, link, html

Y sobre esta lista se basa la primera UDF, ya que deseo eliminar éstas para poder luego dar un tratamiento con objetos de lectura de XML (otro 'lenguaje' similar basado en nodos... pero que siempre finalizan sus etiquetas!).
Por tanto, después de analizar el código HTML, y comprobar que nada relevante aportan esas etiquetas 'especiales', decido eliminarlas... con la siguiente UDF insertada en un módulo estándar del editor de VBA de Excel:

Function QuitaEtiquetasHTMLsinFinal(cadena As String) As String
Dim RegEx As Object
Set RegEx = CreateObject("VbScript.RegExp")

With RegEx
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
    'identificamos cualquiera de las etiquetas de la lista indicada
    'son algunas de las etiquetas (no todas) más frecuentes que nos encontramos
    'en HTML que no tienen fin </...>
    .pattern = "<(br|hr)+>|<(img|br|hr|input|meta|link|html).+>?"
End With

'aplica el método Replace
QuitaEtiquetasHTMLsinFinal = RegEx.Replace(cadena, "")
Set RegEx = Nothing
End Function

Si aplicamos la función sobre el contenido de una celda veríamos...

La explicación del modelo de 'Regular Expressions' empleado en el ejemplo:
"<(br|hr)+>|<(img|br|hr|input|meta|link|html).+>?"
nos dice que identifica o bien el conjunto de las etiquetas:
"<(br|hr)+>
br o hr sin más, entre los símbolos de menor-mayor..
o también el conjunto:
<(img|br|hr|input|meta|link|html).+>?"
que recoge la lista de esas etiquetas que comienzan por < seguidas de cualquier cosa (atributos de cualquier clase)...

En el siguiente ejemplo aplicaremos de manera opcional el método Replace o el método Execute del objeto VbScript.RegEx.

Y el código de la función personalizada, dentro del editor de VBA, en un módulo estándar:

Function TransformaCadenasTexto(cadena As String, modelo As String, Optional Metodo As String)
Dim RegEx As Object
Set RegEx = CreateObject("VbScript.RegExp")

With RegEx
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
    .pattern = modelo
End With

Dim arrDatos() As Variant
'método Execute para obtener una lista de todas las coincidencias...
If IsMissing(Metodo) Or Metodo = "" Or LCase(Metodo) = "execute" Then
    Set coincidencias = RegEx.Execute(cadena)
    'listamos las coincidencias en una array
    For Each Match In coincidencias
        x = x + 1
        ReDim Preserve arrDatos(1 To x) As Variant
        arrDatos(x) = Match.Value
    Next Match
    'para finalmente devolver la array a la función,
    'será por tanto una fórmula matricial..
    TransformaCadenasTexto = arrDatos()
ElseIf LCase(Metodo) = "replace" Then
    'reemplazamos por el mismo valor, pero añadiendo el menor y mayor al inicio y final
    TransformaCadenasTexto = RegEx.Replace(cadena, "<$&>")
    'o sencillamente 'reemplazar por nada' o eliminar... el clásico
    'TransformaCadenasTexto = RegEx.Replace(cadena, "")
Else

End If

Set RegEx = Nothing
End Function

La función permite personalizar como argumento la expresión que necesitemos... y de igual modo, como argumento opcional indicar qué tipo de método emplear...

Las expresiones empleadas en los dos casos siguientes;
- en E7: "i.*?f" identifica subcadenas entre la i y la f
- en E10: "i.*?f" que identifica cualquier otro caracter no contenido en subcadenas entre una i y una f

En E7 la primera función emplea el método Execute, por lo que tras identificar todas las coincidencias las lista... tras pasarla por una array.
En E10 usamos el método Replace para reemplazar esas subcadenas por la expresión "<$&>" que representa cada elemento de la lista de coincidencias...

Una buena ayuda para el tratamiento de textos.
Un último comentario... recomendado activar/habilitar la librería correspondiente: Microsoft VBScript Regular Expressions 5.5 desde el menú del editor de VB, en Herramientas > Referencias

Seguir leyendo...

Power Query: Expandir columnas con condición

Continuando con las posibilidades que ofrece Power Query en cuanto a formas de combinar-unir distintas tablas o consultas (revisa las entradas previas respecto a Table.Join, Table.SelectRows o Table.AddJoinColumn).
En la mayoría de los casos, una vez realizadas las uniones o 'joins' nos encontramos con una nueva columna que debemos expandir... y sin las relaciones son muchas, se hace un poco tedioso :-(

Por este motivo hoy veremos un poco de código M que nos permitirá expandir las columnas cuando se cumplan ciertos criterios (obviamente las que puedan ser expandidas jeje).

Se trata por tanto de expandir las Tablas contenidas en las columnas procedentes de una join cuando cumplan ciertos patrones...

Veamos un ejemplo sencillo...
Empezaremos cargando solo como conexión dos tablas de nuestra hoja de cálculo: 'TblALT_ED' y 'TblCOMISION

En este ejercicio me interesa que notes que la TblCOMISION tiene además de filas vacías, elementos duplicados!!.
Sabemos que esto es un problema, ya que en combinaciones 'normales' (con Table.NestedJoin) obtendremos duplicidades en aquellos registros 'duplicados'!!
Lo interesante es que el código que escribiremos a continuación permite controlar esta situación y tomar una decisión al respecto...

Así pues, tras la carga de ambas tablas en el editor de consultas de Power Query abriremos una Consulta en blanco e introduciremos el siguiente código M:

let
    //Añadimos una nueva columna vinculada a la TblCOMISION
    Origen = Table.AddJoinColumn(TblALT_ED,{"Producto","Comercial"},
                                TblCOMISION,{"Cod","Vendedor"},
                                "DATA_COMISION"),
    //generamos una lista con los nombres de las columnas de TblCOMISION
    //lo cargamos en el Buffer para facilitar posteriores usos de la lista
    CamposTblCOMISION=List.Buffer(Table.ColumnNames(TblCOMISION)),

    //Incorporamos, por facilidad, una columna tipo Índice
    AñadeId=Table.AddIndexColumn(Origen,"Id",0,1),

    //Creamos una nueva columna, a partir de la generada con Table.AddJoinColumn
    //donde segmentamos aquellas Tablas con varias filas,
    // de los que solo contienen una fila de información (los que nos interesan...)
    //además, en los casos afirmativos (i.e., con datos), convertimos la tabla en Record
    AddColumnaExpandida = Table.AddColumn(AñadeId, 
        "AddCol_Expande", 
        each 
            if Table.RowCount([DATA_COMISION])>1 then 
                null 
            else
                Origen[DATA_COMISION]{[Id]}{0}),

    //Y preparamos un proceso para recorrer todas las columnas de nuestra tabla
    IncorporoColumnas=
        let
        origen = AddColumnaExpandida,

        //contamos el número de elementos/columnas de la lista
        NumCols = List.Count( CamposTblCOMISION ),

        //creamos una función recursiva
        AddColumns = ( tabla as table, contador as number ) as table =>
            let
                //agregamos a una tabla con datos vacíos en este caso (each null)
                //con el nombre tomado de nuestra lista
                AgregoCol = Table.AddColumn( tabla, 
                                    CamposTblCOMISION{contador}, 
                                    each if [AddCol_Expande]=null then 
                                        null 
                                    else 
                                        Record.Field(Origen[DATA_COMISION]{[Id]}{0}, CamposTblCOMISION{contador}) ),
                //montamos el bucle y salida de este
                //mientras que el contador no supere el número de columnas a incorporar
                //OJO!!, listas en base 0
                ColAgregada = if ( contador < NumCols ) then @AddColumns( AgregoCol, contador + 1 ) else tabla
            in
                ColAgregada,

        //para finalmente aplicar nuestra función recursiva sobre la tabla original
        TablaFinal = AddColumns( origen, 0 )
    in
        TablaFinal,
    
    //quitamos las columnas que no necesitamos...
    ColumnasQuitadas = Table.RemoveColumns(IncorporoColumnas,{"Id", "AddCol_Expande", "DATA_COMISION"})
in 
    ColumnasQuitadas

Observamos como el número de filas coincide con las iniciales de la 'TblALT_ED', es decir, no existen duplicidades...
Además, en aquellos casos en los que se hubiera producido la duplicidad, con nuestro código, hemos logrado resaltarlos dejándolos vacíos...

Otro aspecto importante ha sido el uso de una función recursiva (lee algo más sobre esto), que nos ha permitido recorrer cada columna de nuestra tabla en busca de las que se debían expandir...

Y por finalizar la entrada de hoy, os dejo una alternativa al código anterior, donde en lugar de dejar vacío el registro (para aquellos casos con duplicidades en los items), retornamos la media aritmética del dato:

let
    Origen = Table.AddJoinColumn(TblALT_ED,{"Producto","Comercial"},
                                TblCOMISION,{"Cod","Vendedor"},
                                "DATA_COMISION"),

    CamposTblCOMISION=List.Buffer(Table.ColumnNames(TblCOMISION)),

    AñadeId=Table.AddIndexColumn(Origen,"Id",0,1),

    //Aquí generamos la nueva columna donde, a partir de la creada con AddJoinColumn
    //añadimos la tabla resultante de la join
    //o la tabla agrupada con un cálculo de Media
    AddColumnaExpandida = Table.AddColumn(AñadeId, 
        "AddCol_Expande", 
        each 
            if Table.RowCount([DATA_COMISION])>1 then 
                Table.Group(Origen[DATA_COMISION]{[Id]},
                        {"Cod","Vendedor"}, 
                        {{"Comisión", each List.Average([Comisión]), type nullable number}}){0} 
            else
                Origen[DATA_COMISION]{[Id]}{0}),


    IncorporoColumnas=
        let
        origen2 = AddColumnaExpandida,

        NumCols = List.Count( CamposTblCOMISION ),

        AddColumns = ( tabla as table, contador as number ) as table =>
            let
                AgregoCol = Table.AddColumn( tabla, 
                                    CamposTblCOMISION{contador}, 
                                    each 
                                        Record.Field(tabla[AddCol_Expande]{[Id]}, CamposTblCOMISION{contador}) ),

                ColAgregada = if ( contador < NumCols ) then @AddColumns( AgregoCol, contador + 1 ) else tabla
            in
                ColAgregada,

        TablaFinal = AddColumns( origen2, 0 )
    in
        TablaFinal,


    ColumnasQuitadas = Table.RemoveColumns(IncorporoColumnas,{"Id", "AddCol_Expande", "DATA_COMISION"})
in 
    ColumnasQuitadas

Compara ambas consultas resultantes para aquellas filas con el problema de duplicidades...
Muy interesante para dar una respuesta al problema de tablas con repetidos.

Seguir leyendo...

Power Query: Table.AddJoinColumn alternativas a Table.NestedJoin

Días atrás comenté algunas alternativas (unas mejores, otras no tan buenas) al clásico Table.NestedJoin obtenido con el asistente de Combinar consultas.
Puedes leer este par de artículos para refrescarte:
Table.SelectRows entre tablas
Table.Join versus Table.NestedJoin

Hablaremos hoy de otra gran desconocida:
Table.AddJoinColumn(table1 as table, key1 as any, table2 as function, key2 as any, newColumnName as text) as table
Que resumiendo combina las filas de las dos tablas indicada, basándose en la igualdad de los valores de las columnas de clave que seleccionamos key1 (para table1) y key2 (para table2).
Los resultados se introducen en una nueva columna de la primera tabla.
Esta función M se comporta de forma similar a Table.Join con un JoinKind de LeftOuter, salvo que los resultados de la combinación se muestran de forma anidada en lugar de sin formato.

Teoría aparte, veamos su uso con un ejemplo múltiple...
Partiremos desde las cinco tablas que teníamos en los post anteriores:
TblDATOS, TBLPRECIO, TblREGION, TblDESCUENTO y TblCOMISION

Obviamente empezamos cargando todas las tablas de nuestra hoja de cálculo al editor de consultas de Power Query, solo como conexión... como siempre.
A continuación abriremos una Consulta en blanco donde insertaremos el siguiente código M:

let
    Origen = TblDATOS,

    //Table.AddJoinColumn(table1 as table, key1 as any, table2 as function, key2 as any, newColumnName as text) as table  

    //realizamos la unión entre Producto y Artículo de la TblDATOS y TblPRECIOS respectivamente
    //y llevamos el resultado de la unión, en modo de tabla, a la nueva columna 'DATA_PRECIOS'
    AddJoinColumn_PZ = Table.AddJoinColumn(Origen,{"Producto"},TblPRECIOS,{"Artículo"},"DATA_PRECIOS"),
    Expande_DATA_PRECIOS = Table.ExpandTableColumn(AddJoinColumn_PZ, "DATA_PRECIOS", {"Precio"}, {"Precio"}),

    //Idem o similar al anterior
    AddJoinColumn_REGION = Table.AddJoinColumn(Expande_DATA_PRECIOS,{"País"},TblREGION,{"Country"},"DATA_REGION"),
    Expande_DATA_REGION = Table.ExpandTableColumn(AddJoinColumn_REGION, "DATA_REGION", {"Region", "UE_Y_N"}, {"Region", "UE_Y_N"}),

    //realizamos la unión entre Producto+Comercial y Cod+Vendedor de la TblDATOS y TblCOMISION respectivamente
    //y llevamos el resultado de la unión, en modo de tabla, a la nueva columna 'DATA_PRECIOS'
    AddJoinColumn_COMISION = Table.AddJoinColumn(Expande_DATA_REGION,{"Producto","Comercial"},TblCOMISION,{"Cod","Vendedor"},"DATA_COMISION"),
    Expande_DATA_COMISION = Table.ExpandTableColumn(AddJoinColumn_COMISION, "DATA_COMISION", {"Comisión"}, {"Comisión"}),

    //Idema al primero
    AddJoinColumn_DCTO = Table.AddJoinColumn(Expande_DATA_COMISION,{"Comercial"},TblDESCUENTO,{"Comerciales"},"DATA_DCTO"),
    Expande_DATA_DCTO = Table.ExpandTableColumn(AddJoinColumn_DCTO, "DATA_DCTO", {"Dirección","Descuento"}, {"Dirección","Descuento"})

    //después de cada columna añadida, procedemos a expandir dicha columna....
in
    Expande_DATA_DCTO

Aceptamos y Cerramos y cargamos para devolver la tabla resultante a nuestra hoja de cálculo...
Por supuesto el resultado es el mismo que con el resto de técnicas aplicadas...

Con un proceso, aunque no lo parezca, similar al del empleo de Table.SelectRows entre tablas... empleando esta función ganamos en flexibilidad, rapidez y facilidad de construcción.
Ventajas a tener en cuenta...

Seguir leyendo...

Power Query: Función con parámetros opcionales

Siguiendo con la entrada anterior donde expuse un par de formas para modifcar una dato concreto (fila - columna) dentro de una consulta de Power Query... Hoy convertiremos ese proceso es una función M personalizada, pero con parámetros opcionales !!
Revisa el artículo anterior, donde se explica el proceso de modificación de una 'celda concreta' en una consulta.

Partimos de nuestra ya conocida tabla del post comentada, con unos campos:Año, País, Comercial, Producto y Unidades; Tabla que se ha cargado al editor de consultas de Power Query solo como conexión.

Para crear nuestra función M personalizada te recomiendo leas primero este interesante post, donde se explican las metatags de documentación de una función M.

Igualmente es importante que conozcas estas funciones de Expression:
Expression.Evaluate
Expression.Identifier

Con esos conceptos leídos, podemos acceder al editor de consultas de Power Query y añadir una Consulta en blanco donde escribiremos el siguiente código M:

let 
TablaConDatos=
    //Trabajaremos sobre la Tabla de la hoja de cálculo cargada
    let
        Origen = Excel.CurrentWorkbook(){[Name="TblDATOS"]}[Content]
    in
        Origen,

//Creamos una función personalizada que contiene entre 3-7 parámetros
// tres obligatorios + cuatro opcionales
ModificaFila=
    (NumFila as number,

    Campo1 as text,
    ValorNuevo1 as any, 

    optional Campo2 as text, 
    optional ValorNuevo2 as any,

    optional Campo3 as text, 
    optional ValorNuevo3 as any ) as table=>

let
    //definimos los ámbitos/contextos donde podremos trabajar con las funciones Expression.Evaluate y .Identifier
    AmbitoVariables = 
            [ValorNuevo1I=ValorNuevo1, 
            ValorNuevo2I=ValorNuevo2, 
            ValorNuevo3I=ValorNuevo3],
    //incluímos #shared para englobar la totalidad de funciones, secciones, etc...
    AmbitoVariables_Globals = Record.Combine({AmbitoVariables, #shared}),

    //Componemos, de manera dinámica, y según los argumentos rellenados
    //el Record que permite modificar un dato de una fila-columna en concreto

    //en caso que se relleno o no el Campo2
    CampoDOS = if Campo2=null then
                {TablaConDatos{NumFila} & 
                    Expression.Evaluate("[" & Text.From(Campo1) & "=" & Expression.Identifier("ValorNuevo1I") & "]", AmbitoVariables_Globals)}
            else
                {TablaConDatos{NumFila} & 
                    Expression.Evaluate("[" & Text.From(Campo1) & "=" & Expression.Identifier("ValorNuevo1I") & "]", AmbitoVariables_Globals) & 
                    Expression.Evaluate("[" & Text.From(Campo2) & "=" & Expression.Identifier("ValorNuevo2I")  & "]",AmbitoVariables_Globals)},
    
    //en caso que se relleno o no el Campo3
    CampoTRES = if Campo3=null then
                CampoDOS
            else
                {TablaConDatos{NumFila} & 
                    Expression.Evaluate("[" & Text.From(Campo1) & "=" & Expression.Identifier("ValorNuevo1I") & "]", AmbitoVariables_Globals) & 
                    Expression.Evaluate("[" & Text.From(Campo2) & "=" & Expression.Identifier("ValorNuevo2I")  & "]",AmbitoVariables_Globals) &
                    Expression.Evaluate("[" & Text.From(Campo3) & "=" & Expression.Identifier("ValorNuevo3I")  & "]",AmbitoVariables_Globals)},

    // Acción donde primero Eliminamos la fila 2 (recuerda que estamos en base 0 !!)
    //para seguidamente Insertar una nueva fila en el lugar de la anterior
    // con un registro compuesto por los nuevos valores
    // que modifican los previos existentes
    ValorModificado = Table.InsertRows(
        Table.RemoveRows(TablaConDatos,NumFila,1),   //Elimina la fila 2
        NumFila,          //insertamos una nueva fila en la posición 2
        //el valor de la fila compuesto por el siguiente Record
        CampoTRES)
in 
    ValorModificado,
        
//Configuramos Valores Permitidos para algunos campos...
ValoresPermitidos =
    let
        //Lista con los nombres de los campos de la Tabla 'original'
        //que serán los valores permitidos al rellenar: Campo1, Campo2 o Campo3
        optCampos123 = Table.ColumnNames(TablaConDatos),

        //Asignamos características a los tipos de campo
        CamposParamType = type text 
            meta
            [Documentation.FieldCaption = "Selecciona un campo",
            Documentation.AllowedValues = optCampos123],
        ValoresParamType = type any 
            meta
            [Documentation.FieldCaption = "Indica el nuevo valor del registro"],

        //redefinimos los tipos de datos que admiten nuestros parámetros
        NuevosTipos = type function (
            NumFila as number,
            Campo1 as CamposParamType,
            ValorNuevo1 as ValoresParamType,
            optional Campo2 as CamposParamType,
            optional ValorNuevo2 as ValoresParamType,
            optional Campo3 as CamposParamType,
            optional ValorNuevo3 as ValoresParamType)
        as table,
        
        ReemplazamosTiposFuncion = Value.ReplaceType(ModificaFila, NuevosTipos)
    in
        ReemplazamosTiposFuncion

in 
    ValoresPermitidos

El resultado sería:

Si invocamos nuestra función bien directamente, bien desde el paso intermedio de alguna consulta, veríamos el resultado del cambio/modificación...
Por ejemplo:

Hemos indicado que deseamos modificar de la fila 3 (ojo!!, trabajamos en base cero) el dato de la columna de Año y País por nuevos valores 2021 y España...Dejando los argumentos del tercer campo vacíos, i.e., sin cambios.

Seguir leyendo...

Power Query: Modificar un dato por fila y columna en la consulta

El artículo de hoy creo es de lo más interesante de la seríe de posts publicados sobre Power Query.
Ya que se trata de uno de los temas más preguntados en multitud de foros: cambiar el valor de un dato en particular, de una fila y columna concreta dentro de nuestra consulta.

Sí, seguro que has leído mucho sobre el tema y en la mayoría de los casos se nos dirige a una solución condicionada empleando Table.ReplaceValue... una buena solución, sin duda (puedes leer algo más en este otro post).

La parte negativa de este tip ode procesos es que necesitamos el apoyo de alguna condición, debiendo asegurarnos si fuera el caso de la unicidad del elemento...

Mi solución para evitar este problema es emplear Records, lo que nos aportará la flexibilidad que necesitamos...
Veamos cómo operar con este caso.
Cargaremos como siempre una tabla al editor de consultas de power Query (en mi caso solo como conexión).
Y a continuación abriremos el Editor avanzado donde escribiremos el siguiente código M:

let
    Origen = Excel.CurrentWorkbook(){[Name="TblDATOS"]}[Content],

    // Acción donde primero Eliminamos la fila 2 (recuerda que estamos en base 0 !!)
    //para seguidamente Insertar una nueva fila en el lugar de la anterior
    // con un registro compuesto por los nuevos valores
    // que modifican los previos existentes
    ValorModificado = Table.InsertRows(
        Table.RemoveRows(Origen,2,1),   //Elimina la fila 2
        2,          //insertamos una nueva fila en la posición 2
        //el valor de la fila compuesto por el siguiente Record
        ({Origen{2} & ([País="XXX"] & [Año=2021] &  [Unidades=Origen{2}[Unidades]*100])}))
in
    ValorModificado

Cuando la tabla original era:

Creo muy interesante la forma en que hemos modificado ciertos valores de esa fila de nuestra Tabla (tres en mi ejemplo... pero sin límite al respecto).
En primer lugar recuperamos la fila entera sobre la que estamos interesados... sin condiciones de ningún tipo:
Origen{2}, lo que nos devuelve la fila completa en forma de Record

Y ahora, a partir de ese Record, 'añadiremos' nuevos campos usando el operador de unión &:
{Origen{2} & ([País="XXX"] & [Año=2021] & [Unidades=Origen{2}[Unidades]*100])}
Mencionar que aunque esta técnica está pensada para añadir nuevos campos, cuando el campo nuevo añadido ya existe en el Record de origen, se produce una sustitución del valor de ese campo...esto es, a todos los efectos estamos cambiando una valor por otro!!
Fíjate que el tipo de dato nuevo puede ser de cualquier tipo: número, texto o incluso calculado a partir del mismo registro de partida :OO

Con el nuevo Record modificado ya creado, podemos hacer uso de dos funciones M muy sencillas y seguramente conocidas:
Table.RemoveRows(table as table, offset as number, optional count as nullable number) as table
que nos permite eliminar filas seguidas de una tabla... la posición de la fila a eliminar la indicamos en el segundo argumento (offset as number).
Y la otra función necesaria es:
Table.InsertRows(table as table, offset as number, rows as list) as table
la cual retorna una tabla con la/s filas insertada/s en nuestra tabla en la posición determinada, argumetno offset.
Cada columna de la fila que se va a insertar debe coincidir con los tipos de columna de la tabla !!! (no será un problema inicialmente para nuestro ejemplo)..

Combinando todo en la secuencia correcta, en una línea de código M hemos conseguido modificar o cambiar valores concretos de una fila y columna de nuestra consulta!.

Alternativamente, en lugar del método anterior para generar el nuevo Record, podemos usar otra función M:
Record.Combine(records as list) as record
función pensada para combina los registros en el elemento records determinado...
Pero curiosamente, realizando unas pruebas de otro tema, me percaté que ocurre lo mismo que en el método anterior...
Esto es, al intentar combinar el Record origen con los nuevos valores, los últimos modifican a los primeros:

let
    Origen = Excel.CurrentWorkbook(){[Name="TblDATOS"]}[Content],

    // Acción donde primero Eliminamos la fila 2 (recuerda que estamos en base 0 !!)
    //para seguidamente Insertar una nueva fila en el lugar de la anterior
    // con un registro compuesto por los nuevos valores
    // que modifican los previos existentes
    ValorModificado = Table.InsertRows(
        Table.RemoveRows(Origen,2,1),   //Elimina la fila 2
        2,          //insertamos una nueva fila en la posición 2
        //el valor de la fila compuesto por el siguiente Record
        {Record.Combine({ Origen{2}, [País="XXX"], [Año=2021],  [Unidades=Origen{2}[Unidades]*100] }) } )
in
    ValorModificado

LLegando nuevamente al resultado esperado!!

Un par de métodos que seguro te serán de muchísima utilidad para preparar la carga de tus datos ;-)

Seguir leyendo...