Resolver tipos de cambio faltantes con Power Query en LuckyTemplates

En este tutorial, aprenderá a usar Power Query para realizar conversiones de divisas en LuckyTemplates. Puedes ver el video completo de este tutorial en la parte inferior de este blog.

El método discutido en este blog lo ayudará a resolver problemas en su tabla de tasas de cambio utilizando la última tasa disponible.

Comprenderá cómo resolver las cosas cuando la tabla de tipos de cambio no tenga un tipo de cambio todos los días.

Tabla de contenido

• Identificación de datos faltantes
• Separar diferentes monedas
• Tasas de llenado que faltan
• Fusión de todas las monedas
• Creación de relaciones en el modelo de datos
• Comparación de los métodos DAX y Power Query
• Conclusión

Identificación de datos faltantes

Este problema puede ocurrir si la tabla de fechas no tiene datos para vacaciones o fines de semana.

Si no tiene una tarifa en ningún día y no la tiene en cuenta, las métricas como Ventas totales no se convertirán.

Si echa un vistazo a esta tabla de tasas de cambio, tiene las tasas en euros para enero de 2016.

Pero observe que faltan los días 27 y 28 y no tienen tarifa. Entonces, si vas a la matriz, puedes ver que no hay tarifas para 27 y 28.

La medida Tipo de cambio con faltante es simplemente una suma de la tabla Tasas de cambio de hecho.

El total de 26.6983 en la tabla no tiene sentido. Es solo la suma de todos los tipos de cambio por encima que no necesita sumar porque son lo que son en el día actual.

Ahora, esto se puede solucionar en Power Query porque se trata más de un problema de modelado de datos que implica consultas y agregaciones. Esto también se puede hacer con DAX, pero es más fácil y rápido con Power Query en LuckyTemplates.

Entonces, haga clic en Transformar datos y luego abra la ventana de Power Query.

Esta es la tabla con datos faltantes. También puede ver en esta tabla de tipos de cambio que contiene euros, libras y dólares.

Y luego esta es la mesa final que tiene valores el 27 y 28 de enero. Esta es la tabla o salida que debe tener.

Importe sus datos de cualquier fuente de la que los obtenga y simplemente cambie el tipo de datos.

Separar diferentes monedas

Luego, si tiene más de una moneda, es importante separarlas y agruparlas por su ticker.

Cuando los agrupe, asegúrese de agruparlos en Todas las filas para que, si hace clic en Euros, por ejemplo, solo se muestren los tipos de cambio en euros.

También puede ver que faltan días aquí, así como la moneda de la libra.

Hay algo llamado función personalizada de invocación en LuckyTemplates. Esta es una función con FillMissingRates.

Si hace clic en Euro, verá una tabla completa que contiene los datos faltantes que vio en la tabla expandida. Está ordenado de las tarifas más antiguas a las más nuevas.

Averigüe qué están haciendo las funciones personalizadas. Si hace clic en el ícono de ajustes cerca de Invocar función personalizada, puede ver el nombre de la columna y la fuente que está extrayendo la columna Todos.

Para hacer eso, comience con una tabla básica. Realice las transformaciones, obtenga el resultado que desea y luego utilícelo para construir la función. Tiene más sentido que tratar de hacerlo en la tabla donde tienes todas las demás subtablas.

Por ejemplo, si quiere centrarse en el euro, fíltrelo hasta el teletipo.

Para averiguar qué días faltan, combínelos con su tabla de fechas.

Aquí hay una tabla de divisas filtrada básica y una tabla DimDate:

Para el tipo de unión, puede usar Full Outer para traer todas las filas de ambas tablas. Esto le muestra lo que falta y las tarifas que coinciden.

Cuando lo combine, le dará una tabla completa como subtabla.

Después de eso, ordene las filas y luego expanda DimDate para incluir solo la columna Fecha.

Ahora, puedes ver que todo está emparejado. Y cuando ordena las filas de más antiguas a más nuevas, también puede ver los dos días que faltan con valores nulos.

Tasas de llenado que faltan

Es importante que estén en este orden porque va a completar todos los valores nulos en todas las columnas excepto Fecha.1.

Después de completarlo, puede ver que los valores nulos han cambiado a euros y la última tarifa disponible.

A continuación, elimine las columnas innecesarias como la columna Fecha de la tabla.

La columna Fecha.1 ya tiene las fechas, por lo que la columna Fecha no es necesaria. Luego, establezca la Tasa cruzada en Número decimal.

Por último, reordene y cambie el nombre de las columnas y asegúrese de que sus tipos de datos estén configurados.

Ahora tiene una tabla completa de tipos de cambio.

Sin embargo, esto solo funciona para esta tabla. Debe encontrar una manera de aplicar la misma lógica a una tabla donde se agrupan las monedas.

Fusión de todas las monedas

Si tiene diferentes monedas, es difícil mantener diferentes tablas de tasas. Por lo tanto, debe fusionarlos para crear una tabla de divisas.

Para comenzar, tome su tabla existente y cree una función haciendo clic en Tabla y seleccionando Crear función . A continuación, ingrese un nombre de función.

Después de eso, vaya al Editor avanzado . Elimina los cálculos que no necesitarás. En este caso, deshágase de la fuente, filtre y cambie la sintaxis.

A continuación, asigne un nombre a su parámetro Fuente y luego ingrese Let. Dado que se eliminaron Filas filtradas, cambie el valor dentro de la sintaxis de Consultas combinadas a Origen. Después de eso, elimine la fuente al final de la sintaxis y luego haga clic en Listo.

Ahora, vaya a Agregar columna, haga clic en Invocar función personalizada. Ingrese un nombre de columna como Todos los datos. Para la consulta de función, seleccione la función que se creó anteriormente. Elija Todo como Origen y luego haga clic en Aceptar.

Lo siguiente que debe hacer es eliminar todas las columnas excepto Todos los datos. Luego, expanda esa columna y desmarque la configuración "Usar el nombre de la columna original como prefijo". Después de eso, haga clic en Aceptar y cambie los tipos de columna.

Establezca el tipo de las columnas Fecha, Ticker y Crossrate en Fecha, Texto y Decimal respectivamente. Una vez hecho esto, haga clic en Cerrar y aplicar.

Ahora obtendrá una tabla de tasas de divisas combinadas sin fechas ni tasas faltantes.

Creación de relaciones en el modelo de datos

En su modelo de datos, puede ver la tabla FactCurrencyRates . Ahora, cree una relación, Ticker to Ticker y Date to Date, con esa tabla y las tablas DimCurrencyRates y DimDate.

En su matriz, ingrese la Tasa de moneda No faltante para ver que ahora tiene las tasas para el 27 y el 28, todo hecho en Power Query.

La columna Última tasa de cambio informada también muestra las tasas de los días que faltan. Muestra las mismas tasas y valores. Pero la diferencia entre ellos es que esta columna se crea en DAX.

Esta es la medida y la sintaxis de esa columna. No es tan simple como usar solo la función en Power Query.

Si observa la tabla, verá la columna Ventas convertidas que no faltan. Muestra las ventas que se convierten utilizando la columna Tipo de cambio sin faltantes. La otra columna con ventas convertidas usa la columna que se hizo en DAX.

La función utilizada para obtener las ventas convertidas es . Si observa la medida de esa columna, puede ver SUMX repasando la tabla DimDate.

Comparación de los métodos DAX y Power Query

De hecho, puede comparar las columnas de ventas convertidas que usaron DAX y Power Query en LuckyTemplates.

Para hacer eso, elimine todas las columnas excepto las columnas de ventas convertidas. Luego, inicie el analizador de rendimiento y haga clic en Iniciar grabación.

A continuación, abra DAX Studio. Es una aplicación que le muestra cómo y por qué funcionan las cosas en su modelo. Después de eso, copie la consulta de su matriz y péguela dentro del estudio.

Para la comparación, primero convierta uno de ellos en un comentario para que no interfiera con la otra columna que se está probando.

Y luego, cargue los tiempos del servidor y el plan de consulta. Ya que está comparando rendimientos, primero borre el caché antes de ejecutar la comparación.

Después de ejecutar la prueba, puede ver que la columna que usó DAX obtuvo muchos escaneos y tiene un tiempo total de 71 milisegundos.

Ahora, ejecute la otra columna que usó Power query. Pero primero, convierta la otra columna de la sintaxis en un comentario.

Puede ver que la columna solo se ejecutó durante 25 milisegundos y solo tiene 7 escaneos.

Puede ver claramente cuál funciona mejor y más rápido entre los métodos DAX y Power Query.

Conclusión

Una ventaja de usar el método Power Query en LuckyTemplates es que los datos ya están almacenados. Entonces, la medida puede encontrar la tasa y luego sacarla usando funciones simples.

Otra cosa acerca de este método es que no se ralentiza si el cálculo se vuelve complejo.

Sin embargo, esto solo funciona si los datos que solicita no tienen que convertirse sobre la marcha. Si es así, debe usar DAX.

Mis mejores deseos,