Using Alphavantage API in Power Query produces an error

[Martynas_BI]

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"

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

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

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

Best,

Martynas

[Martynas_BI]

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.

[lbendlin]

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

[Martynas_BI]

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

[lbendlin]

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