Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Martynas_BI
Frequent Visitor

Using Alphavantage API in Power Query produces an error

Hi,

 

I've been trying to get financial data (Income Statement, in particular) relating to public stocks from Alphavantage API, which is free.

https://www.alphavantage.co/documentation/

 

This is the M code i've used
Note: in row 3, it represent my personal computer path where I had created an Excel file with several stock tickers (i.e. AAPL, MSFT, TSLA), to which I could add tickers and the data in Power BI would extract additional financials automatically).

The later text in red (123....) represents the unique user/API key that you get for free from Alphavantage.

 

let
// Step 1: Load the list of unique identifiers from the Excel file
Source = Excel.Workbook(File.Contents("DummyTextWhereIusedMyPersonalDrive"), null, true),
SP500_Sheet = Source{[Item="SP500",Kind="Sheet"]}[Data],

// Step 2: Transform the data to a list of unique identifiers
UniqueIdentifiers = SP500_Sheet[Column1],

// Step 3: Define function to fetch data for a single company
GetCompanyData = (symbol as text) =>
let
Source = Json.Document(Web.Contents("https://www.alphavantage.co/query?function=INCOME_STATEMENT&symbol=" & symbol & "&apikey=12345678.....")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded quarterlyReports" = Table.ExpandListColumn(#"Converted to Table", "quarterlyReports"),
#"Expanded quarterlyReports1" = Table.ExpandRecordColumn(#"Expanded quarterlyReports", "quarterlyReports", {"fiscalDateEnding", "reportedCurrency", "grossProfit", "totalRevenue", "costOfRevenue", "costofGoodsAndServicesSold", "operatingIncome", "sellingGeneralAndAdministrative", "researchAndDevelopment", "operatingExpenses", "investmentIncomeNet", "netInterestIncome", "interestIncome", "interestExpense", "nonInterestIncome", "otherNonOperatingIncome", "depreciation", "depreciationAndAmortization", "incomeBeforeTax", "incomeTaxExpense", "interestAndDebtExpense", "netIncomeFromContinuingOperations", "comprehensiveIncomeNetOfTax", "ebit", "ebitda", "netIncome"}, {"fiscalDateEnding", "reportedCurrency", "grossProfit", "totalRevenue", "costOfRevenue", "costofGoodsAndServicesSold", "operatingIncome", "sellingGeneralAndAdministrative", "researchAndDevelopment", "operatingExpenses", "investmentIncomeNet", "netInterestIncome", "interestIncome", "interestExpense", "nonInterestIncome", "otherNonOperatingIncome", "depreciation", "depreciationAndAmortization", "incomeBeforeTax", "incomeTaxExpense", "interestAndDebtExpense", "netIncomeFromContinuingOperations", "comprehensiveIncomeNetOfTax", "ebit", "ebitda", "netIncome"})
in
#"Expanded quarterlyReports1",

// Step 4: Fetch data for each company in the list
CompanyData = List.Transform(UniqueIdentifiers, each GetCompanyData(_))
in
CompanyData

 

I keep getting the error "The column 'quarterlyReports' of the table wasn't found"

Martynas_BI_1-1714586304288.png

 

Which may be due to the fact that you get two lists / tables to choose from? (annualReports and quarterly reports)

Martynas_BI_2-1714586378159.png

 

I've tried multiple things trying to adjust the M code, but with no luck

Martynas_BI_0-1714586203068.png

 

I would highly appreciate if anyone's encountered this or know how to solve it

 

Best,

Martynas

 

4 REPLIES 4
Martynas_BI
Frequent Visitor

Column1 (and you can see the error, which contains "quarterlyReport"). Unfortunately, I can't simply just remove errors, as when I do even though everything looks fine in PQ, when I apply and load I get the aforementioned error in Desktop.

Martynas_BI_1-1714675050136.png

 

Remove errors and then expand the column. Read about how to handle missing columns

I might be missunderstanding something, but as I just said, this approach didn't work as I've tried it

lbendlin
Super User
Super User

which columns do you see in the #"Converted to Table" step?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors