Forum Discussion

Till__'s avatar
Till__
Helper I
3 years ago

Connecting Eurostat Json File

Dear all, 

I want to get the daily exchange rates from the eurostat website. Unfortunatly they changed the web adress... 

Before I used this code: 

let

  Quelle = Table.FromColumns({Lines.FromBinary(Web.Contents("https://ec.europa.eu/eurostat/wdds/rest/data/v2.1/unicode/de/ert_bil_eur_d?sinceTimePeriod=2019M07D01&precision=2&unit=NAC&statinfo=AVG&currency=ALL&currency=ARS&currency=AUD&currency=BAM&currency=BGN&currency=BRL&currency=CAD&currency=CHF&currency=CNY&currency=CZK&currency=DKK&currency=DZD&currency=EGP&currency=GBP&currency=HKD&currency=HRK&currency=HUF&currency=IDR&currency=ILS&currency=INR&currency=ISK&currency=JPY&currency=KRW&currency=MAD&currency=MKD&currency=MXN&currency=MYR&currency=NOK&currency=NZD&currency=PHP&currency=PLN&currency=RON&currency=RSD&currency=RUB&currency=SEK&currency=SGD&currency=THB&currency=TRY&currency=TWD&currency=USD&currency=ZAR"), null, null, 65001)}),

  #"Spalte nach Trennzeichen teilen" = Table.SplitColumn(Quelle, "Column1", Splitter.SplitTextByDelimiter("#(tab)")),

  #"Höher gestufte Header" = Table.PromoteHeaders(#"Spalte nach Trennzeichen teilen", [PromoteAllScalars = true]),

  #"Entfernte obere Zeilen" = Table.Skip(#"Höher gestufte Header", 6),

  #"Transponierte Tabelle" = Table.Transpose(#"Entfernte obere Zeilen"),

  #"Entfernte obere Zeilen 1" = Table.Skip(#"Transponierte Tabelle", 1),

  #"Umbenannte Spalten" = Table.RenameColumns(#"Entfernte obere Zeilen 1", {{"Column1", "Date"}}),

  #"Ersetzter Wert" = Table.ReplaceValue(#"Umbenannte Spalten", "NA", "Date", Replacer.ReplaceText, {"Date"}),

  #"Höher gestufte Header 1" = Table.PromoteHeaders(#"Ersetzter Wert", [PromoteAllScalars = true]),

  #"Entfernte obere Zeilen 2" = Table.Skip(#"Höher gestufte Header 1", 1),

....

 

The new link to get the new json file from the query builder is this one:

https://ec.europa.eu/eurostat/api/dissemination/statistics/1.0/data/ert_bil_eur_d?format=JSON&freq=D&unit=NAC&statinfo=END&currency=BGN&currency=CZK&currency=DKK&currency=HUF&currency=PLN&currency=RON&currency=SEK&currency=CHF&currency=ISK&currency=NOK&currency=GBP&currency=ALL&currency=MKD&currency=RSD&currency=TRY&currency=ARS&currency=AUD&currency=BAM&currency=BRL&currency=CAD&currency=CNY&currency=DZD&currency=EGP&currency=HKD&currency=IDR&currency=ILS&currency=INR&currency=JPY&currency=KRW&currency=MAD&currency=MXN&currency=MYR&currency=NZD&currency=PHP&currency=RUB&currency=SGD&currency=THB&currency=TWD&currency=USD&currency=ZAR

 

Is there anyone who is able to include the json file in the first steps? 

Thank you all in advance! 

No RepliesBe the first to reply