Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
FRED (Federal Reserve Econcomic Data) is a great website that offers all kinds of economic series data free to the public, and I'm wondering if anyone can recommend a good connector to import these data series into BI.
Thank you in advance.
Solved! Go to Solution.
API can be tough. Basically, it's like getting from a web source. Check out these instructions: https://blogs.msdn.microsoft.com/charles_sterling/2016/05/25/how-to-call-rest-apis-and-parse-json-wi...
Hello All - I've built a custom connector for FRED (and the Energy Information Administration – EIA). Would appreciate you trying it out - and providing feedback. Hopefully, this can be a built-in/certified connector one day. https://github.com/tylerchessman/PBI_FRED_EIA;
If you like what you see, make sure to vote for the feature at https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=437ab693-4155-ed11-a76e-0003ff4534c2. For EIA data, please vote at https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=18f4857d-5269-412e-b093-a036b52c3fa1
I had been trying to get more than one economic indicator in power bi at the same time through their API so my friend and I created a python script to collect whatever number of series you want and appends them already making it really easy to slice when in power bi. I hope you guys enjoy this it took me a long time. It works with python 3.77 and make sure you have the modules requests, matplotlib and pandas. Oh and make sure you add your API token(key)
Enjoy and don't take credit for it.
#Modules
import urllib.request as urllib2
import urllib, json
import numpy as np
import pandas as pd
#Static Variables
TOKEN = 'YourAPItoken'
#initialzing array for tickers
tickers= []
#open file and create one array of tickers IDs
tickers =['mspnhsus'
]
#temporary variable store values from "for loop"
d = []
#creating list of URL based on tickers list
for ticker in tickers:
tdata = '['
url = str(f'https://api.stlouisfed.org/fred/series/observations?series_id={ticker.strip()}&api_key={TOKEN}&file_...')
#Opening web api and read JSON data
response = urllib2.urlopen(url)
#create JSON object
json_obj = json.load(response)
for i in json_obj['observations']:
d.append({'ticker': ticker, 'date': i['date'], 'value': i['value']})
#create variable "df" using format dataframe
df = pd.DataFrame(data=d)
#print data ready to use for PowerBi
print(df)
You are awesome!
You can pull FRED data in with a Python script without an API if you have Python installed or you're comfortable installing it. Get Data/Python script then paste in code per this model:
import pandas_datareader.data as web import datetime # Choose start and end dates for the data series start = datetime.datetime(2015, 1, 1) end = datetime.datetime(2019, 10, 9) # Read the data: US Housing Starts HOUST FRED data set houst = web.DataReader('HOUST', 'fred', start, end) # Move the dates, which are row labels, to their own column houst.index.name = 'date' houst.reset_index(inplace=True)
This worked perfect for me. Thanks for your help
These links are broken
API can be tough. Basically, it's like getting from a web source. Check out these instructions: https://blogs.msdn.microsoft.com/charles_sterling/2016/05/25/how-to-call-rest-apis-and-parse-json-wi...
I saw that. I know this is a very newbie question, but how do I use an API with PBI?
https://api.stlouisfed.org/fred/series/observations?series_id=GNPCA&api_key=abcdefghijklmnopqrstuvwxyz123456
I was using Python to pull FRED data series, but after discovering the web connector has a refresh advantage, I'm switching.
I tried using the example it does connect and get a reply but it only returns three columns to query editor :
Series, Attibute:realtime_start, Attribute:realtime_end.
It does not return data and it only has the same date for start and end .
There has to be a way to get data and specify dates
If you'd like to share your code, I'd be happy to help troubleshoot. Were you able to try this step to transform and expand, or do you not have that option?
Thank you for your reply. I used in PowerBI "Get from Web" and then the example which is at the FRED API :
https://api.stlouisfed.org/fred/series/observations?series_id=GNPCA&api_key=abcdefghijklmnopqrstuvwxyz123456
but I substituted my API key. This is what comes back after I click transform:
@powerbime, your api request looks good. This is what I get when I use it. The tan word Table is a signal that you have some data to expand. Click the double expansion arrows in the green square to see what's available.
You can choose some or all.
Chances are good you only want date and value, and you probably don't want to keep the original column name as prefix (you can uncheck what you don't want in the prior step), but here's everything in this data set.
Thanks,
I see the expansion arrows and when i choose I get the menu like in your picture but no "Value". See the snips below. I am using the GNPCA indicator but I do not get the same data as in your example. It's like if it is only giving me the indicators description but no values or historical dates.
What I get is this
In the transform data area, there is be a ribbon option that's labeled something like advanced editor. Can you please open that and take a screenshot of the window to post here to help with further troubleshooting? Of course it's fine to obscure your API key.
Here it is and thanks again. I don't know why I am having so much trouble.
And with the expansion as well
Try this, @powerbime . You can skip the change type step, which I'm guessing was automatic. The removed other columns step gets this down to just the date and the observation. If you want the other columns, delete this step.
let
Source = Xml.Tables(Web.Contents("https://api.stlouisfed.org/fred/series/observations?series_id=GNPCA&api_key=YOURKEYHERE")),
#"Expanded observation" = Table.ExpandTableColumn(Source, "observation", {"Attribute:date", "Attribute:value"}, {"Attribute:date", "Attribute:value"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded observation",{"Attribute:date", "Attribute:value"})
in
#"Removed Other Columns"
No luck
Maybe I am getting different data.
the Series you posted does not have the same columns I get. For one I do not get attribute:value, which is why i imagine I so not see the data.
Hi, @powerbime I can see attribute:value in the list in your screen shot. Do you need to scroll to the right to see the column? I recommend unchecking all but the value and date.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
107 | |
106 | |
90 | |
61 |
User | Count |
---|---|
165 | |
136 | |
134 | |
97 | |
86 |