Get M! Contornando limites em API

Olá comunidade do BI! Antes de Iniciar com as publicações eu gostaria de explicar qual o objetivo das postagens Get M!

Para os artigos dessa categoria, estarei focando única e exclusivamente na linguagem M, nada de layouts bonitos e fazer os olhos brilharem. Essa é a parte que eu particularmente acho mais importante dentro de um projeto de BI. Evidentemente em um projeto grande, como em grandes empresas com Gigabytes de dados, você estará trabalhando com um DW (Data Warehouse) bem elaborado em SQL provavelmente utilizando o SSMS (SQL Server Management Studio), mas o foco destes artigos é demonstrar a capacidade da Liguagem M até mesmo utilizando o Power Query no Excel.

Limites em API

Neste Exemplo, estamos tentando determinar se existe uma relação entre o preços de diversos componentes eletrônicos, e o Dólar. Para isso vamos precisar de bases históricas, preferencialmente de fontes confiáveis. Nosso objetivo Final é montar um único gráfico contendo as variações percentuais (valor do produto no período / média do valor do produto no histórico) de todos os produtos e do dólar.

Métodos de modelagem que veremos:

• Consumir uma API pública (Query Dólar, passo 7.2);
• Repetir uma consulta e unir o resultado em uma única tabela (Query Dólar passos 2 à 7.4);
• Obter dados de sites que possui os dados de forma tabelada (Query Função para consultar o histórico de preços, todos os passos);
• Converter uma Query em função personalizada (Query Função para consultar o histórico de preços, passo 4)

Lista de aquisição de dados, vamos às compras?

• Histórico da cotação do dólar;
• Scraping de Histórico de preços de diversos aparelhos eletrônicos:
  • Função para consultar os preços;
  • Tabela para invocar a função criada em vários produtos.
• Resultado Final.

Dólar

Vamos então começar pelo mais simples. Para nossa sorte o Banco Central do Brasil possui uma API aberta para consultarmos os dados. Você pode ver toda a documentação aqui.

Neste caso, vamos precisar da base histórica a partir de 2018 até a data de hoje. Entretanto a documentação da API nos informa que ela a API só retorna dados de no máximo 6 meses. Por algum motivo, em meus testes os dados vieram somente para 5 meses, então para evitar problemas e arredondar o número de consultas por ano, vamos precisar realizar uma consulta para cada 4 meses.

Nossos passos são:

1. Adicionar uma Query em Branco no Excel ou Power BI, e nomea-la como Dolar;

2. Adicionar a Seguinte Fórmula:
=List.Numbers(0, Number.RoundUp(((Date.Year(DateTime.LocalNow()) – 2018) * 12 + Date.Month(DateTime.LocalNow())) /4),4)
Como Funciona

3. Converter a Lista em uma Tabela
Como

4. Agora vamos adicionar uma Nova Coluna Chamada “Início” com a Fórmula Date.AddMonths(#date(2018,1,1),[Column1])
Como

5. Vamos adicionar mais uma Coluna, desta vez com a Fórmula: List.Min({DateTime.Date(DateTime.LocalNow()), Date.AddDays(Date.AddMonths([Inicio], 4),-1)})
Como

6. Antes de consultar o banco central, vamos converter o formato das nossas datas para o layout da API “MM-dd-yyyy”. Isso pode ser feito editando uma Query de adição de Prefixo subtituindo a formula each por: “Date.ToText(_, “MM-dd-yyyy”)”.
Como

7. Hora de consultar os dados do Banco Central, vamos adicionar mais uma coluna personalizada com a fórmula de consumo da API do Banco, com modificações nos parâmetros da data. A forma mais fácil de se obter essa fórmula é adicionando uma nova Query de consulta de site do Banco Central.

7.1. Obter um link de teste no site do Banco Central. Isso pode ser feito Clicando aqui. No meu caso o meu resultado foi a URL: https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/CotacaoDolarPeriodo(dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?@dataInicial=’01-01-2020’&@dataFinalCotacao=’02-29-2020’&$top=100&$format=json

7.2. Consumir a API em uma nova consulta.
Como

7.3. Copie o Resultado da Query do passo 7.2, e adicione ele como uma nova coluna, que será nomeada “Dólar”, alterando os campos de data na Query para que utilizem as demais colunas.

Antes: Json.Document(Web.Contents( “https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/CotacaoDolarPeriodo(dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)? @dataInicial=’01-01-2020′& @dataFinalCotacao=’02-29-2020′&$top=100&$format=json”))
Depois: Json.Document(Web.Contents( “https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/CotacaoDolarPeriodo(dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)? @dataInicial=‘” & [Inicio] & “‘& @dataFinalCotacao=‘” & [Fim] & “‘&$top=100&$format=json”))

7.4. Excluir a Query do passo 7.2, pois ela não será mais utilizada.
Como

8. Voltando na consulta que estamos montando, vamos remover as colunas que não Chamam “Dolar”
Como

9. Expandir a coluna “Dolar” selecionando apenas a coluna “value”.
Como

10. Adicionar uma fórmula personalizada, para converter a lista de registros em uma tebela com a fórmula Table.FromRecords(_). A fórmula completa deve ficar parecida com essa: Table.TransformColumns(#”Expanded Dolar”, {{“value”, each Table.FromRecords(_), type table}})”
Como

11. Expandir a coluna “value”, com os campos cotacaoCompra e dataHoraCotacao, que iremos utilizar. Se preferir pode utilizar o campo cotacaoVenda ao invés do cotacaoCompra. Vamos aproveitar e renomear as colunas para “Valor” e “Data”.
Como

12. Aplicar uma formatação sugerida nas novas colunas.
Como

13. Aplicar uma nova etapa de formatação trocando a coluna “Data” para o formato tipo date.
Como

14. Remover possíveis Duplicatas, usando a Table.Distinct. Isso é importante pois não sabemos se nesse banco de dados do Banco Central fora salvo mais de uma Cotação de Dólar no mesmo dia, como o formato de origem veio como Data Hora, é melhor nos resguardar e aplicar essa fórmula, já que só temos interesse em um valor por data.
Como

15. Adicionar uma coluna chamada “Produto”, com o texto fixo “Dólar”. Iremos precisar pois essa Query será unida com a Query dos nossos produtos. Vamos aproveitar e formata-la como texto. Com uma rápida edição na função M.
Como

O código ficará assim:

let
    Custom1 = List.Numbers(0,Number.RoundUp(((Date.Year(DateTime.LocalNow())-2018)*12+Date.Month(DateTime.LocalNow()))/4),4),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom5" = Table.AddColumn(#"Converted to Table", "Inicio", each Date.AddMonths(#date(2018,1,1),[Column1])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom5", "Fim", each List.Min({DateTime.Date(DateTime.LocalNow()), Date.AddDays(Date.AddMonths([Inicio], 4),-1)})),
    #"Added Prefix" = Table.TransformColumns(#"Added Custom1", {{"Inicio", each Date.ToText(_, "MM-dd-yyyy"), type text},{"Fim", each Date.ToText(_, "MM-dd-yyyy"), type text}}),
    #"Added Custom" = Table.AddColumn(#"Added Prefix", "Dolar", each Json.Document(Web.Contents("https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/CotacaoDolarPeriodo(dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?@dataInicial='" & [Inicio] & "'&@dataFinalCotacao='" & [Fim] & "'&$top=100&$format=json"))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Dolar"}),
    #"Expanded Dolar" = Table.ExpandRecordColumn(#"Removed Other Columns", "Dolar", {"value"}, {"value"}),
    #"Added Prefix2" = Table.TransformColumns(#"Expanded Dolar", {{"value", each Table.FromRecords(_), type table}}),
    #"Expanded value" = Table.ExpandTableColumn(#"Added Prefix2", "value", {"cotacaoCompra", "dataHoraCotacao"}, {"Valor", "Data"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded value",{{"Valor", type number}, {"Data", type datetime}}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Changed Type2",{{"Data", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Tipo Alterado", {"Data"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Duplicates", "Produto", each "Dólar", type text)
in
    #"Added Custom2"

Histórico de Preços (Scraping)

Infelizmente não temos um site confiável para buscar o histórico de preços. Então para ao menos termos o que comparar localizei um site que faz o acompanhamento de preços e que conseguiremos extrair os dados utilizando o método de scraping, já que não conseguimos uma API. O site é baixou.com.br. Então vamos iniciar.

Função para consultar os preços

Basicamente o método de Scraping consiste em buscar as informações dentro o código HTML, tentando ao máximo manter o nosso código o mais genérico o possível, já que pretendemos usar essa primeira consulta para replicar em outras páginas.

1. Vamos começar consultando o site https://www.baixou.com.br/produtom/1594689/Memoria-Ram-8gb-Ddr3-1333mhz-Corsair. E nomear essa query como TesteSite.
Como

2. Vamos excluir a etapa “Navegation” inserida pelo Power query automaticamente.

3. Vamos Alterar a fórmula “Web.Page” para “Text.FromBinary”.
Essa etapa se faz necessária pois o site não tem esses dados dentro de uma tabela e sim dentro de um gráfico. Vamos obter as informações diretamente deste gráfico.
Como

4. Vamos adicionar um parâmetro tipo texto chamado Site, usa-lo na na primeira etapa e criar uma função personalizada.
Como

5. Em seguida vamos adicionar esta fórmula personalizada: = Text.Split(Text.Replace(Text.Split(Text.Split(Fonte, ” var prodId = “){1}, “”){0}, “‘”, “”),”;”)
Essa etapa é similar à etapa 10 da Query Dolar.
Porquê

6. Vamos converter a lista em tabela, Igual fizemos no passo 3 da Query Dolar.
7. Vamos filtrar as linhas que contenham o caracter “[“.
Como

8. Vamos Substituir o valor “[” por nada na única coluna da nossa tabela.
Como
9. Vamos quebrar a coluna por delimitador utilizando o delimitador “[“.
Como
10. Vamos alterar a coluna “Column1.2″ com a fórmula personalizada each Text.Split(_,”,”). Similar ao que fizemos no passo 6 da Query Dolar.
11. Em seguida iremos adicionar mais uma fórmula personalizada similar ao passo 10 da tabela Dolar: = Table.FromColumns(#”Prefixo Adicionado”[Column1.2])
12. Similar ao passo 7, vamos filtrar as linhas onde a Column1 é diferente de “00/00/00” e “” (vazio).
13. Vamos renomear as colunas Column1 e Column2 para Data e Valor.
Como
14. Novamente substituiremos um valor, desta vez o ponto da coluna “Valor” por vírgula. Esse procedimento é similar ao item 8.
15. Por fim, vamos detectar o tipo de dados. Deverá ser Data como date e Valor como number.
O código ficará assim:

let
    Fonte = Text.FromBinary(Web.Contents(Site)),
    Personalizar2 = Text.Split(Text.Replace(Text.Split(Text.Split(Fonte, " var prodId = "){1}, "</script>"){0}, "'", ""),";"),
    #"Convertido para Tabela1" = Table.FromList(Personalizar2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Linhas Filtradas" = Table.SelectRows(#"Convertido para Tabela1", each Text.Contains([Column1], "[")),
    #"Valor Substituído" = Table.ReplaceValue(#"Linhas Filtradas","]","[",Replacer.ReplaceText,{"Column1"}),
    #"Dividir Coluna por Delimitador" = Table.SplitColumn(#"Valor Substituído", "Column1", Splitter.SplitTextByDelimiter("[", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Tipo Alterado1" = Table.TransformColumnTypes(#"Dividir Coluna por Delimitador",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
    #"Prefixo Adicionado" = Table.TransformColumns(#"Tipo Alterado1", {{"Column1.2", each Text.Split(_,","), type list}}),
    #"Outras Colunas Removidas" = Table.FromColumns(#"Prefixo Adicionado"[Column1.2]),
    #"Linhas Filtradas2" = Table.SelectRows(#"Outras Colunas Removidas", each ([Column1] <> "00/00/00")),
    #"Colunas Renomeadas" = Table.RenameColumns(#"Linhas Filtradas2",{{"Column1", "Data"}, {"Column2", "Valor"}}),
    #"Valor Substituído1" = Table.ReplaceValue(#"Colunas Renomeadas",".",",",Replacer.ReplaceText,{"Valor"}),
    #"Tipo Alterado2" = Table.TransformColumnTypes(#"Valor Substituído1",{{"Data", type date}, {"Valor", type number}}),
    #"Linhas Filtradas1" = Table.SelectRows(#"Tipo Alterado2", each ([Data] <> null))
in
    #"Linhas Filtradas1"

Tabela para invocar a função criada

Essa query servirá para incovar a função personalizada que criamos na etapa anterior. Essa consulta deve se chamar “Dados”.

1. Criamos uma tabela contendo a descrição do produto, o tipo, o fabricante e a URL do site baixou.com.br
Como fazer no Power BI

Como fazer no Excel
2. Agora vamos adicionar uma coluna de Invocação da Fórmula FnSite.
Como
3. Agora vamos expandir a coluna que adicionamos, similar ao passo 9 da Query Dolar.
4. Vamos detectar o tipo de dados das colunas Data e Valor (date e number).
5. Por fim vamos adicionar uma função personalizada: = Table.Combine({ #”Tipo Alterado1″, Dolar})
O código ficará assim:

let
     Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZPNboJAFIVf5Ya11xmYGYTutCRqjAkRuzIuQBolFTCAtelr9RH6Yr0D4u/GupnM7/nOnDuzWBhB4MEkydZllWfQl5yDdPhwYHT0CrXtGnU3VbUrXxg7HA7dKEy+8n13lafdqGC7Io/3VZ4yyxS2LU02ijEoYySpdYT+dl9iEGZxUn4Yy87CGMVAq3CcgrdtVYQwtGywJO0/odsjj5GlsmxLtOTjWay1kbSx0a7xHuhbm/MBBB71+mjOOdebAOEMfcaEkj3lkAkP6TTJDvCGcJVDG4AOowlhnDQZgFIcFefp02lYPeko1sagI2miGCdNEngm1FYu6lFXra6HEiqNWPmkB+GaJpfsshzNg9A51NJlzZ6+p78/RRLCLEzBIbQXFwJMIUS6+YbXvCjDpCDmrD+l9jx+wIOpXGk7LiNETgQkAhIBNQGPBGwV77yc/sWtqZOZf/4ORa/DkVduWoV7W+ivaMBtejg4+Sz0dCwy11mjH1Ih/ZWxXP4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Produto = _t, Tipo = _t, Fabricante = _t, Site = _t]),
     #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Produto", type text}, {"Tipo", type text}, {"Fabricante", type text}, {"Site", type text}}),
     #"Função Personalizada Invocada" = Table.AddColumn(#"Tipo Alterado", "FnSite", each FnSite([Site])),
     #"FnSite Expandido" = Table.ExpandTableColumn(#"Função Personalizada Invocada", "FnSite", {"Data", "Valor"}, {"Data", "Valor"}),
     #"Tipo Alterado1" = Table.TransformColumnTypes(#"FnSite Expandido",{{"Data", type date}, {"Valor", type number}}),
     Personalizar1 = Table.Combine({#"Tipo Alterado1", Dolar})
 in
     Personalizar1

Resultado Final

O objetivo desse projeto foi Concluído. A tabela “Dados” é o nosso objetivo final. Podemos criar uma media personalizado em DAX da seguinte forma

Média Histórica = DIVIDE(
    SUM(Dados[Valor]);
    CALCULATE(AVERAGE(Dados[Valor]);ALLEXCEPT(Dados;Dados[Produto]));0)

Com esse medida Podemos plotar nossos dados em um gráfico de linhas onde o Eixo é a data, a legenda é o Produto e o valor é a média Histórica.

Chegamos ao nosso objetivo final agora podemos analisar as informações para ver se o preço do dólar tem uma relação com o preço de compra dos produtos.

No exemplo acima vemos o Dólar em azul claro e os demais produtos listados no gráfico. Ao meu ver o resultado é inconclusivo. Nossa base de dados não é completa o suficiente para para podermos ter informações conclusivas sobre o preço dos produtos. Precisamos de uma base de dados com mais produtos e com o preço em um intervalo de datas menor.

De qualquer forma espero que possam retirado alguma técnica diferente para obtenção de dados através deste artigo. Obrigado!