jueves, 9 de julio de 2020

Power Query: Repetir y numerar filas como n de N

Recientemente una lectora preguntaba la posibilidad de pasar de un listado donde se indicaban repeticione spor registro a un listado desglosado con tantas filas repitas y numeradas como se inidicase...
Fácil de entender viendo la imagen siguiente:
Power Query: Repetir y numerar filas como n de N

Vemos como pasamos de una sola fila donde se indican 5 repeticiones a cinco filas numeradas como 1 de 5, 2 de 5, ..., 5 de 5.. y así con cada registro.
La usuaria pedía una macro, pero pensé que Power Query podría ofrecer un resultado igual de válido... y así fue, y así lo voy a explicar. ;-)
Comenzamos cargando la tabla al Editor de consultas de Power Query, desde la Ficha Datos > grupo Obtener y transformar > botón Desde tabla o rango.
Ya en el editor empezaremos a incorporar etapas o pasos.
El primer paso es añadir una columna personalizada con la siguiente función, que emplea Table.Repeat:
Table.Repeat(
  		Table.FromRecords({
        	[Col1=1]
            }),
        [Repetición])

Power Query: Repetir y numerar filas como n de N

El resultado sería:
Power Query: Repetir y numerar filas como n de N
Donde vemos que Table.Repeat devuelve una tabla con tantas filas como se especifiquen en el segundo argumento.. en mi ejemplo el valor dado en el campo 'Repetición' Table.Repeat(table as table, count as number) as table
Este paso es básico, ya que nos permite repetir las filas tal como esperábamos...
En el siguiente paso expandiremos la columna recién añadida, y añadairemos una columna de índice:
Power Query: Repetir y numerar filas como n de N

Para la siguiente etapa debemos recordar cómo obtener un acumulado creciente condicionado con Power Query, para ello revisa esta entrada del blog
Así pues añadimos una nueva columna personalizada:
let Id=[Id],Índice=[Índice] in
        List.Sum(Table.SelectRows(ÍndiceAgregado, each [Índice]<=Índice and [Id]=Id )[Col1])

Power Query: Repetir y numerar filas como n de N

El resultado es un incremental basado en la anterior columna personalizada, obtenida con Table.Repeat, condicionada al campo 'Id' original...
Power Query: Repetir y numerar filas como n de N


Ya tenemos todo lo que necesitamos. La nueva columna 'Acum' que crece por cada registro, y el total para cada registro en el campo 'Repetición' de la tabla original.
Por tanto bastará seleccionar la columna 'Acum' y luego 'Repetición' y desde la ficha Transformar > Columna de texto > Combinar columnas, personalizando el separador por ' de '
Power Query: Repetir y numerar filas como n de N
Finalizamos eliminando columnas que ya no necesitamos... como 'Col1' e 'Índice'. LLegando al resultado mostrado al imagen del post.

El código completo visto desde el editor avanzado sería:
let
    Origen = Excel.CurrentWorkbook(){[Name="TblVentas"]}[Content],
    ColADD = Table.AddColumn(Origen, "Personalizado", each Table.Repeat(
                                                                Table.FromRecords({
                                                                    [Col1=1]
                                                                }),
                                                                [Repetición])),
    Expandido = Table.ExpandTableColumn(ColADD, "Personalizado", {"Col1"}, {"Col1"}),
    ÍndiceAgregado = Table.AddIndexColumn(Expandido, "Índice", 1, 1),

///
RunTotal = Table.AddColumn(ÍndiceAgregado, "Acum", each let Id=[Id],Índice=[Índice] in
        List.Sum(Table.SelectRows(ÍndiceAgregado, each [Índice]<=Índice and [Id]=Id )[Col1])),
    #"Columnas combinadas" = Table.CombineColumns(Table.TransformColumnTypes(RunTotal, {{"Acum", type text}, 
        {"Repetición", type text}}, "es-ES"),
        {"Acum", "Repetición"},
        Combiner.CombineTextByDelimiter(" de ", QuoteStyle.None),"n de N"),
    #"Columnas quitadas" = Table.RemoveColumns(#"Columnas combinadas",{"Col1", "Índice"})
in
    #"Columnas quitadas"

Power Query: Repetir y numerar filas como n de N
Ya podemos Cerrar y cargar en... nuestra consulta en nuestra hoja de cálculo destino.

martes, 7 de julio de 2020

Power Query: Renombrar pasos de la consulta

Hoy hablaremos de una recomendación al trabajar con nuestras consultas de Power Query, en especial cuando éstas tienen muchos pasos intermedios grabados... la recomendación del día es: Renombra los pasos
Imagina una consulta de este estilo
Power Query: Renombrar los pasos de la consulta
Lo normal es que la asignación de los nombres de los pasos se asignen automáticamente en el idioma elegido; esto puedes definirlo en Archivo > Opciones y configuración > Opciones de consulta > GLOBAL > Configuración regional > Pasos de la consulta
Power Query: Renombrar los pasos de la consulta

Lo 'malo' es que los nombres de los pasos son demasiado genéricos y poco descriptivos... de ahí la recomendación de Renombrar los pasos, lo cual facilitará futuras modificaciones y búsquedas dentro de la consulta.

El proceso de cambio es simple, basta hacer clic derecho sobre cada paso y seleccionar la opción Cambiar nombre, repitiendo la acción en todos los pasos que consideremos... lo interesante es que todo el código M generado se ajustará a este cambio...
Power Query: Renombrar los pasos de la consulta


La diferencia que aporta este trabajo es grande... ya que con un simple vistazo, tras renombrar los distintos pasos, podemos localizar etapas o pasos a modificar o consultar muy rapidamente...
Power Query: Renombrar los pasos de la consulta

Se ve en la imagen anterios como los nuevos nombres describen algo mejor la acción realizada, tal como buscábamos...

Obviamente esto se debería realizar a medida vamos grabando los pasos ;-)

viernes, 3 de julio de 2020

Excelforo: XI aniversario y VII Premio Microsoft MVP Excel 2020-2021

ONCE años... no son pocos ;-)
y por séptimo año consecutivo (desde el 2014) he sido premiado por Microsoft con el título MVP (Most Vauable Professional) en Excel... (ver perfil)

Empezar por agradecer el apoyo mostrado durante todo este tiempo, sin vosotros nada hubiera ocurrido.
Agradecer, también, a Microsoft por este nuevo premio... lo tomo como incentivo para seguir difundiendo todo el conocimiento posible de nuestra hoja de cálculo favorita: Excel.
Por otra parte, son más diez años!! ya prestando la mejor formación presencial y elearning(online).
Sin olvidar todos aquellos clientes a los que las horas de consultoría han ahorrado tiempo y dinero.
Visita mi web:
www.excelforo.com



Como en años pasados, en este nuevo aniversario, mostraré algunos datos estadísticos acumulados hasta la fecha; respecto al blog diré que son ya más de 11.350.000 páginas vistas, y un millones de usuarios de todo el mundo (España, México, Colombia, Perú, Chile, Argentina, Ecuador, Estados Unidos.. y un largo etcétera)...
Son ya más de 1.100 entradas publicadas, de casos prácticos propuestos por vosotros, solucionados y explicados; y más de 12.000 comentarios, y desde luego muchísimas horas dedicadas.

No quiero olvidar aventuras ya consolidadas, como el grupo de Facebook de Excel: Microsoft Excel en Español...
https://www.facebook.com/groups/ExcelEspanol/
donde lo especial es que trato de controlar al máximo los comentarios que no aporten nada (como publicidad de cursos, web, grupos, etc...), reduciendo el grupo a contenido de valor añadido.
Contento de alcanzar los 19.000 seguidores!!
ÚNETE.. no lo dudes!


No ha sido un año fácil en lo personal y esto ha repercutido en todos los aspectos de mi trabajo y de mis hobbies... lo que ha repercutido en mi imposibilidad en responder comentarios o correos, por desgracia el tiempo es finito para todos y en estos momentos tengo que gestionar lo que me queda.
Pido perdón por esto.
Como siempre mi agradecimiento hacia todos vosotros.