Una de las cosas que muchos hemos visto al crear queries con Power Query en Excel es encontrarnos con datos que desaparecen al añadir columnas manualmente y dando click al botón de actualizar. Para explicarlo mejor imaginemos el siguiente excenario:
- Creamos un query.
- Cargamos nuestro query a una tabla dentro de una hoja de cálculo de Excel.
- Añadimos nuevas columnas con comentarios o notas en las celdas de estas nuevas columnas.
- Actualizamos nuestro query y notamos que se pierden todos los datos de las celdas que creamos manualmente.
Siendo este un problema que ha existido desde el lanzamiento de Power Query, ha habido varias soluciones implementadas por Microsoft a través de los años, y al final el motivo de que Power Query y Excel no pudieran resolver este problema se debió a un problema de almacenamiento o con bases de datos, más allá de ser causado por Power Query o Excel.
Por lo que en este caso lo que vamos a intentar lograr implicaría una solución dedicada para almacenar datos antes de actualizar la información. Esto es algo que nos vendría bien tener directo en Excel y Power Query, pero por desgracia no hay garantía de que el valor o las fórmulas permanezcan ahí luego de actualizar, a menos que se trate de una tabla de fórmulas.
Conservando datos en columnas nuevas
Aunque existen soluciones a corto plazo que nos pueden ayudar, es mejor contar con un método seguro y firme. Si ya tenemos experiencia con Power Query, entonces nos será más fácil aprender a implementar los flujos de datos para PowerApps.
Antes de empezar, cabe mencionar que este método requiere tener una cuenta de PowerApps y contar con el acceso correcto para poder crear nuestros propios flujos de datos y tener control total de los mismos. Si aun no cuentas con PowerApps, es posible registrarse para una prueba gratis de treinta días siguiendo este enlace.
Si necesitas más información sobre como usar Power Apps puedes checar nuestra introducción a Power Apps aquí.
Una vez tengamos todo listo podremos empezar.
Definiendo nuestra fuente de datos
En este caso estaremos usando una simple hoja de cálculo de Excel con tres campos: ID, Nombre y Apellido.
En esencia, esta es una tabla de contactos, y la idea es añadir columnas adicionales a la tabla, tales como «país de origen» o «fecha de seguimiento».
Creando el flujo de datos y Nueva entidad en PowerApps
Una vez iniciemos sesión en PowerApps iremos a la pestaña «Data», y si aún no hemos creado una Entidad, entraremos a la pestaña “Entities” y crearemos alguna en el ambiente de desarrollo.
Luego a Flujos de Datos y finalmente a «crear un flujo de datos».
Daremos un nombre a nuestro flujo de datos para poder crearlo y se nos abrirá una ventana para elegir nuestra fuente de datos, la cual se verá así:
Daremos un nombre a nuestro flujo de datos para poder crearlo y se nos abrirá una ventana para elegir nuestra fuente de datos, la cual se verá así:
Para este caso nos estaremos conectando a un archivo de Excel, aunque la idea general es que podemos conectarnos a cualquier fuente de datos de nuestra preferencia. Este paso funciona para cualquier fuente de datos y a cierto grado se puede usar el mismo query que hayamos creado e importado aquí como un nuevo flujo de datos.
Dado que en este caso estamos usando el conector de Excel con una puerta, al introducir la fuente de datos la ventana se verá así:
Tras lo cual podremos ver nuestra hoja de cálculo y la tabla que necesitamos.
Para los usuarios de Power Query, todo hasta ahora tendrá una apariencia bastante similar. En este caso no hizo falta hacer muchas transformaciones, ya que todo lo que se busca hacer es combinar los datos de las columnas de «Nombre» y «Apellido» en un campo nuevo llamado «Nombre Completo» y mantener la columna de ID como el identificador único de los registros y acabar por establecer los tipos de datos.
Y aquí viene la parte donde difiere de usar Power Query para Excel.
- En Power Query solo podemos cargar los datos ya sea a una tabla en el libro de trabajo o a un modelo de datos.
- En PowerApps podemos cargar los datos a una nueva entidad, la cual es un tipo específico de objeto para el Modelo de Datos Comunes que, explicado en términos simples, es una tabla que existe en almacenamiento de nube a través del CDM (Common Data Model, por sus siglas en inglés).
Aquí se muestra el cómo establecemos el flujo de datos para que cargue el query a una nueva entidad. Se le asigna nombre a la nueva entidad y se establece el campo clave de la nueva entidad.
Nótese también cómo está marcada la opción de «borrar filas que no sean usadas en la salida del query», lo cual nos será útil más adelante. Tras darle a «Siguiente» podemos establecer cómo se actualizará el flujo de datos. Por defecto está establecido como «Actualizar manualmente», pero lo podemos cambiar para que se actualice en un periodo de nuestra preferencia.
Añadiendo nuevas columnas a nuestra entidad
Ahora que hemos creado una nueva entidad, iremos a la pestaña «Data» y buscaremos la opción de «Entidades» para poder encontrar nuestra entidad.
En este caso, la entidad se llama «clientes» y se verá así:
Aquí, daremos click a «Añadir campo» y podremos crear una nueva columna o campo. En este caso será una nueva columna que se llamará «País».
Ya con esto podremos guardar nuestra entidad.
Añadiendo datos a nuestra nueva columna
En la parte superior veremos un campo que dice «Editar datos en Excel», al cual le daremos click. Esto descargará un archivo que se abrirá en Excel, el cual se verá así:
Daremos click a «Habilitar edición» y, de no tener el add-in de PowerApps Office Store, nos pedirá permisos para poder instalarlo.
Tras haberlo obtenido, nos pedirá que iniciemos sesión. Una vez tengamos todo en orden nos cargará todo de esta forma:
Ahora podremos ver la tabla con nuestros datos y el campo que creamos. También tendremos el add-in de PowerApps Office del lado derecho, en donde podremos hacer cambios, actualizar o incluso filtrar nuestros registros.
Aquí introduciremos valores en el campo de «País» y daremos click en el botón «Publicar» para que los cambios se guarden en la nube:
Mientras se publican los cambios que hayamos hecho nos mostrará varios estados indicando lo que pasa, lo que nos podrá resultar útil.
Probando la solución
Ahora viene la parte importante: asegurarnos de que todo funciona. Recordando nuestro archivo con la fuente original, se veía así:
Y tras cambiarlo luce así:
Nótese cómo añadimos el ID 4 antes del ID 1 y se removió la fila en donde Ramira estaba. Luego seguimos y actualizamos el flujo de datos. Tras haberse actualizado, volvimos al archivo de Excel que fue creado por PowerApps y dimos click a Actualizar desde el panel de PowerApps y esto nos resultó:
Ramira ya no está en la tabla y ahora tanto Mario como Jose aparecen con el país correcto aun ahí. La fila de «Alguien Mas» no tiene ningún dato para el campo de «País», lo cual se debe a que es una nueva fila. Esto quiere decir que funciona.
Más formas de hacer esto aún mejor
Dado que la información es almacenada en una entidad en el CDM, podemos hacer uso de varias herramientas para tomar ventaja de esto, principalmente Power BI y Microsoft Flow. Podemos conectarnos al repositorio central (o sea, la entidad) usando Power BI Desktop para hacer nuevos análisis.
También podemos activar flujos en base a datos que son introducidos o modificados en esos registros. Y estos no son las únicas formas en que podemos interactuar con datos. Podemos incluso crear una aplicación con PowerApps para proveer la mejor UX posible para que nuestros usuarios introduzcan o modifiquen información en conjunto con cómo funcionen nuestros flujos de datos y cómo se siga actualizando la información.
Y ¿Porque Power Query no funciona en este caso?
Power Query es una herramienta de preparación de datos diseñada para extraer, transformar y cargar la información. Opera dentro del motor MashUp para desarrollar los conjuntos de transformaciones que definimos a través de la interfaz de Power Query. La forma en que lo hace es estableciendo una conexión con una fuente de datos de nuestra elección, para luego aplicar las transformaciones que definamos y cargar los datos de salida a dichas transformaciones dentro de una tabla, que en este caso se encuentra dentro de un documento de Excel.
En pocas palabras, estos significan que Power Query no almacena datos y es por eso por lo que al actualizar se recalcula toda la tabla. Esto también significa que Power Query no toma en cuenta las columnas nuevas o los cambios que se pudieron haber hecho a la tabla de salida en Excel, dado que estos datos no están almacenados.
Y antes de terminar, me gustaría poner aquí una imagen que expresa todo lo que siento en este momento.
Cualquier duda nos leemos abajo.