Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all!
I'm in need of some help from knowledgable members who have experience using APIs as a data source in Power BI. I am trying to pull financials for companies using IEX's APIs (https://api.iextrading.com/1.0/stock/aapl/financials?period=annual). However, I don't want to pull the data for just one company (aapl in my example URL), but rather an entire list of companies (https://api.iextrading.com/1.0/ref-data/symbols). Is there a way to pass the list of symbols in to the URL so that it pulls the data for each ticker symbol?
This is the code for just a single company:
let
Source = Json.Document(Web.Contents("https://api.iextrading.com/1.0/stock/aapl/financials?period=annual")),
financials = Source[financials],
#"Converted to Table" = Table.FromList(financials, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"reportDate", "grossProfit", "costOfRevenue", "operatingRevenue", "totalRevenue", "operatingIncome", "netIncome", "researchAndDevelopment", "operatingExpense", "currentAssets", "totalAssets", "totalLiabilities", "currentCash", "currentDebt", "totalCash", "totalDebt", "shareholderEquity", "cashChange", "cashFlow", "operatingGainsLosses"}, {"reportDate", "grossProfit", "costOfRevenue", "operatingRevenue", "totalRevenue", "operatingIncome", "netIncome", "researchAndDevelopment", "operatingExpense", "currentAssets", "totalAssets", "totalLiabilities", "currentCash", "currentDebt", "totalCash", "totalDebt", "shareholderEquity", "cashChange", "cashFlow", "operatingGainsLosses"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"grossProfit", type text}, {"costOfRevenue", type text}, {"operatingRevenue", type text}, {"totalRevenue", type text}, {"operatingIncome", type text}, {"netIncome", type text}, {"researchAndDevelopment", type text}, {"operatingExpense", type text}, {"currentAssets", type text}, {"totalAssets", type text}, {"totalLiabilities", type text}, {"currentCash", type text}, {"currentDebt", type text}, {"totalCash", type text}, {"totalDebt", type text}, {"shareholderEquity", type text}, {"cashChange", type text}, {"cashFlow", type text}, {"operatingGainsLosses", type text}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"grossProfit", Currency.Type}, {"costOfRevenue", Currency.Type}, {"operatingRevenue", Currency.Type}, {"totalRevenue", Currency.Type}, {"operatingIncome", Currency.Type}, {"netIncome", Currency.Type}, {"researchAndDevelopment", Currency.Type}, {"operatingExpense", Currency.Type}, {"currentAssets", Currency.Type}, {"totalAssets", Currency.Type}, {"totalLiabilities", Currency.Type}, {"currentCash", Currency.Type}, {"currentDebt", Currency.Type}, {"totalCash", Currency.Type}, {"totalDebt", Currency.Type}, {"shareholderEquity", Currency.Type}, {"cashChange", Currency.Type}, {"cashFlow", Currency.Type}, {"operatingGainsLosses", Currency.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"totalRevenue", "Total Revenue"}, {"reportDate", "Date"}})
in
#"Renamed Columns"
Here is the code for getting the list of symbols:
let
Source = Json.Document(Web.Contents("https://api.iextrading.com/1.0/ref-data/symbols")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"symbol", "name", "date", "isEnabled", "type", "iexId"}, {"symbol", "name", "date", "isEnabled", "type", "iexId"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([isEnabled] = true))
in
#"Filtered Rows"
Thanks in advance for any help!!!
I recently wrote an article on something very similar. https://community.powerbi.com/t5/Community-Blog/Turning-quot-Web-by-Example-quot-into-Power-Query-Fu...
You are going to want to pull your symbols into a table. Then you can calculate the URL using the symbol. Then you can invoke a custom function like below, passing in your URL.
let fnGetDetail = (url) => let Source = Json.Document(Web.Contents(url)), financials = Source[financials], #"Converted to Table" = Table.FromList(financials, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"reportDate", "grossProfit", "costOfRevenue", "operatingRevenue", "totalRevenue", "operatingIncome", "netIncome", "researchAndDevelopment", "operatingExpense", "currentAssets", "totalAssets", "totalLiabilities", "currentCash", "currentDebt", "totalCash", "totalDebt", "shareholderEquity", "cashChange", "cashFlow", "operatingGainsLosses"}, {"Column1.reportDate", "Column1.grossProfit", "Column1.costOfRevenue", "Column1.operatingRevenue", "Column1.totalRevenue", "Column1.operatingIncome", "Column1.netIncome", "Column1.researchAndDevelopment", "Column1.operatingExpense", "Column1.currentAssets", "Column1.totalAssets", "Column1.totalLiabilities", "Column1.currentCash", "Column1.currentDebt", "Column1.totalCash", "Column1.totalDebt", "Column1.shareholderEquity", "Column1.cashChange", "Column1.cashFlow", "Column1.operatingGainsLosses"}) in #"Expanded Column1" in fnGetDetail
@ImkeF may have more options and advice. There is also an "Advanced" option to the Web Connector that allows passing in of "parts" which may be of use.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |