Forum Discussion

shippen70's avatar
shippen70
Helper I
5 years ago
Solved

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 pul...
Need Help
  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    5 years ago

    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. 

StefanoGrimaldi's avatar
StefanoGrimaldi
Resident Rockstar
5 years ago

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. 

StefanoGrimaldi's avatar
StefanoGrimaldi
Resident Rockstar
5 years ago

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

  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    Resident Rockstar
    5 years ago

    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