Get M! Avoing API Limits

Hi Bi community. Before Start with the publications I would like to explain the goal with “Get M!” posts.

For this category, I’ll focus exclusive in M language, no beautiful and crazy layout. For me this is the most important part of an BI project, so with time, this will be the largest category of the site. Is a project for a big company, with Gigabytes of data, you will be better using a well structured Data Warehouse (DW), probably using SSDT (SQL Server Data Tools), but the goal here is to show the capability of the Power Query.

API Limits

In this example, we are trying to determinate an relationship between the many hardware components price (in the Brazilian market) and the purchasing power of the Real (that we get by comparing with the US dollar). For this we will need the historical record, preferably from an trusted source. Our final goal is to display in an single graph the percentage change (product value in the period divided by the average of the historical value product), of all product and the dollar.

Métodos de modelagem que veremos:

• Consume a public API (Query Dolar, step 7.2);
• Repeat a query and append the result in a single table (Query Dolar steps 2 to 7.4);
• Get data from a site that does not contain tables (Query Function to query the historical price, all steps);
• Convert a Query in a custom function (Query Function to query the historical price, step 4)

List of data acquisition, let’s go shop?

• Historical Dollar Real exchange rate;
• Scraping Historical of the hardware products price in the Brazilian market:
  • Custom Function to consult prices;
  • Table to invoke the created custom function in many products.
• Final Result.

Exchange rate

So, let’s start with the simplyet. To Our lucky, the Banco Central do Brasil has an open API to Query those data. You can chek the whole documentation here.

For our purpose, we will need the historical base since 2018 until today. The API documentation says that it can only return an max of 6 months. But for some reason in my tests I could only get 5 at time, and for roundup the year querys lets make a call every 4 months.

So, our steps are:

1. Add a Blank Query to the Excel or Power BI;

2. Add the follow formula:
=List.Numbers(0, Number.RoundUp(((Date.Year(DateTime.LocalNow()) – 2018) * 12 + Date.Month(DateTime.LocalNow())) /4),4)
How it Works

3. Convert the list into a table
How

4. Now lets add an new column call “Início” with the formula Date.AddMonths(#date(2018,1,1),[Column1]).
How

5. Lets add one more column and call “Fim”, this time with the formula: List.Min({DateTime.Date(DateTime.LocalNow()), Date.AddDays(Date.AddMonths([Inicio], 4),-1)}).
How

6. Before query the Banco Central do Brasil, we will convert our date to the api’s format “MM-dd-yyyy”. This can be done by editing the formula of add prifix, replacing the formula by Date.ToText(_, “MM-dd-yyyy”).
How

7.3. Copy the formula from the added query, and in the first Query, add a new Custom column named “Dolar”, changing the date fields in the URL to mach the columns “Inicio” and “Fim”.

Before: 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”))
After: 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. Delete the added query on step 7.2. since it won’t be used any more.
How

8. At the first Query lets remove all other columns that does not call “Dolar”.
How

9. Expand the Column Dolar, selecting only the column “value”.
How

10. Add a custom formula, to convert a register list into a table, with the formula Table.FromRecords(_). The full formula must be something like Table.TransformColumns(#”Expanded Dolar”, {{“value”, each Table.FromRecords(_), type table}}).
How

11. Expand the column “value” with the fields “cotacaoCompra” and “dataHoraCotacao”, that we will use. If you prefer the field cotacaoVenda can be use instead of cotacaoCompra. Lets also remane the column names to “Valor” and “Data”.
How

12. Apply detected data types.
How

13. Add a new data type step to convert the column “Data” from datetime to date.
How

14. Remove Duplicates, using Table.Distinct. This step is important to avoid error with our data set, since we do not know how the Banco Central do Brasil, stores his data. Is better add this step to be more cautions.
How

15. Add a new column call “Produto” with a fix text “Dólar”. Lets also indicate the data type in of the column by editing the M formula.
How

The result will be something like this:

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"

Historical Prices

Hardware historical prices (scraping)

Unfortunately we don’t have a trusted web site to search for the historical prices. So, for we at least have something to compare, I find a site that tracks the price of some hardware products, and we can get the date from it using the Scraping method since the site doe not have an public API. The site is baixou.com.br, so lets get started.

Function to query prices

Basically to Scraping the site we will have to search the data embedded in the HTML’s code, and keep our M code as generic as we can, since we want to use this query as a function and replicate in others pages.

1. We will query the follow site as “Web”: https://www.baixou.com.br/produtom/1594689/Memoria-Ram-8gb-Ddr3-1333mhz-Corsair. And rename the query to TesteSite.
How

2. Delete the step “Navigation” automatic inserted by Power query.

3. Alter the formula “Web.Page” to “Text.FromBinary”.
This step is necessary because this site does not have structure data in tables and all information that we want is within the graph. Let get the data of it.
How

4. Now we need to add a parameter call Site, we will use in the first step of the query ” TesteSite” and create a function base on this query.
How

5. Next we nedd to add this custom formula: = Text.Split(Text.Replace(Text.Split(Text.Split(Fonte, ” var prodId = “){1}, “”){0}, “‘”, “”),”;”)
This step is similar as the step 10 in the Dolar Query.
Why

6. We will convert the list into a table, similar as the Step 3 of the query dolar.
7. Filter the rows that contains the character “[“.
How

8. Replace the value “[” to nothing in the only column of the table.
How
9. Split a column by delimiter using “[“.
How
10. Change the Column “Column1.2” with the custom formula each Text.Split(_,”,”). Similar as the step 6 of the Query Dolar.
11. Add a custom column similar as the step 10 of the query Dolar: = Table.FromColumns(#”Prefixo Adicionado”[Column1.2])
12. Similar as the Step 7, let’s filter the Rows that is different than “00/00/00” and “” (blank).
13. Rename the column Column1 and Column2 to Data and Valor.
How
14. Replace the caracter dot by comma in the column “Valor”. Similar as the step 8. IMPORTANT: this step is necessary for users that has comma as decimal separator.
15. And detect data type, Data most be date and Valor most be number.
The code will be some thing like this:

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"

Table to invoke the created function

This query will invoke the custom fuction created in the previous query. This query will be named “Dados”.

1. Create a table containing the descripction of the product, the type of the product, the manufacturer and the URL of the site baixou.com.br.
How to in Power BI

How to in Excel
2. Add a new Column base on the Invoked Custom Function FnSite.
How
3. Expand the add column, similar as the step 9 in query dolar.
4. Detect Data type, Data and Valor (date and number).
5. And to end this, add append the Dolar Query.
How
The code will be something like this:

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

Final Result

This project’s goal has been achieved, the table “Dados”. We can now load the queries and create this custom DAX measure to evaluate the data:

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

With this we can plot the data in a line graph where the axis is the Data Column, Produto is the legend and Média Histórica is the value.

Now we can analyse the data and check if the price of the hardware is related with the Real’s purchasing power.

In the graph above we see the exchange rate of Real and the others products also shown. As far as I can tell, the result is inconclusive. Unfortunately the site that we choose has not enough data points. We need a data base with more products and with a bigger frequency.

Any way, I hope that you could get some different technique to use in your projects with this article. Obrigado!