Diferencia con previo distinto de cero

Hoy un poco de formulación Excel que nos permitirá calcular la diferencia con la fila anterior que sea distinto de cero!!, esto es, solo trabajando con las filas con datos...

Emplearemos algunas de las últimas funciones lanzadas por Microsoft como EXCLUIR o APILARV, junto a las ya clásicas LET o SCAN.
En primer lugar la fórmula (siempre mejorable):

=INDICE(LET(acum;SCAN(0;[Importes];LAMBDA(ac;fila;SI(fila<>0;fila;ac)));
acum_1;EXCLUIR(acum;1);
APILARV(0;EXCLUIR(acum_1-acum;-1)));FILA()-FILA(TblDATOS[#Encabezados]))

En este caso me baso en la idea de reemplazar los elementos de valor cero por el último dato con valor diferente de cero, obteniendo un vector de importes como el siguiente, lo que conseguimos con ayuda de la función SCAN:
SCAN(0;TblDATOS[Importes];LAMBDA(ac;fila;SI(fila<>0;fila;ac)))

Seguidamente, a partir del vector anterior, y con el objetivo de poder restar el dato actual menos el previo, en lugar de usar complejos algortimos, emplearemos la función EXLCLUIR para quitar el primer elemento, y 'correr' el inicio del vector una posición...
=EXCLUIR(G3#;1)
donde G3# es el vector anterior

Completaremos el elemento 'excluido' añadiendo al final del proceso un dato cero con la función APILARV... pero antes, ya podemos restar elemento a elemento ambos vectores.

Ya tenemos casi el resultado... solo falta reubicar posiciones del resultado, lo que conseguimos quitando el último elemento del vector conseguido (que retornaba #N/D) e incorporando un cero en la primera posición (ya que no hay diferencia respecto de nada para el primer elemento):
APILARV(0;EXCLUIR(acum_1-acum;-1))


Para poder incluirlo dentro del campo formulado de nuestra tabla lo maquetamos en el contexto de la función INDICE, tal como se veía en la fórmula del inicio...
Listo. Tenemos nuestra diferencia con el previo distinto de cero.

Seguir leyendo...

DIVIDIRTEXTO y REPETIR para repartir datos

Hoy un ejercicio imaginativo para repartir elementos de un listado por nuestra hoja de cálculo, respetando unas condiciones de número máximo de elementos por cada fila, y además separándolos un número de columnas y de filas entre sí!!.

En la imagen vemos un ejemplo de lo esperado...
Dado un listado de N elementos cualquiera, y unas condiciones de inicio:
-máximo cuatro elementos por cada fila
-separados por dos columnas vacías entre cada elemento
-y por una fila vacía entre cada fila de datos

Hay diferentes formas de llegar al resultado esperado... pero en esta ocasión he optado por una función poco 'apetecible' inicialmente:
REPETIR(texto; núm_de_veces)
Muy simple, verdad?... Repite el texto o caracteres indicados las veces deseadas.

La combinaremos, entre otras, con la nueva función DIVIDIRTEXTO(text;col_delimiter;[row_delimiter];[ignore_empty]; [match_mode]; [pad_with])
La cual divide cadenas de texto mediante delimitadores de columna y fila.

Cadena de texto que compondremos con ayuda de la clásica UNIRCADENAS.

Para una mejor interpretación nos paoyaremos con la función LET.
Insertaremos en la celda D6 la siguiente fórmula:
=LET(MaxCols;$A$1; rng;ndListado; distanciaCols;$A$2; distanciaFilas;$A$3; NumEltos;CONTARA(rng); Pos;DIVIDIRTEXTO(UNIRCADENAS("";1;LET(dato;SECUENCIA(1;NumEltos);SI(RESIDUO(dato;MaxCols)=0;dato&REPETIR(";"&REPETIR("\";MaxCols*distanciaCols);distanciaFilas)&";";dato&"\"&REPETIR("\";distanciaCols))));"\";";"); SI.ERROR(INDICE(rng;Pos);""))

La clave de la fórmula la encontramos en la variable 'Pos' (de posición):
Pos;DIVIDIRTEXTO(UNIRCADENAS("";1;LET(dato;SECUENCIA(1;NumEltos);SI(RESIDUO(dato;MaxCols)=0;dato&REPETIR(";"&REPETIR("\";MaxCols*distanciaCols);distanciaFilas)&";";dato&"\"&REPETIR("\";distanciaCols))));"\";";")
Que ejecutada fuera del contexto de nuestra fórmula nos devolvería la siguiente cadena de texto:
1\\\2\\\3\\\4;\\\\\\\\\\\\;5\\\6\\\7\\\
Y si, has acertado... son los caracteres que en mi sistema se emplean para diferenciar las filas (;) y las columnas (\).
Solo faltaría interpretar esa cadena de texto como una matriz... lo que podemos hacer al aplicar la función DIVIDIRTEXTO.
El último paso aplica el reparto correspondiente de nuestro listado empleando la función INDICE...

La curiosidad del ejercicio es como la combinación de varias funciones REPETIR, en el orden correcto, nos permite generar la cadena de texto, con la secuencia correcta que distribuirá los elementos respetando las separaciones de filas y columnas...
Te animo a descomponer la fórmula ;-)

Seguir leyendo...

VBA: Jagged Arrays o Array de Arrays

Como continuación del artículo anterior, donde mencionaba el uso de Arrays de arrays (jagged arrays-array escalonada o array irregular), pondré hoy un ejemplo del uso de la técnica, que nos permitirá replicar la función M de Power Query: Table.SelectColumns, o la lnueva función de Excel: ELEGIRCOLS

El desarrollo de nuestra función UDF nos llevará a obtener, en un solo rango, juntas, los campos elegidos... empleando el método de construir una Array de arrays (de columnas o campos de una tabla).

Veamos el código de nuestra UDF insertado en un módulo estándar...

Function fxSelectColumns(ByRef arrCampos() As Variant, rngDatos As Range)
On Error GoTo trato_errores

'generamos una array de arrays (cada campo será una array)
10 Dim arrJagged() As Variant
20 x = 0
30 For a = LBound(arrCampos) To UBound(arrCampos)
40    ReDim Preserve arrJagged(x) As Variant
50    col = Application.Match(arrCampos(a, 1), rngDatos.Offset(-1, 0).Resize(1, rngDatos.Columns.Count), 0)
    ''' simplificamos y reducimos dimensiones
60    Dim txt As String, arrTmp As Variant
70    txt = Join(Application.Transpose(rngDatos.Columns(col)), "|")
80    arrTmp = Split(txt, "|")
    
    '''
90    arrJagged(x) = arrTmp       'rngDatos.Columns(col)
    
100    x = x + 1
110 Next a

120 fxSelectColumns = Application.Transpose(arrJagged)

Exit Function
trato_errores:

	Debug.Print Err.Description & "_ línea" & Erl()

End Function

para depurar errores que pudieran aparecer, en cuanto a las dimensiones de las arrays y/o rangos, he numerado las líneas del código, y añadido un control de errores con la función de VBA: Erl

He empleado las mismas técnicas descritas en el post anterior, donde básicamente se identifican y extraen las columnas/campos indicados en el primer argumento de nuestra UDF.
A partir del segundo argumento, será el rango de celdas o Tabla, recuperamos el encabezado para localizar las posiciones de las columnas, y por extensión, los valores asociados a cada campo...
Finalmente devolvemos a la hoja de cálculo la matriz resultante...

Seguir leyendo...

VBA: Dimensiones de una Array

Dedicaremos un poco de tiempo a ver ciertas curiosidades al trabajar con Arrays en VBA, en concreto cómo se cuantifican las dimensiones al crear o cargar una Array.

Resulta curioso que al cargar un vector de valores desde un rango de celdas, se genere una array de dos dimensiones... (a pesar de ser solo una fila o columna de datos), o que al crear una Array de constantes, obtengamos una array unidimensional.

Por otra parte, la numeración o intervalo asignado a estas dimensiones puede variar, empezando desde 0 o desde 1, dependiendo de la procedencia de la Array... si bien, gran parte (NO todas) de las arrays se pueden homogeneizar y forzar para que comiencen por 1 o por 0 (0 to 10, o bien 1 to 11).
Basta definir al inicio del módulo la instrucción Option Base 0 o Option Base 1

Veamos y analicemos el siguiente código insertado en un módulo estándar:

Sub ArrayDimensiones()
'matriz de una dimensión 1 to 6
Dim arrCreada() As Variant
arrCreada = Array("ES", "FR", "DE", "PT", "UK", "IT")

'matriz de 'dos' dimensiones 1 to 6, 1 to 1 !!!
Dim arrDatos() As Variant
arrDatos = Hoja1.Range("C4:C9").Value

'y podemos convertirla en una nueva de 0 to 5
Dim txt As String, arrTmp As Variant
txt = Join(Application.Transpose(arrDatos), "|")
arrTmp = Split(txt, "|")

'Alternativamente, reducimos/quitamos una de las dimensiones...
Dim arrDatosNew(1 To 6) As Variant
For f = 1 To 6
    arrDatosNew(f) = Application.Transpose(Application.Index(arrDatos, f, 1))
Next f

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unimos dos arrays de igual dimensión (vectores unidimensionales)
'y resulte una nueva array de 2-D
Dim arr2D(1 To 6, 1 To 2) As Variant
For f = 1 To 6
    arr2D(f, 1) = arrCreada(f - 1)
    arr2D(f, 2) = arrDatosNew(f)
Next f

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Y si probamos una Array de Arrays???
'OJO, tendremos una matriz Unidimensional,
'que contiene dos matrices Unidimensionales (de cualquier tipo)
Dim arrJagged() As Variant
ReDim arrJagged(1 To 2) As Variant
For a = 1 To 2  'número de arrays
    Select Case a
        Case Is = 1: matriz = arrCreada
        Case Is = 2: matriz = arrTmp
    End Select
    arrJagged(a) = matriz
Next a

Debug.Print arrJagged(2)(3)

End Sub

Fijémonos en la ventana de variables locales cómo se ha dimensionado cada una de las arrays trabajadas...
Recuerda que estamos trabajando, por omisión, en Option base 0.
La primera de ellas:
arrCreada = Array("ES", "FR", "DE", "PT", "UK", "IT")
matriz de constantes, se ha creado con una dimensión, con un intervalo de elementos de 0 to 5
Parece lógico sea así, al ser un listado plano de valores...

La siguiente array:
arrDatos = Hoja1.Range("C4:C9").Value
procede de la carga de un rango de celdas, pero en concreto de un vector vertical (solo una columna de valores), sin embargo, se ha creado con dos dimensines, con intervalos de elementos de (1 to 6, 1 to 1)
Podríamos decir que está pensado para trabajar sobre rangos de varias filas por varias columnas...y que la excepción de ser un vector, la salva definiendo la segunda dimensión como (1 to 1)

Si el rango hubiera tenido dos columnas sería (1 to 2), tres columnas (1 to 3), etc...

Notemos que en este caso, los elementos empienzan por defecto en 1 y no en 0 como el caso anterior.

Vamos a 'transformar' esta última matriz-array, en una UNIdimensional.
Un truco para esto es emplear la combinación de funciones VBA JOIN-SPLIT, junto a la función Transpose:
Dim txt As String, arrTmp As Variant
txt = Join(Application.Transpose(arrDatos), "|")
arrTmp = Split(txt, "|")
El punto interesante es que, efectivamente hemos conseguido reducir el número de dimensiones de dos a una, pero lo curioso es que SPLIT retornará siempre un intervalo de (0 to n). :O

Una alternativa al método anterior sería el siguiente:
'Alternativamente, reducimos/quitamos una de las dimensiones...
Dim arrDatosNew(1 To 6) As Variant
For f = 1 To 6
arrDatosNew(f) = Application.Transpose(Application.Index(arrDatos, f, 1))
Next f
Con esta forma si controlamos en que valor 0 ó 1 empezará el intervalo de nuestra dimensión

Dos tips extra contenidos en la macro... cómo unir arrays.
Ya vimos en el blog algún método para unir rangos de celdas discontinuos (artículo uno o dos).

En este ejemplo veremos métodos alternativos. ;-)
El primero de ellos es algo rústico y demasiado rígido, pero funcional... Consiste en crear una nueva array con las dimensiones correctas, e ir añadiendo los diferentes elementos de las arrays originales:

'Unimos dos arrays de igual dimensión (vectores unidimensionales)
'y resulte una nueva array de 2-D
Dim arr2D(1 To 6, 1 To 2) As Variant
For f = 1 To 6
    arr2D(f, 1) = arrCreada(f - 1)
    arr2D(f, 2) = arrDatosNew(f)
Next f

el resultado una matriz bidimensional compuesta por los valores de las dos primeras arrays...

El segundo consiste en crear una Array de Arrays (en inglés se le conoce a esto como Jagged array matriz irregular o matriz escalonada... no se si de alguna otra forma, yo la bautizaría 'Array Frankestein').
Basicamente consiste en crear una Array con elementos que podrian ser cualquier cosa: rangos, constantes, arrays, ... y de cualquier tamaño o dimensión

Esto hace esta herramienta muy potente, pero a la vez puede resultar compleja de manejar...
Cómo conseguimos esta Array de arrays. Una forma muy básica podría ser:

'Y si probamos una Array de Arrays???
'OJO, tendremos una matriz Unidimensional,
'que contiene dos matrices Unidimensionales (de cualquier tipo)
Dim arrJagged() As Variant
ReDim arrJagged(1 To 2) As Variant
For a = 1 To 2  'número de arrays
    Select Case a
        Case Is = 1: matriz = arrCreada
        Case Is = 2: matriz = arrTmp
    End Select
    arrJagged(a) = matriz
Next a
'para recuperar el tercer elemento de la segunda Array cargada
Debug.Print arrJagged(2)(3)

Quizá este artículo parezca tonto o inutil, pero conocer las dimensiones de las arrays, así como su numeración de intervalos de los elementos, evitará muchos errores en nuestros códigos... y muchos quebraderos de cabeza ;-)

Seguir leyendo...

VBA: Application.ThisCell

Una propiedad poco conocida del objeto Application es esta: ThisCell.
La cual nos devuelve especificamente la celda desde la cual se está ejecutando una UDF (función definida por el usuario), es decir, en qué celda se encuentra la función...

Esta propiedad es muy interesante si la combinamos con controles de error, lo que nos permite identificar en qué celda está fallando nuestra UDF.
Veamos un ejemplo. Creamos nuestra UDF en un módulo estándar de nuestro proyecto VBA:

Function fxMiDivision(dividendo As Long, divisor As Byte)
On Error GoTo trato_error

fxMiDivision = dividendo / divisor

Exit Function
trato_error:
Debug.Print Application.ThisCell.Address & " _ " & Err.Description
End Function

Si trasladamos nuestra función a las celdas C1:C10 de nuestra hoja de cálculo, veremos los siguientes mensajes en la ventana de inmediato.

Sin duda una propiedad fácil de emplear y muy práctica para localizar fallos, entre otras cosas ;-)

Seguir leyendo...

VBA: Erl-Controlando los errores en VBA

Son muchos años ya los que llevo publicando artículos sobre esta herramienta, pero siempre hay un paso más, siempre aprendo algo nuevo.. y me fascina.
Hace unos siete años, por el 2015, publiqué un post sobre el objeto Err que nos aportaba información sobre el error generado.
Veíamos como con Err.Number o Err.Description determinabamos más o menos de qué error se trataba... una gran ayuda para depurar nuestras macros.
Puedes estudiar el listado completo de errores que pueden aparecer en este link:
https://learn.microsoft.com/es-es/office/vba/language/reference/user-interface-help/trappable-errors

En el artículo de hoy vamos a ir un paso más allá... vamos a emplear la función de VBA Erl, la cual nos indicará en qué línea del código se está produciendo el fallo!!.
MUY ÚTIL cuando ejecutamos UDF y el depurador salta sin detenerse en ningún sitio en concreto ;-)

Si bien, lo primero que tenemos que saber y hacer es numerar cada línea o bloque de código en nuestra macro.
Sin esta numeración, la función Erl siempre devolverá el valor 0 (y no nos servirá de nada).

Pongamos un ejemplo sencillo, donde recorremos, con un bucle For each, el rango A1:A10 que contiene diferentes valores entre 200 y 300.
En nuestra macro, definimos un 'cálculo' con un tipo de dato Byte, lo que significa que solo admitirá valores entre 0 y 255...
Esto es, fallará para valores superiores a ese 255.
En un módulo estándar de nuestro libro de trabajo tenemos la siguiente macro:

Sub Gestionando_Errores()
On Error GoTo trato_error

10 Dim dato As Byte    'valores entre 0 y 255
20 For Each celda In Range("A1:A10")
30    dato = celda.Value
40    Debug.Print dato
50 Next celda

Exit Sub
trato_error:
    
    Debug.Print Err.Number & " - " & Err.Description & " - línea - " & Erl()
End Sub

Fíjate como he comenzado a escribir cada línea de código con un número, que será el número de línea asociado y que la función Erl leerá.

Al ejecutar el proceso en la ventana de Inmediato veremos..

:OOO
Al saltar el controlador 'On Error GoTo trato_error', se interpreta el número y descripción del error generado... y con Erl junto al detalle de líneas añadido, idetificamos en qué momento exacto de la macro ha ocurrido!!.

Mágico!.

Claro, para este código sencillo es fácil añadir un número de línea por fila de código...
Pero si nuestro código en VBA tuviera cientos y cientos de líneas, se haría inviable este trabajo...

Aunque he leído que hay programas de terceros que incorporan automáticamente esta numeración de línea, o incluso con algunas macros dentro del mismo Excel se podrían autogenerar, creo que hay una salida más simple y práctica.
Numerar por bloques de código dentro de nuestra macro.

No creo ser el único que al programar vaya secuenciando diferentes acciones, por distintos bloques... lo que reduciría, en principio la cantidad de números de líneas a añadir... en un paso siguiente, si procede, añadiríamos la numeración por líneas, pero solo de ese bloque!!.

Pongamos un segundo ejemplo. Tenemos el siguiente código:

Sub Gestionando_Errores()
On Error GoTo trato_error

Dim arrValores As Variant   'definición de una matriz
Dim dato As Byte    'valores entre 0 y 255

x = 0
10 For Each celda In Range("A1:A10")
  dato = celda.Value
  ReDim Preserve arrValores(x) As Variant
  arrValores(x) = dato
  x = x + 1
Next celda

100 For i = LBound(arrValores) To UBound(arrValores)
    Cells(1, "B").Value = arrValores(i)/2
Next i

Exit Sub
trato_error:
    
    Debug.Print Err.Number & " - " & Err.Description & " - línea - " & Erl()
End Sub

Observa que solo he numerado el inicio de mis dos bloques de programación.
Con el número de línea 10 al bucle For each que recorre el rango A1:A10 e intenta cargas dichos valores a la matriz.
Y con el número de línea 100 el bucle For que trasladaría a la columna B la operación dada (dividir el dato cargado entre dos)

Nuestra macro contiene un primer error de compilación a la hora de definir la matriz arrValores.
Debería ser:
Dim arrValores() As Variant
en lugar de
Dim arrValores As Variant
Al ejecutar la macro, veríamos en la ventana de Inmediato el siguiente mensaje:
13 - No coinciden los tipos - línea - 10

Una vez identificado el bloque, en una segunda etapa, podemos renumerar las demás líneas para ganar en precisión.. así:

Sub Gestionando_Errores()
On Error GoTo trato_error

Dim arrValores As Variant   'definición de una matriz
Dim dato As Byte    'valores entre 0 y 255

x = 0
10 For Each celda In Range("A1:A10")
11  dato = celda.Value
12  ReDim Preserve arrValores(x) As Variant
13  arrValores(x) = dato
14  x = x + 1
15 Next celda

100 For i = LBound(arrValores) To UBound(arrValores)
    Cells(1, "B").Value = arrValores(i) / 2
Next i

Exit Sub
trato_error:
    
    Debug.Print Err.Number & " - " & Err.Description & " - línea - " & Erl()
End Sub

Que al ejecutar en esta segunda ocasión nos lanza el mensaje:
13 - No coinciden los tipos - línea - 12
Esto es, precisando que el error procede más concretamente de la línea 12...

Depurando así, poco a poco, línea a línea, nuestros códigos de VBA.

Una recomendación general leída en diferente documentación es que el añadir estas numeraciones de línea ralentiza los tiempos de ejecución de las macros, personalmente no he verificado estos retrasos, pero tiene sentido ya que hay más 'código' que leer ;-)

Seguir leyendo...

Power Query: Añadir filas vacías

Hace tiempo publiqué un artículo similar donde con un método alternativo conseguíamos insertar filas en una tabla, con valores controlados.
Aquel método consistía en generar un registro de valores nulos, para luego combinarlo con otro registro de valores determinados.

Hoy veremos una alternativa para, primero, generar un registro de nulos, y, añadirlos después de cada agrupación de filas por Categoría (uno de los campos de la tabla).

Digamos que pretendemos tener 'separados' cada grupo de Categoría.

Como siempre, el primer paso consistirá en Cargar la tabla de datos 'Tabla1' al Editor de Power Query, donde modificaremos la consulta hasta que nos quede con el siguiente código:

let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    //Obtenemos el listado de nombres de columnas
    NombresCols=Table.ColumnNames(Origen),
    //y su recuento
    NumCols=List.Count(NombresCols),

    //Creamos el registro de nulos
    Registro_NULO= Record.FromList(
                List.Generate(
                    () => [x = null, y = 1],
                    each [y] <= NumCols,
                    each [x = null, y=[y]+1 ],
                    each [x]), 
        NombresCols),

    //Agrupamos por Campo1 para poder segregar las distintas Categorias
    Agrupo_Categoria = Table.Group(Origen, {"Categoría"}, {{"CAMPOS_GROUPPED", each _, type table }}),
    
    //Inserto un registro al final de cada agrupación
    Añado_RegistroNulos = Table.AddColumn(Agrupo_Categoria, 
                                    "AddRecordNull", 
                                    each Table.InsertRows([CAMPOS_GROUPPED],
                                                        Table.RowCount([CAMPOS_GROUPPED]),
                                                        {Registro_NULO})),
    OtrasColumnasQuitadas = Table.SelectColumns(Añado_RegistroNulos,{"AddRecordNull"}),
    Expandimos = Table.ExpandTableColumn(OtrasColumnasQuitadas, 
                        "AddRecordNull", 
                        NombresCols, 
                        NombresCols)
in
    Expandimos

La línea de la consulta creada es:
- listar y contar el nombre y número de columnas en la tabla origen.
- fabricar un registro de nulos, en esta ocasión con la función List.Generate
- agrupar por la columna deseada, de 'Categoría', que nos permite juntar todos las filas de la misma categoria en un solo 'paquete'
- a cada agrupación resultante, con la función Table.InsertRows añadimos el registro de valores nulos recién creado.
- Finalmente eliminaremos campos sobrantes y expandiremos las nuevas tablas, ya con la nueva fila de nulos.!!

La diferencia de métodos para lograr esos registros nulos nos abre la puerta a nuevas alternativas.
El primer método:

Record.FromList(List.Repeat({""},Table.ColumnCount(TipoCambiado)),Table.ColumnNames(TipoCambiado)

Una forma muy simple y directa (e imaginativa, diría yo) de lograr esos nulos...
Frente al más ortodoxo método con List.Generate:

Record.FromList(
                List.Generate(
                    () => [x = null, y = 1],
                    each [y] <= NumCols,
                    each [x = null, y=[y]+1 ],
                    each [x]), 
        NombresCols)

donde se hace necesario un control de incremento y salida hasta completar las columnas necesarias con valores Null.

Siempre me ha parecido interesante tener opciones frente a un mismo problema ;-)

Seguir leyendo...

Power Query: Ordenar por aparición y fila

Me planteaban hace unos días la posibilidad de obtener una numeración ordinal (1,2,3,...) para los distintos valores de cada fila, de acuerdo a su aparción de izquierda a derecha de ciertos campos..

Veremos un par de métodos:
-uno clásico empleando Grupos, columnas de Índice y las opciones de Dinamizar y Anular dinamización
-un segundo empleando Records appoyándonos en la función List.Generate, algo más elegante.

Lo primero será cargar nuestra tabla con ventas ('TblVENTAS') al editor de Power Query, y con la tabla cargada procederemos a generar una segunda consulta como referencia de la tabla subida.
Con la consulta editada, escribimos el siguiente código...

let
    Origen = TblVENTAS,
    //obtenemos los encabezados de la tabla
    Encab=Table.ColumnNames(Origen),
    //ya que a partir de ellos obtendremos automáticamente aquellas columnas que tengan un valor numérico
    //i.e., las columnas con 'años', que serán sobre las que trabajaremos.
    lstFechas=List.Select(Encab, each Value.Is(Value.FromText(_), type number)),
    
    //añadimos un registro compuesto de un dato por cada año
    //autonumerado de izquierda a derecha por orden de aparición
    //siempre que sea distinto de cero
    AddRecord  = Table.AddColumn( Origen, 
                    "DATOS", 
                    each 
                        let
                        Lst=List.ReplaceValue(Record.ToList(Record.SelectFields(_, lstFechas)), null,0, Replacer.ReplaceValue), 
                        num=List.Count(Lst),
                        Acum=List.Generate(
                            () => 
                            [   x = 1, y=if Number.From(Lst{0})<>0 then {1} else {0}],
                            each [x] <=  num,
                            each [  y = if Number.From(Lst{[x]})<>0 then  [y] & {1} else  [y] & {0} ,
                                    x= [x]+1  ],
                            each if List.Last([y])=0 then 0 else List.Sum([y])   )
                        in
                            Record.FromList(Acum, lstFechas) ),
    //discriminamos campos 'viejos' para evitar conflicto con los nuevos generados
    SelCols=Table.SelectColumns(AddRecord,{"País","DATOS"}),
    //expandimos el REgistro
    ExpandeDATOS = Table.ExpandRecordColumn(SelCols, "DATOS", lstFechas, lstFechas)

in
    ExpandeDATOS

El punto interesante de este primer método es que mediante la función List.Generate hemos podido generar un Record, con dos variables x e y, que mediante ciertos condicionales, devuelven el orden buscado 1,2,3,... para aquellos valores distintos de cero!!.
Recuerda los argumentos de la función:
1- punto de inicio
2- punto/condición de salida
3- punto de incremento o siguiente valor
4- punto a devolver

El valor de la x corresponde al contador de valores, mientras que la y responde al valor incremental buscado.

Con el segundo método solo tenemos que usar el asistente de pasos, y seguirlos en la secuencia correcta:
En una secuencia de ocho pasos tendríamos el siguiente código

let
    Origen = TblVENTAS,
    //seleccionamos las columnas de los años y Reemplazamos Valores
    Reemplazo_0_Null = Table.ReplaceValue(Origen,0,null,Replacer.ReplaceValue,{"2021", "2022", "2023", "2024", "2025"}),
    //Seleccionamos la columna País y anulamos la dinamización de las otras columnas
    Anulacion_dinamizacion = Table.UnpivotOtherColumns(Reemplazo_0_Null, {"País"}, "Atributo", "Valor"),
    //Seleccionamos País y agrupamos
    Agrupo_Pais = Table.Group(Anulacion_dinamizacion, {"País"}, {{"Recuento", each _, type table [País=text, Atributo=text, Valor=number]}}),
    //Añadimos una columna personalizada, donde como fórmula incorporamos la que añade una columna índice
    AddIndice = Table.AddColumn(Agrupo_Pais, "ConINDICE", each Table.AddIndexColumn([Recuento],"Id",1,1)),
    //Nos quedamos solo con esta última columna recién creada
    OtrasColumnasQuitadas = Table.SelectColumns(AddIndice,{"ConINDICE"}),
    //y la expandimos
    Expandimos = Table.ExpandTableColumn(OtrasColumnasQuitadas, "ConINDICE", {"País", "Atributo",  "Id"}, {"País", "Atributo",  "Id"}),
    //dinamizamos la columna de Atributo (que corresponde a los Años)
    ColumnaDinamizada = Table.Pivot(Expandimos, List.Distinct(Expandimos[Atributo]), "Atributo", "Id", List.Sum),
    //y finalmente reordenamos o seleccionamos las columnas en el orden deseado
    SelCols=Table.SelectColumns(ColumnaDinamizada, {"País","2021", "2022", "2023", "2024", "2025"})
in
    SelCols

El resultado es el mismo... ;-)

El sentido de este ejercicio podría ser, sobre todo empleando el primer método, recuperar exclusivamente el primer valor de cada registro (o la/s posiciones que neceistáramos).
Un filtro aplicado en el momento-paso adecuado, nos daría el resultado.

Seguir leyendo...