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

Web Content Dynamic URL

I am using Power Query to pull Json data from a website.  I need to change the string each day to pull the data from the current date (noted in underlined red italic below). I have setup the data pull in a PowerApps Dataflow.  I first tried to setup a parameter based on a list value generated via query.  I could not get the value to update based on the parameter when the query was refreshed.  There appears to be a lot of threads with similar issues without resolution.

 

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"))
 
Any assistance is approcaiated on the best way to approach this.
 
The second part of the isue is that there is not data for every day.  This is HR data so there often is no data on Saturday or Sunday.  I have a Date Table where I could lookup the last working day and go back to that day if there are no values returned.  It seems as if I'm going to have the same issue with the parameter above.
1 ACCEPTED SOLUTION
StefanoGrimaldi
Super User
Super User

that could work you can create a new table to get no working days, compare it with the today date before its converted to tex\list, and when its a non working date them you can make it subtract the difference of today and last working day to get the last working date. somehting like that. I would recommend open a new topic ( if the main one of the date on url its done mark as soluction if it helped) for this las problem as its different from the main topic here, and its worth getting more perspective on it. 





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

Proud to be a Super User!




View solution in original post

15 REPLIES 15
StefanoGrimaldi
Super User
Super User

that could work you can create a new table to get no working days, compare it with the today date before its converted to tex\list, and when its a non working date them you can make it subtract the difference of today and last working day to get the last working date. somehting like that. I would recommend open a new topic ( if the main one of the date on url its done mark as soluction if it helped) for this las problem as its different from the main topic here, and its worth getting more perspective on it. 





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

Proud to be a Super User!




View solution in original post

StefanoGrimaldi
Super User
Super User

you mean with your url it pulls 0 data and so returns a blank or invalid result? for this when you configure the auto refresh on power bi service you can specify it not to refresh on weekend (select a weekly refresh and specify the days it will refresh the data, them on weekend will remain the data of friday):

StefanoGrimaldi_0-1609039441921.png

to try if the soluction works you can add a step to add a couple days on the date before the add new column step to try a future date on the query. 





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

Proud to be a Super User!




Because there is no data being extracted it is not building the table.  If I change the date to a day that has data the extract works fine and the table is built in the query.

 

shippen70_0-1609039767650.png

 

ok, them yes, the work around this its to avoid the query to refresh on weeken by disable the sunday and saturday in the program refresh (setted as weekly).





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

Proud to be a Super User!




@StefanoGrimaldi The answer is not that simple because every weekday is not a working day.  Fore instance 12/24 (thursday) and 12/25 (friday) were non-working days because of Christmas .  I have a couple thoughts.  One option might be to use my date table to find the last working day, if today is not a working day then use the last working day.

StefanoGrimaldi
Super User
Super User

and a little correction in the syntaxys here: 

 

"https://timestar.insperity.com/stellind/services/timesimplicity/time?start_date="&tabledatenewquery...&stop_date=2021-01-31&company_id=1"

 

forgot to add the "" before and after the reference so it identies the segments for the reference to work. 





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

Proud to be a Super User!




@StefanoGrimaldi   Your code seems to be working.  Since today is a weekend there is no data so the table is erroroing out.  Any thoughts on the best way to approach the last working day?

StefanoGrimaldi
Super User
Super User

my bad, bad habbit of coding in both languague xD: 

here you go: 

let
Source = DateTime.LocalNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Converted to Table", "Column1", "Column1 - Copy"),
#"Extracted Date" = Table.TransformColumns(#"Duplicated Column",{{"Column1", DateTime.Date, type date}}),
#"Extracted Time" = Table.TransformColumns(#"Extracted Date",{{"Column1 - Copy", DateTime.Time, type time}}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Time",{"Column1 - Copy"}),
#"Parsed Date" = Table.TransformColumns(#"Removed Columns",{{"Column1", each Date.From(DateTimeZone.From(_)), type date}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Parsed Date",{{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each [#"Column1.3"]&"-"&[#"Column1.1"]&"-"&[#"Column1.2"]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
Custom = #"Removed Other Columns"{0}[Custom]
in
Custom

 

StefanoGrimaldi_0-1609037803254.pngthis a little example of how to insert the table value as a reference in your M code as described above. 

 





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

Proud to be a Super User!




shippen70
Helper I
Helper I

Thank you @StefanoGrimaldi.  Part of the M code did not translate to english.  Can you repost your code in English.

sorry, here you go the code all in english: 

let
Source = DateTime.LocalNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Converted to Table", "Column1", "Column1 - Copy"),
#"Extracted Date" = Table.TransformColumns(#"Duplicated Column",{{"Column1", DateTime.Date, type date}}),
#"Extracted Time" = Table.TransformColumns(#"Extracted Date",{{"Column1 - Copy", DateTime.Time, type time}}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Time",{"Column1 - Copy"}),
#"Parsed Date" = Table.TransformColumns(#"Removed Columns",{{"Column1", each Date.From(DateTimeZone.From(_)), type date}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Parsed Date",{{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each [#"Column1.3"]&"-"&[#"Column1.1"]&"-"&[#"Column1.2"]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
Custom = #"Removed Other Columns"{0}[Custom]
in
Custom

 

and a little example of using the new table as part of the code:

StefanoGrimaldi_0-1609037938490.png

 





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

try this (lets hope it work for your case :P): 

first create a new table to get the date value as a text of today date as follow: 

let
Source = DateTime.LocalNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Converted to Table", "Column1", "Column1 - Copy"),
#"Extracted Date" = Table.TransformColumns(#"Duplicated Column",{{"Column1", DateTime.Date, type date}}),
#"Extracted Time" = Table.TransformColumns(#"Extracted Date",{{"Column1 - Copy", DateTime.Time, type time}}),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Time",{{"Column1", type text}, {"Column1 - Copy", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Ultima Fecha/hora actualización datos", each [Column1]&" "&[#"Column1 - Copy"]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1", "Column1 - Copy"}),
#"Parsed Date" = Table.TransformColumns(#"Removed Columns",{{"Ultima Fecha/hora actualización datos", each Date.From(DateTimeZone.From(_)), type date}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Parsed Date",{{"Ultima Fecha/hora actualización datos", type text}}),
#"Ultima Fecha/hora actualización datos1" = #"Changed Type1"{0}[#"Ultima Fecha/hora actualización datos"]
in
#"Ultima Fecha/hora actualización datos1"

 

them go to advance editor for the original table and set your path as follow:

https://timestar.insperity.com/stellind/services/timesimplicity/time?start_date=&tabledatenewquery&&stop_date=2021-01-31&company_id=1

tabledatenewquery in this reference goes the name of the above table you created, that table will get the actual date when query refresh parse the date to a text format and them send that reference to your URL,

 

if this helped please let me know and mark it as soluction, any futher assitence will be glad to help. 





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

Proud to be a Super User!




you may need to do some alteration to that query to match the date format needed by your URL





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

Proud to be a Super User!




try this for the date table will put the date in the same format you needind it: 

let
Source = DateTime.LocalNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Converted to Table", "Column1", "Column1 - Copy"),
#"Extracted Date" = Table.TransformColumns(#"Duplicated Column",{{"Column1", DateTime.Date, type date}}),
#"Extracted Time" = Table.TransformColumns(#"Extracted Date",{{"Column1 - Copy", DateTime.Time, type time}}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Time",{"Column1 - Copy"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Ultima Fecha/hora actualización datos"}}),
#"Parsed Date" = Table.TransformColumns(#"Renamed Columns",{{"Ultima Fecha/hora actualización datos", each Date.From(DateTimeZone.From(_)), type date}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Parsed Date",{{"Ultima Fecha/hora actualización datos", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Ultima Fecha/hora actualización datos", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Ultima Fecha/hora actualización datos.1", "Ultima Fecha/hora actualización datos.2", "Ultima Fecha/hora actualización datos.3"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each [#"Ultima Fecha/hora actualización datos.3"]&"-"&[#"Ultima Fecha/hora actualización datos.2"]&"-"&[#"Ultima Fecha/hora actualización datos.1"]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
Custom = #"Removed Other Columns"{0}[Custom]
in
Custom





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

Proud to be a Super User!




shippen70
Helper I
Helper I

let
  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"
StefanoGrimaldi
Super User
Super User

hey, 

 

can you please post your Power Query M code? at least the segment that manage the file path Pull. 





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

Proud to be a Super User!




Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors