Recursive function data sources

[Alberto_devper]

Hello!

I have a problem combining various files into one table calling a recursive function. I want to get from a webpage some files that are updated daily and their names are just the date they are updated. So I decided to create a function that is increasing a date variable and a table content. Until it reaches the endDate. The problem is right on the code that is calling the external table when I call the function this is the output it throws

Formula.Firewall: Query 'Invoked Function' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Changing the bold piece of code for a local table makes it work perfectly, combining those 2 tables as many times as is called the function. Any suggestions?

Thanks!

(startDate as date, endDate as date, data as table) as table =>
let
sDate = Date.AddDays(startDate, -1),
currentData = 
Table.Combine(
{
data, 
Csv.Document(
Web.Contents(
#"Github link"(
if(Time.Hour(DateTime.LocalNow()) >= 0 and Time.Hour(DateTime.LocalNow()) < 19) then 
Date.AddDays(
Date.From(DateTime.LocalNow()), 
-2
) 
else 
Date.AddDays(
Date.From(DateTime.LocalNow()), 
-1
)
)
),
[Delimiter=",", Columns=14, Encoding=65001, QuoteStyle=QuoteStyle.None]
)
}
),
loopCheck = 
if(sDate <= endDate) then
currentData
else
@Query1(sDate, endDate, currentData)

in
loopCheck

[lbendlin]

if you think about it this is not so much a recursive function as an iterator. (recursive functions can crawl down multiple levels, iterators stay on the same level)

so an alternative is to list.generate the github URLs (based on the same cutoff criteria), and then call web.contents() against the list and combine the results.

[Alberto_devper · Accepted solution]

Thank you for the suggestion @lbendlin. I thought about iterating inside one "step" but I didn't fully understand how to do it. After you suggested to use List.Generate(), I started to read about it and try to implement it and finally it worked, this one is the solution that works if anyone is having the same problem. I strongly recommend this page List.Generate() explanation to understand what is going on inside this "method".

let
startDateTime = DateTime.LocalNow(),
//First date reported
endDate = #date(2020,01,22),
currentData = 
List.Generate(
() => 
[x = Date.From(startDateTime), 
y = #table(
type table [
Column1 = text, 
Column2 = text, 
Column3 = text, 
Column4 = text, 
Column5 = text, 
Column6 = text, 
Column7 = text, 
Column8 = text, 
Column9 = text, 
Column10 = text, 
Column11 = text, 
Column12 = text, 
Column13 = text, 
Column14 = text
], 
{}
)
],

each [x] > endDate, 
each [
x = Date.AddDays([x], -1), 
y = Table.Combine(
{
[y], 
Table.AddColumn(Csv.Document(
Web.Contents(
#"Github link"(
if(Time.Hour(startDateTime) >= 0 and Time.Hour(startDateTime) < 19 and x = Date.From(startDateTime)) then 
Date.AddDays(
Date.From(x), 
-2
) 
else 
Date.AddDays(
Date.From(x), 
-1
)
)
),
[Delimiter=",", Columns=14, Encoding=65001, QuoteStyle=QuoteStyle.None]
), "Reference_Date", each x)
}
)
], 
each [y]
),
lastTable = List.Last(currentData),
#"Renamed Columns" = Table.RenameColumns(lastTable,{{"Column1", "FIPS"}, {"Column2", "Admin2"}, {"Column3", "Province_State"}, {"Column4", "Country_Region"}, {"Column5", "Last_Update"}, {"Column6", "Lat"}, {"Column7", "Long_"}, {"Column8", "Confirmed"}, {"Column9", "Deaths"}, {"Column10", "Recovered"}, {"Column11", "Active"}, {"Column12", "Combined_Key"}, {"Column13", "Incident_Rate"}, {"Column14", "Case_Fatality_Ratio"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([FIPS] <> "FIPS" and [FIPS] <> "Province/State")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",".",",",Replacer.ReplaceText,{"Lat", "Long_", "Confirmed", "Deaths", "Recovered", "Active", "Incident_Rate", "Case_Fatality_Ratio"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Long_", type number}, {"Confirmed", Int64.Type}, {"Deaths", Int64.Type}, {"Recovered", Int64.Type}, {"Active", Int64.Type}, {"Incident_Rate", type number}, {"Case_Fatality_Ratio", Percentage.Type}, {"Reference_Date", type date}, {"Last_Update", type datetime}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type1","US","United States of America",Replacer.ReplaceText,{"Country_Region"}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Replaced Value1", {{"Last_Update", null}, {"Case_Fatality_Ratio", null}})
in
#"Replaced Errors"