cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shippen70
Helper I
Helper I

Date Limited Dynamic URL

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(), nullnull, 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.  
 
 

shippen70_0-1609088853282.png

 

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

 

1 ACCEPTED SOLUTION
shippen70
Helper I
Helper I

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"

View solution in original post

4 REPLIES 4
shippen70
Helper I
Helper I

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"

View solution in original post

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 :). 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




StefanoGrimaldi
Super User
Super User

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

StefanoGrimaldi_0-1609096015677.png

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. 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




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

shippen70_0-1609096548162.png

 

shippen70_1-1609096600313.png

 

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors