How can I get a list of binaries from a dynamic data source

[tecken]

I have a series of urls I want to connect. I wan to retrieve all the connections, which return JSONs, as a table or array of binaries. The urls I have are in an written as a table.

The current code I have allows to retrieve the first of the elements in my url table and looks as follows:

    Source = 
      Web.Contents(Table.AddColumn(
            Web.Page(Web.Contents(FirstUrl)){0}[Data], "URL", each 
                  SecondUrl&
                  [Símbolo]&
                  "?formatted=true&region=ES&interval="&
                  Interval &
                  "&period1="&
                  Number.ToText(TimeStamp1)&
                  "&period2="&
                  Number.ToText(TimeStamp2))[URL]{0})

Part of the url, [Símbolo], depends on another query to another site. As far as I understand it, this can't be done in two steps or I'll get a firewall error, this is why I did the two connections in the same line, which seems to solve my first problem and explains why in the first Web.Contents(FirstUrl) is used before  retrieving Web.Contents(SecondUrl....). This code should be equivalent to:

Source = Web.Contents([URL]{0})

My problem is that I don't know how to retrieve all of the urls that can be obtained for every row in [Símbolo] in one query and get an array of binaries from it.

Thanks in advance.

[tecken]

Yeah, inside the list of binaries there is a JSON file per row and from each of those JSONs I want to extract a series of lists: timestamp, open prices and close price. The final idea is to go through each of the jsons and append each timestamp, open price and close price list, and then convert the list into a table with timestamp, open price and close price. I'm trying to do this with the Yahoo api. This is an example URL:

https://query2.finance.yahoo.com/v8/finance/chart/MRL.MC?formatted=true&includeAdjustedClose=true&interval=1h&period1=1665644400&period2=1697180400&events=capitalGain%5E%25%5E7Cdiv%5E%25%5E7Csplit&useYfid=true&corsDomain=es.finance.yahoo.com

Where MRL.MC is the variable I'm trying to change with the each keyword, [Símbolo] in my first example.

The table I'll end up looks as follows

Simbolo      Binaries       DataInList
COL.MC       binary1         list1
MRL.MC       binary2         list2
RED.MC       binary3         list3
MTS.MC       binary4         list4
LOG.MC       binary5         list5

Another problem I'm facing is that the elements in DataInList is nested and I'm having some trouble placing it back in the table. Each list element look like this:

{{1697439600,1697443200,1697446800,1697450400,1697454000,1697457600,1697461200,1697464800,1697468400,1697643000},{5,309999943,5,309999943,5,309999943,5,329999924,5,349999905,5,380000114,5,364999771,5,364999771,5,375,5,260000229},{5,275000095,5,304999828,5,309999943,5,309999943,5,329999924,5,340000153,5,375,5,360000134,5,364999771,5,260000229}}

 And the final step would be to have a table with the simbol of each company and the timestamp, open and close prices. Like this:

Simbolo     TimeStamp      Open      Close
COL.MC      a timestamp1   price1    price1
COL.MC      a timestamp2   price2    price2
COL.MC      a timestamp3   price3    price3
COL.MC      a timestamp4   price4    price4
COL.MC      a timestamp5   price5    price5
BKT.MC      a timestamp1   price1    price1
BKT.MC      a timestamp2   price2    price2
BKT.MC      a timestamp3   price3    price3
BKT.MC      a timestamp4   price4    price4
BKT.MC      a timestamp5   price5    price5
ACX.MC      a timestamp1   price1    price1
ACX.MC      a timestamp2   price2    price2
ACX.MC      a timestamp3   price3    price3
ACX.MC      a timestamp4   price4    price4
ACX.MC      a timestamp5   price5    price5

I'm trying to unnest the lists with the following snippet:

    test = companiesTable[lists],
    combineLists = 
        List.Accumulate(
            {0 .. List.Count(test)-1},
            {},
            (current,counter) =>
                List.Combine(
                    {
                        current,
                        {List.Combine(List.Zip(test){counter})}
                    }
                )
        )

Which brings a level up the lists of the first lists of lists elements a level up resulting in a list like:

{{timestamp},{timestamp},{timestamp},{timestamp},{timestamp},{open},{open},{open},{open},{open},{close},{close},{close},{close},{close}}

But isn't the final result I'm trying to accomplish as I said above, I need to bring the lists elements a level up again or group only the timstamps with the timestamps, open with open, close with close and then turn each list into a table.

The code to read each json is already written, it's this function:

(json) as list => 
let
    jsonParsed = Json.Document(json),
    jsonData = jsonParsed[chart][result]{0},
    
    indicators = jsonData[indicators][quote]{0},
    
    timestamp = jsonData[timestamp],
    listOpen = indicators[open],
    listClose = indicators[close]
in
    {timestamp, listClose, listOpen}

And to create the column with the lists I've written this:

datos = Table.AddColumn(jsonTable, "lists", each json2list([Data]))

So there you have it, that is the full process I'm trying to accomplish.

[lbendlin]

The "array" of binaries  (the list, basically) can only be an intermediate result. What are you ultimately trying to achieve?  Can you provide some sample URLs?

[tecken]

Hi I wrote another comment above but I'm not sure if you received it or if I answered to the whole post, check the question to see it.

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcvb30fN1VorViVbyDYIzg1xd4KIhwTCmj787mBkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Simbolo = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Binary", each Web.Contents("https://query2.finance.yahoo.com/v8/finance/chart/",[RelativePath = [Simbolo],Query=[
    formatted="true",
    includeAdjustedClose="true",
    interval="1h",
    period1="1665644400",
    period2="1697180400", 
    events="capitalGain%5E%25%5E7Cdiv%5E%25%5E7Csplit",
    useYfid="true"]])),
    GetLists = (Binary)=>
    let
        result1 = Json.Document(Binary,65001)[chart][result]{0},
        combined = List.Zip({result1[timestamp],result1[indicators][quote]{0}[open],result1[indicators][quote]{0}[close]}),
        #"Converted to Table" = Table.FromList(combined, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Timestamp", "Open", "Close"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Timestamp", Int64.Type}, {"Open", type number}, {"Close", type number}}),
        #"Added Custom1" = Table.AddColumn(#"Changed Type", "Date", each #datetime(1970,1,1,0,0,0) + #duration(0,0,0,[Timestamp]),type datetime)
    in
        #"Added Custom1",
    AddedList = Table.AddColumn(#"Added Custom","Data", each GetLists([Binary])),
    #"Removed Other Columns" = Table.SelectColumns(AddedList,{"Simbolo", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Timestamp", "Open", "Close", "Date"}, {"Timestamp", "Open", "Close", "Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Data",{{"Open", type number}, {"Close", type number}, {"Date", type datetime}, {"Timestamp", Int64.Type}})
in
    #"Changed Type"

[tecken]

Amazing! Thank you so much!