Solved: Re: Adding Multiple JSON files by Query Editor

jonasdedual23 · ‎09-17-2023

Hi There

I have the following situation. I want to add about 1000 shares to a power bi dataset by using API from eodhd. All the API request strings are saved in a seperate Table:

Here an example:

https://eodhd.com/api/eod/AAPL.US?api_token=demo&fmt=json&period=d

I then create a new query with below code(In below code I only add the colum date to make it more simple. In final version there will be more columns):

let
    Source = SYMBOLS_Price,
    AddCustomColumns = Table.AddColumn(Source, "Custom", each
        let
            ApiUrl = [Column1], 
            Source = Json.Document(Web.Contents(ApiUrl)),
            TableFromList = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            ExpandedColumn = Table.ExpandRecordColumn(TableFromList, "Column1", {"date"}, {"date"}),
            ChangedType = Table.TransformColumnTypes(ExpandedColumn, {{"date", type date} })
        in
            ChangedType
    ),
    #"Expanded Custom" = Table.ExpandTableColumn(AddCustomColumns, "Custom", {"date"}, {"Custom.date"})
in
    #"Expanded Custom"

This is working fine and I get what I want:

However when doing the same with fundament data and replacing in aboce code SYMBOLS_Price with "SYMBOLS_Cash_Flow", I'll get an Error message:

Expression.Error: We cannot convert a value of type Record to type List.
Details:
Value=[Record]
Type=[Type]

A sample of an API string would be:

https://eodhd.com/api/fundamentals/AAPL.US?api_token=demo&filter=Financials::Cash_Flow::quarterly

The only difference that I have found is the structure of the JSON format:

SYMBOLS_Price has:

[
   {
      "date":"1999-11-18",
      "open":42.8579,
      "high":47.0966,
      "low":37.6772,
      "close":40.2676,
      "adjusted_close":24.428,
      "volume":44733602
   }
]

SYMBOLS_Cash_Flow has:

{
"2023-07-31":{
"date":"2023-07-31",
"filing_date":"2023-08-31",
"currency_symbol":"USD",
......
}
}

I suspect that "2023-07-31": is the issue here. However I have no Idea how to adress this issue.

Any Help is highly apreciated.

kind Regards

Jonas

jonasdedual23 · ‎09-24-2023

Finally I have found a workaround for my Problem.

I am using now the code below:

let
    SYMBOLS_LIST = {SYMBOLS_LIST{0}, SYMBOLS_LIST{1}, SYMBOLS_LIST{2}}, 


    AddData = List.Accumulate(SYMBOLS_LIST, Table.FromRecords({}), (table, symbol) =>
        let
            ApiUrl = "https://eodhd.com/api/fundamentals",
            QueryParameters = [
                RelativePath = symbol,
                Query = [
                    api_token = "API-Token",
                    filter = "Financials::Cash_Flow::quarterly"
                ]
            ],
            Source = Json.Document(Web.Contents(ApiUrl, QueryParameters)),
            RecordList = Record.ToList(Source),
            TableFromRecord = Table.FromRecords(RecordList),
            Final = Table.AddColumn(TableFromRecord, "SymbolColumn", each symbol)
        in
            Table.Combine({table, Final})
    ),

    FinalTable = Table.Distinct(AddData),
    #"Sorted Rows" = Table.Sort(FinalTable,{{"SymbolColumn", Order.Ascending}, {"date", Order.Descending}, {"filing_date", Order.Descending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"date", "filing_date", "SymbolColumn", "currency_symbol", "investments", "changeToLiabilities", "totalCashflowsFromInvestingActivities", "netBorrowings", "totalCashFromFinancingActivities", "changeToOperatingActivities", "netIncome", "changeInCash", "beginPeriodCashFlow", "endPeriodCashFlow", "totalCashFromOperatingActivities", "issuanceOfCapitalStock", "depreciation", "otherCashflowsFromInvestingActivities", "dividendsPaid", "changeToInventory", "changeToAccountReceivables", "salePurchaseOfStock", "otherCashflowsFromFinancingActivities", "changeToNetincome", "capitalExpenditures", "changeReceivables", "cashFlowsOtherOperating", "exchangeRateChanges", "cashAndCashEquivalentsChanges", "changeInWorkingCapital", "stockBasedCompensation", "otherNonCa**bleep**ems", "freeCashFlow"}) // Optional: Entfernen von Duplikaten, falls erforderlich
in
    #"Reordered Columns"

I guess there would be a simpleer way of coding but above works for me.

The List "SYMBOLS_LIST " contains the following Entries:

A.US

AA.US

AAPL.US

I get then the below result(not all columns / Rows in this sample:

31.08.2023	A.US	USD	-89000000
26.05.2023	A.US	USD	-77000000
03.03.2023	A.US	USD	-104000000
21.12.2022	A.US	USD	-3000000
27.07.2023	AA.US	USD	-120000000
04.05.2023	AA.US	USD	-102000000
23.02.2023	AA.US	USD	-171000000
27.10.2022	AA.US	USD	-138000000
04.08.2023	AAPL.US	USD	437000000
05.05.2023	AAPL.US	USD	2319000000
03.02.2023	AAPL.US	USD	2483000000
28.10.2022	AAPL.US	USD	2806000000

View solution in original post

jonasdedual23 · ‎09-20-2023

Hi there Thanks for your reply.

As requested. This would be a sample of my expected result:

Column1	Date
https://eodhd.com/api/fundamentals/AAPL.US?api_token=demo&filter=Financials::Cash_Flow::quarterly	30.06.2023
https://eodhd.com/api/fundamentals/AA.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	30.06.2023
https://eodhd.com/api/fundamentals/AAA.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	30.06.2023
https://eodhd.com/api/fundamentals/AAAAX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	30.06.2023
https://eodhd.com/api/fundamentals/AAACX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	30.06.2023
https://eodhd.com/api/fundamentals/AAAEX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	30.06.2023
https://eodhd.com/api/fundamentals/AAAFX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	30.06.2023
https://eodhd.com/api/fundamentals/AAAGX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	30.06.2023
https://eodhd.com/api/fundamentals/AAAHX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	30.06.2023
https://eodhd.com/api/fundamentals/AAAIX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	30.06.2023
https://eodhd.com/api/fundamentals/AAPL.US?api_token=demo&filter=Financials::Cash_Flow::quarterly	31.03.2023
https://eodhd.com/api/fundamentals/AA.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	31.03.2023
https://eodhd.com/api/fundamentals/AAA.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	31.03.2023
https://eodhd.com/api/fundamentals/AAAAX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	31.03.2023
https://eodhd.com/api/fundamentals/AAACX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	31.03.2023
https://eodhd.com/api/fundamentals/AAAEX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	31.03.2023
https://eodhd.com/api/fundamentals/AAAFX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	31.03.2023
https://eodhd.com/api/fundamentals/AAAGX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	31.03.2023
https://eodhd.com/api/fundamentals/AAAHX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	31.03.2023
https://eodhd.com/api/fundamentals/AAAIX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly	31.03.2023

Not sure what you mean by that:

"Please provide sample data that fully covers your issue."

The sample data can be directly downloaded from here:

https://eodhd.com/api/fundamentals/AAPL.US?api_token=demo&filter=Financials::Cash_Flow::quarterly

Please note that api_token=demo only works for the Symbol AAPL.US However structure of each Symbol is the same.

Here is the Table of SYMBOLS_Cash_Flow: (I had to remove APIKEY)

Column1

https://eodhd.com/api/fundamentals/AAPL.US?api_token=demo&filter=Financials::Cash_Flow::quarterly

https://eodhd.com/api/fundamentals/AA.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly

https://eodhd.com/api/fundamentals/AAA.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly

https://eodhd.com/api/fundamentals/AAAAX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly

https://eodhd.com/api/fundamentals/AAACX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly

https://eodhd.com/api/fundamentals/AAAEX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly

https://eodhd.com/api/fundamentals/AAAFX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly

https://eodhd.com/api/fundamentals/AAAGX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly

https://eodhd.com/api/fundamentals/AAAHX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly

https://eodhd.com/api/fundamentals/AAAIX.US?api_token=APIKEY&filter=Financials::Cash_Flow::quarterly

lbendlin · ‎09-20-2023

Cash_Flow the transform is very simple

let
    Source = Json.Document(Web.Contents("https://eodhd.com/api/fundamentals",[RelativePath="AAPL.US", Query=[api_token="demo" ,filter="Financials::Cash_Flow::quarterly"]])),
    #"Converted to List" = Record.ToList(Source),
    #"Converted to Table" = Table.FromRecords(#"Converted to List")
in
    #"Converted to Table"

I don't think the Price is included in the API response.

jonasdedual23 · ‎09-24-2023

Finally I have found a workaround for my Problem.

I am using now the code below:

let
    SYMBOLS_LIST = {SYMBOLS_LIST{0}, SYMBOLS_LIST{1}, SYMBOLS_LIST{2}}, 


    AddData = List.Accumulate(SYMBOLS_LIST, Table.FromRecords({}), (table, symbol) =>
        let
            ApiUrl = "https://eodhd.com/api/fundamentals",
            QueryParameters = [
                RelativePath = symbol,
                Query = [
                    api_token = "API-Token",
                    filter = "Financials::Cash_Flow::quarterly"
                ]
            ],
            Source = Json.Document(Web.Contents(ApiUrl, QueryParameters)),
            RecordList = Record.ToList(Source),
            TableFromRecord = Table.FromRecords(RecordList),
            Final = Table.AddColumn(TableFromRecord, "SymbolColumn", each symbol)
        in
            Table.Combine({table, Final})
    ),

    FinalTable = Table.Distinct(AddData),
    #"Sorted Rows" = Table.Sort(FinalTable,{{"SymbolColumn", Order.Ascending}, {"date", Order.Descending}, {"filing_date", Order.Descending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"date", "filing_date", "SymbolColumn", "currency_symbol", "investments", "changeToLiabilities", "totalCashflowsFromInvestingActivities", "netBorrowings", "totalCashFromFinancingActivities", "changeToOperatingActivities", "netIncome", "changeInCash", "beginPeriodCashFlow", "endPeriodCashFlow", "totalCashFromOperatingActivities", "issuanceOfCapitalStock", "depreciation", "otherCashflowsFromInvestingActivities", "dividendsPaid", "changeToInventory", "changeToAccountReceivables", "salePurchaseOfStock", "otherCashflowsFromFinancingActivities", "changeToNetincome", "capitalExpenditures", "changeReceivables", "cashFlowsOtherOperating", "exchangeRateChanges", "cashAndCashEquivalentsChanges", "changeInWorkingCapital", "stockBasedCompensation", "otherNonCa**bleep**ems", "freeCashFlow"}) // Optional: Entfernen von Duplikaten, falls erforderlich
in
    #"Reordered Columns"

I guess there would be a simpleer way of coding but above works for me.

The List "SYMBOLS_LIST " contains the following Entries:

A.US

AA.US

AAPL.US

I get then the below result(not all columns / Rows in this sample:

31.08.2023	A.US	USD	-89000000
26.05.2023	A.US	USD	-77000000
03.03.2023	A.US	USD	-104000000
21.12.2022	A.US	USD	-3000000
27.07.2023	AA.US	USD	-120000000
04.05.2023	AA.US	USD	-102000000
23.02.2023	AA.US	USD	-171000000
27.10.2022	AA.US	USD	-138000000
04.08.2023	AAPL.US	USD	437000000
05.05.2023	AAPL.US	USD	2319000000
03.02.2023	AAPL.US	USD	2483000000
28.10.2022	AAPL.US	USD	2806000000

lbendlin · ‎09-18-2023

As the message says - lists are identified via { } and records are identified via [ ] . you need to parse them differently.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Adding Multiple JSON files by Query Editor

Helpful resources

Power BI Dataviz World Championships

FabCon Atlanta 2026

FabCon is coming to Atlanta

Adding Multiple JSON files by Query Editor

Helpful resources

Power BI Dataviz World Championships

FabCon Atlanta 2026