Forum Discussion
Solved
How can I get a list of binaries from a dynamic data source
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 co...
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"
lbendlin
Super User
Super UserThe "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?
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
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"Amazing! Thank you so much!