jueves, 19 de noviembre de 2015

Power Query: Construir una función personalizada.

Hoy, en mi serie de artículos sobre esta gran herramienta (Power Query), aprenderemos a crear una función personalizada dentro de Power Query que nos permitirá emplearla en el resto de Consultas de nuestro Libro de trabajo.

Partimos de varias tablas en nuestra hoja de cálculo, deseando que en todas ellas podamos convertir la columna que indiquemos en dos columnas nuevas, separando los textos por el primer separador (en el ejemplo un guión medio '-').

Esa será nuestra función, aquella que separe el texto de la columna que indiquemos en dos nuevas columnas, dividiéndola por el primer separador que encuentre.


Nuestras tablas son:

Power Query: Construir una función personalizada.



Comenzaremos por crear nuestra función personalizada en PowerQuery.
Puesto que será algo muy genérico comenzaremos desde la nada, por ejemplo, iniciando Power Query desde Otros orígenes:
Ficha Power Query > Obtener Datos Externos > De Otros Orígenes > Consulta en Blanco

Power Query: Construir una función personalizada.



Esto nos abrirá el editor de consultas de Power Query, y en la barra de fórmulas escribiremos un valor cualquiera que asemeje los diferentes códigos a tratar.. en mi caso, escribiré:
BBB-000-000
ya que los diferentes elementos a tratar en nuestras tablas responden más o menos a esa estructura: 2 ó 3 grupos de caracteres separados por guiones medios.


Tras escribir ese código de ejemplo haremos clic en el cuerpo de la ventana, lo que nos lleva el valor escrito a la ventana de trabajo.
Si ahora hacemos clic derecho sobre ese elemento, aparecerá una opción:
A lista
que convierte, precisamente, el valor en el inicio de una lista:

Power Query: Construir una función personalizada.



Al convertirlo en Lista se nos ofrece una posibilidad de reconvertirlo en Tabla... mucho más cómodo para nuestro objetivo.

Power Query: Construir una función personalizada.



Aplicaremos ahora los pasos buscados, esto es, separar el dato en dos columnas por el primer separador que encuentre...
Desde el menú Inicio > grupo Transformar > botón Dividir columna > Por delimitador, configuraremos la acción:

Power Query: Construir una función personalizada.



El siguiente paso es el importante, puesto que es ahora cuando convertimos nuestra consulta en una función.
Para ello, en la ventana del Editor de consultas, iremos al menú Inicio > grupo Consultas > botón Editor avanzado y escribiremos lo siguiente:
(Origen)=> let
    //Origen = "BBB-000-000",
    #"Líneas del texto" = Lines.FromText(Origen),
    #"Tabla de lista" = Table.FromList(#"Líneas del texto", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Dividir columna por delimitador" = Table.SplitColumn(#"Tabla de lista","Column1",Splitter.SplitTextByEachDelimiter({"-"}, null, false),{"Column1.1", "Column1.2"}),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Dividir columna por delimitador",{{"Column1.1", type text}, {"Column1.2", type text}})
in
    #"Tipo cambiado"


Fundamental, escribir el texto
(Origen)=>
antes de 'let'; e igualmente desactivar de donde tomamos el dato, es decir, insertar dos barras // antes de Origen = "BBB-000-000", en la segunda línea...


Finalmente, una vez creada nuestra función, la guardamos con un nombre, por ejemplo fxSeparador.
Para ello basta guardar la consulta con ese nombre:

Power Query: Construir una función personalizada.



Ahora Cerramos y cargamos la consulta-función...
La podemos ver en el panel de consultas del Libro:

Power Query: Construir una función personalizada.



Listos... probemos nuestra nueva función en cualquiera de las Tablas dentro del Editor.
Cargaremos la tabla del medio (se llama 'TblDatos2'), como siempre desde la ficha Power Query > grupo Datos de Excel > Desde Tabla.
Se nos abrirá el Editor de consultas, y desde el menú de Agregar Columna > grupo General > botón Agregar columna personalizada, asignaremos un nombre a la nueva columna e insertaremos como valor la función:
=fxSeparador([Tipo])

Power Query: Construir una función personalizada.



En el paso siguiente, una vez generada la nueva columna con nuestra fórmula personalziada, basta con Expandir la columna y mostrar los dos campos, finalizando nuestro trabajo:

Power Query: Construir una función personalizada.



Ya podemos Cargar y Cerrar para devolver los datos ya tratados a la hoja de cálculo, y continuar el proceso con el resto de tablas...

Obviamente el ejemplo mostrado es muy sencillo, pero estas funciones nos facilitarían el trabajo cuando los procesos de conversión-transformación de columnas sea más complejo...

4 comentarios:

  1. Hola Ismael
    Antes de nada, darte las gracias por compartir tus conocimientos y tu tiempo que aclaran muchas dudas
    Ahora mi problema. Tengo algunas consultas de Power Query que hago en casa en Excel 2013 luego las llevo al trabajo que también tiene Excel 2013 pero sin el complemento que no puedo instalar por estar en un ambiente corporativo
    Hay forma de que mis consultas funcionen en el ambiente del trabajo?

    Un saludo

    ResponderEliminar
    Respuestas
    1. Hola,
      como bien dices esa funcionalidad se basa en un complemento (Power query)... para su correcto (y completo) funcionamiento se exige tener instalado dicho complemento
      :(
      Saludos

      Eliminar
  2. Hola estoy tratando de hacer una consulta usando la función SI más O en Powerpoint querq pero a pesar que uso los condicionales correctos no puedo , estoy buscando un valor en varias columnas en Excel esta es la fórmula =SI(O(A2="D509",B2="D509",C2="D509",D2="D509",E2="D509",DE="D509"),"VERDE","ROJO") cómo sería esta fórmula power query se que las declaracione son en inglés y he probado de muchas formas pero nada , porfavor me puedes ayudar maestro!!

    ResponderEliminar
    Respuestas
    1. Hola,
      te recomiendo por simplicidad que emplees el asistente de columna condicional (en el editor de consultas, ficha de Agregar columnas, grupo General, botón Columna condicional...
      podrás añadir tantas condiciones O como necesites al SI condicional
      Saludos!

      Eliminar

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