Date Limited Dynamic URL

[shippen70]

I am creating a Query by pulling Json from a website.  The JSon needs to have a dynamic start date (indicated in Red Italics below).  I can make this work to create a query and assign today's date.  Problem is that the data source only gets data on "Working Days" so the table erros out on the days which there is no data.

Static Query

let

  Source = Json.Document(Web.Contents("https://timestar.insperity.com/stellind/services/timesimplicity/time?start_date=2020-12-22&stop_date=2021-01-31&company_id=1")),

  data = Source[data],

  #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

  #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"employee_id", "effective_date", "start_category", "start_timedate", "stop_category", "stop_timedate", "total_minutes", "hours", "pay_type"}, {"employee_id", "effective_date", "start_category", "start_timedate", "stop_category", "stop_timedate", "total_minutes", "hours", "pay_type"})

in

  #"Expanded Column1"

I created a Holiday "Holiday Calendar" and Date Table "Rolling Calendar" where I identify working days and return the last working day to ensure there is data available to create the query table.  As soon as I do this I get Formula.Firewall error.  

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

I found this blog which seems to explain what is happening.  I have seperated the merge of my Holiday and Date tables into a seperate query "Rolling Calendar Source" and "Rolling Calendar Merged" and then reference the new table "Rolling Calendar Merged" but still get the same error.  If I remove the reference to the Date table reference from the URL the query works again.

Any thoughts on getting this to work are appriciated.

Dynamic Query

let
Source = Json.Document(Web.Contents("https://timestar.insperity.com/stellind/services/timesimplicity/time?start_date= "& #"Rolling Calendar Merged" &" &stop_date=2021-01-31&company_id=1")),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"employee_id", "effective_date", "start_category", "start_timedate", "stop_category", "stop_timedate", "total_minutes", "hours", "pay_type"}, {"employee_id", "effective_date", "start_category", "start_timedate", "stop_category", "stop_timedate", "total_minutes", "hours", "pay_type"})
in
#"Expanded Column1"

Holiday Calendar "M"

let
Source = Excel.Workbook(Web.Contents("https://stellarindustries-my.sharepoint.com/personal/stacey_hippen_stellarind_net/Documents/AppData/..."), null, true),
tabHoliday_Table = Source{[Item="tabHoliday",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(tabHoliday_Table,{{"Date1st", type date}, {"Date2nd", type date}, {"Holiday", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInCurrentMonth([Date1st]) or Date.IsInPreviousMonth([Date1st]) or Date.IsInNextMonth([Date1st]))
in
#"Filtered Rows"

Rolling Calendar Source "M"

let
Source = #date (2020,10,1),
Custom1 = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source)+0 ,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Calendar Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Calendar Date", type date}}),
#"Insert Day of Week" = Table.AddColumn(#"Changed Type", "Day of Week", each Date.DayOfWeek([Calendar Date]) +1)

in
#"Insert Day of Week"

Rolling Calendar Merged "M"

let
Source = #"Rolling Calendar Source",

#"Merged Queries" = Table.NestedJoin(#"Rolling Calendar Source", {"Calendar Date"}, #"Holiday Calendar", {"Date1st"}, "Holiday Calendar", JoinKind.LeftOuter),
#"Expanded Holiday Calendar" = Table.ExpandTableColumn(#"Merged Queries", "Holiday Calendar", {"Holiday"}, {"Holiday"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Holiday Calendar",null,"0",Replacer.ReplaceValue,{"Holiday"}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Is Working Day", each if not Text.StartsWith([Holiday], "0") then 0 else if [Day of Week] = 1 then 0 else if [Day of Week] = 7 then 0 else 1),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Is Working Day", type logical}}),
#"Filtered Rows - Working Days true" = Table.SelectRows(#"Changed Type1", each ([Is Working Day] = true)),
#"Kept Last Rows" = Table.LastN(#"Filtered Rows - Working Days true", 2),
#"Kept First Rows" = Table.FirstN(#"Kept Last Rows",1),
#"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Calendar Date"}),
#"Extracted Date" = Table.TransformColumns(#"Removed Other Columns",{}),
#"Changed Type2" = Table.TransformColumnTypes(#"Extracted Date",{{"Calendar Date", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type2", "Calendar Date", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Calendar Date.1", "Calendar Date.2", "Calendar Date.3"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each [#"Calendar Date.3"] &"-"& [#"Calendar Date.1"] &"-"& [#"Calendar Date.2"]),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Custom", "StartDate"}}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"StartDate"}),
Custom2 = #"Removed Other Columns1"{0}[StartDate]
in
Custom2

[shippen70 · Accepted solution]

I was able to resolve the issue by resturcting the entire query.  I first built a date table to determine the last working day.  After transforming the date I added a column that created the dynamic URL based on the date created.  From there I was able to query the website and return the JSon for the established date range.

The below link got me thinking on the right path.  Thanks also to @StefanoGrimaldi for the assiston getting the the inital concept together.

https://community.powerbi.com/t5/Power-Query/API-call-datelimit/m-p/1463943#M45613

Dynamic URL "M"

let
Source = Date.From(Date.AddDays( DateTime.FixedLocalNow(), -10 )),

Custom1 = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source)+0 ,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Calendar Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Calendar Date", type date}}),
#"Insert Day of Week" = Table.AddColumn(#"Changed Type", "Day of Week", each Date.DayOfWeek([Calendar Date]) +1),
#"Merged Queries" = Table.NestedJoin(#"Rolling Calendar Source", {"Calendar Date"}, #"Holiday Calendar", {"Date1st"}, "Holiday Calendar", JoinKind.LeftOuter),
#"Expanded Holiday Calendar" = Table.ExpandTableColumn(#"Merged Queries", "Holiday Calendar", {"Holiday"}, {"Holiday"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Holiday Calendar",null,"0",Replacer.ReplaceValue,{"Holiday"}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Is Working Day", each if not Text.StartsWith([Holiday], "0") then 0 else if [Day of Week] = 1 then 0 else if [Day of Week] = 7 then 0 else 1),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Is Working Day", type logical}}),
#"Filtered Rows - Working Days true" = Table.SelectRows(#"Changed Type1", each ([Is Working Day] = true)),
#"Kept Last Rows" = Table.LastN(#"Filtered Rows - Working Days true", 2),
#"Kept First Rows" = Table.FirstN(#"Kept Last Rows",1),
#"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Calendar Date"}),
#"Extracted Date" = Table.TransformColumns(#"Removed Other Columns",{}),
#"Changed Type2" = Table.TransformColumnTypes(#"Extracted Date",{{"Calendar Date", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type2", "Calendar Date", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Calendar Date.1", "Calendar Date.2", "Calendar Date.3"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each [#"Calendar Date.3"] &"-"& [#"Calendar Date.1"] &"-"& [#"Calendar Date.2"]),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Custom", "StartDate"}}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"StartDate"}),
#"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "URL", each "https://timestar.insperity.com/stellind/services/timesimplicity/time?start_date= "&[StartDate]&" &stop_date=2021-01-31&company_id=1"),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom1",{{"URL", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type4",{"StartDate"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each Json.Document(Web.Contents([URL]))),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom2", "Custom", {"type", "status", "data"}, {"type", "status", "data"}),
#"Removed Other Columns2" = Table.SelectColumns(#"Expanded Custom",{"data"}),
#"Expanded data" = Table.ExpandListColumn(#"Removed Other Columns2", "data"),
#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"employee_id", "effective_date", "start_category", "start_timedate", "stop_category", "stop_timedate", "total_minutes", "hours", "pay_type", "org_levels"}, {"employee_id", "effective_date", "start_category", "start_timedate", "stop_category", "stop_timedate", "total_minutes", "hours", "pay_type", "org_levels"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded data1",{"org_levels"})
in
#"Removed Columns1"

[StefanoGrimaldi]

nice, grald you could solve it, mark your answer as solution for future people that may have a similar question to be able to find it and use it as reference, and if any post was of help give some kudos :). 

[StefanoGrimaldi]

just a quick observation (will try to see if can get this idea working its interesting):

notice the " its before the & you have "& #"Rolling Calendar Merged" &"  but I think it should be &" #"Rolling Calendar Merged" &" 

cause you need the " after the & for the merge reference to take place. 

[shippen70]

@StefanoGrimaldi  I tried moving the Quotation Marks but it does not work