Forum Discussion
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.
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.
15 Replies
hey,
can you please post your Power Query M code? at least the segment that manage the file path Pull.
- letSource = 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"
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.
you may need to do some alteration to that query to match the date format needed by your URL
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
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
Customand a little example of using the new table as part of the code:
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
Customthis a little example of how to insert the table value as a reference in your M code as described above.
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.
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?
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):
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.
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.
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).
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.
