I would like to import weather forecasts in PowerPivot for a specified country by using Yahoo API. So first of all I got the data stream URL including all requiered parameters to get forecasts (it works in my browser).
https://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20weather.forecast%20where%20woeid%3D612977&format=json&diagnostics=true&callback=
Then I tried to use it as "Other data Stream" in PowerPivot to grab the content. However I got the following strange error about DTD :
Cannot connect to the specified feed. Verify the connection and try again. Reason: For security reasons DTD is prohibited in this XML document. To enable DTD processing set the DtdProcessing property on XmlReaderSettings to Parse and pass the settings into XmlReader.Create method
Any idea to solve that ?
The easiest was to install PowerQuery for Microsoft Excel then creating a new request to Yahoo Web Services. After parsing data in PowerQuery (split columns, etc) , formatted data is available in a PowerPivot tab and you can use it as well as direct imported data in PowerPivot.