Resolvendo taxas de câmbio ausentes com o Power Query no LuckyTemplates

Neste tutorial, você aprenderá como usar o Power Query para conversões de moeda no LuckyTemplates. Você pode assistir ao vídeo completo deste tutorial na parte inferior deste blog.

O método discutido neste blog ajudará você a resolver problemas em sua tabela de taxas de câmbio usando a última taxa disponível.

Você entenderá como resolver as coisas quando a tabela de taxas de câmbio não tiver uma taxa de câmbio todos os dias.

Índice

• Identificando dados ausentes
• Separando Moedas Diferentes
• Preenchendo taxas ausentes
• Mesclando todas as moedas
• Criando relacionamentos no modelo de dados
• Comparando os métodos DAX e Power Query
• Conclusão

Identificando dados ausentes

Esse problema pode ocorrer se a tabela Date não tiver dados para feriados ou finais de semana.

Se você não tiver uma taxa em nenhum dia e não a contabilizar, métricas como Total de vendas não serão convertidas.

Se você der uma olhada nesta tabela de taxas de câmbio, ela tem taxas em Euros para janeiro de 2016.

Mas repare que faltam os dias 27 e 28 e não tem taxa. Então, se você for à matriz, verá que não há taxas para 27 e 28.

A medida Currency Rate with Missing é simplesmente uma soma da tabela Fact Currency Rates.

O total de 26,6983 na tabela não tem sentido. É apenas a soma de todas as taxas de câmbio acima dela, que você não precisa somar porque são o que são no dia atual.

Agora, isso pode ser corrigido no Power Query porque é mais um problema de modelagem de dados que envolve consultas e agregação. Isso também pode ser feito com DAX, mas é mais fácil e rápido com o Power Query no LuckyTemplates.

Portanto, clique em Transform Data e abra a janela do Power Query.

Esta é a tabela com dados ausentes. Você também pode ver nesta tabela de taxas de câmbio que contém Euros, Libras e Dólares.

E então esta é a mesa final que tem valores nos dias 27 e 28 de janeiro. Esta é a tabela ou saída que você deve ter.

Importe seus dados de qualquer fonte de onde você os está obtendo e apenas altere o tipo de dados.

Separando Moedas Diferentes

Em seguida, se você tiver mais de uma moeda, é importante separá-las e agrupá-las por seu código.

Ao agrupá-los, certifique-se de agrupá-los em Todas as linhas para que, se você clicar em Euros, por exemplo, ele mostre apenas as taxas de câmbio em Euros.

Você também pode ver que faltam dias aqui, bem como a moeda da libra.

Existe uma coisa chamada Invoke Custom Function no LuckyTemplates. Esta é uma função com FillMissingRates.

Se você clicar em Euro, verá uma tabela completa que contém os dados ausentes que você viu na tabela expandida. É classificado das taxas mais antigas para as mais recentes.

Descubra o que as funções personalizadas estão fazendo. Se você clicar no ícone de engrenagem perto de Invoke Custom Function, poderá ver o nome da coluna e a fonte que está puxando na coluna All.

Para fazer isso, comece com uma tabela básica. Faça as transformações, obtenha o resultado desejado e use-o para criar a função. Faz mais sentido do que tentar fazê-lo na tabela onde você tem todas as outras subtabelas.

Por exemplo, se você quiser se concentrar no Euro, filtre-o até o ticker.

Para descobrir quais dias estão faltando, mescle-os com sua tabela de datas.

Aqui está uma tabela básica de moedas filtrada e uma tabela DimDate:

Para Join Kind, você pode usar Full Outer para trazer todas as linhas de ambas as tabelas. Isso mostra o que está faltando e as taxas correspondentes.

Quando você mesclá-lo, ele fornecerá uma tabela completa como uma subtabela.

Depois disso, classifique as linhas e expanda DimDate para incluir apenas a coluna Date.

Agora, você pode ver que tudo está combinado. E quando você classifica as linhas do mais antigo para o mais recente, também pode ver os dois dias ausentes com valores nulos.

Preenchendo taxas ausentes

É importante que eles estejam nessa ordem porque você preencherá todos os nulos em todas as colunas, exceto Date.1.

Depois de preenchê-lo, você pode ver que os valores nulos mudaram para Euro e a última taxa disponível.

Em seguida, remova as colunas desnecessárias como a coluna Data da tabela.

A coluna Date.1 já possui as datas, então a coluna Date não é necessária. Em seguida, defina o Crossrate como Decimal Number.

Por fim, reordene e renomeie as colunas e certifique-se de que seus tipos de dados estejam definidos.

Agora você tem uma tabela completa de taxas de câmbio.

No entanto, isso só funciona para esta tabela. Você tem que encontrar uma maneira de aplicar a mesma lógica a uma tabela onde as moedas são agrupadas.

Mesclando todas as moedas

Se você tiver moedas diferentes, é difícil manter tabelas de taxas diferentes. Então, você precisa mesclá-los para fazer uma tabela de moedas.

Para começar, pegue sua tabela existente e crie uma função clicando em Table e selecionando Create Function . Em seguida, insira um nome de função.

Depois disso, vá para Editor avançado . Remova os cálculos que você não precisará. Nesse caso, elimine a origem, filtre e altere a sintaxe.

Em seguida, nomeie seu parâmetro Source e insira Let. Como as Linhas Filtradas foram removidas, altere o valor dentro da sintaxe das Consultas Mescladas para Origem. Depois disso, exclua o Source no final da sintaxe e clique em Concluído.

Agora, vá para Add Column, clique em Invoke Custom Function. Insira um nome de coluna como Todos os dados. Para a consulta de função, selecione a função que foi criada anteriormente. Escolha Todos como Origem e clique em OK.

A próxima coisa a fazer é remover todas as colunas, exceto Todos os dados. Em seguida, expanda essa coluna e desmarque a configuração “Usar o nome da coluna original como prefixo”. Depois disso, clique em OK e altere os tipos de coluna.

Defina o tipo das colunas Date, Ticker e Crossrate como Date, Text e Decimal, respectivamente. Feito isso, clique em Fechar e Aplicar.

Agora você obterá uma tabela combinada de taxas de câmbio sem datas e taxas ausentes.

Criando relacionamentos no modelo de dados

Em seu modelo de dados, você pode ver a tabela FactCurrencyRates . Agora, crie um relacionamento, Ticker to Ticker e Date to Date, com essa tabela e as tabelas DimCurrencyRates e DimDate.

Em sua matriz, puxe a taxa de câmbio sem falta para ver se agora ela tem as taxas para os dias 27 e 28, tudo feito no Power Query.

A coluna Última Taxa de Moeda Relatada também mostra as taxas dos dias faltantes. Apresenta as mesmas taxas e valores. Mas a diferença entre eles é que essa coluna é criada no DAX.

Esta é a medida e a sintaxe dessa coluna. Não é tão simples quanto usar apenas a função no Power Query.

Olhando para a tabela, você verá a coluna Vendas convertidas sem falta. Ele mostra as vendas que são convertidas usando a coluna Taxa de moeda sem falta. A outra coluna com vendas convertidas usa a coluna que foi feita no DAX.

A função usada para obter as vendas convertidas é . Se você observar a medida dessa coluna, poderá ver o SUMX passando pela tabela DimDate.

Comparando os métodos DAX e Power Query

Na verdade, você pode comparar as duas colunas de vendas convertidas que usaram DAX e Power Query no LuckyTemplates.

Para fazer isso, elimine todas as colunas, exceto as colunas de vendas convertidas. Em seguida, abra o analisador de desempenho e clique em Iniciar gravação.

Em seguida, abra o DAX Studio. É um aplicativo que mostra como e por que as coisas estão funcionando em seu modelo. Depois disso, copie a query da sua matriz e cole dentro do studio.

Para a comparação, transforme primeiro um deles em comentário para que não interfira na outra coluna que está sendo testada.

E, em seguida, carregue os horários do servidor e o plano de consulta. Como você está comparando desempenhos, limpe o cache antes de executar a comparação.

Depois de executar o teste, você pode ver que a coluna que usou o DAX obteve várias varreduras e tem um tempo total de 71 milissegundos.

Agora, execute a outra coluna que usou a consulta Power. Mas primeiro, transforme a outra coluna na sintaxe em um comentário.

Você pode ver que a coluna foi executada por apenas 25 milissegundos e tem apenas 7 varreduras.

Você pode ver claramente qual executa mais rápido e melhor entre os métodos DAX e Power Query.

Conclusão

Uma vantagem de usar o método Power Query no LuckyTemplates é que os dados já estão armazenados. Assim, a medida pode encontrar a taxa e, em seguida, trazê-la usando funções simples.

Outra coisa sobre esse método é que ele não fica lento se o cálculo ficar complexo.

No entanto, isso só funciona se os dados solicitados não precisarem ser convertidos na hora. Em caso afirmativo, você deve usar o DAX.

Tudo de bom,