Forum Discussion

shippen70's avatar
shippen70
Icon for Helper I rankHelper 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
Icon for Resident Rockstar rankResident 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
Icon for Resident Rockstar rankResident 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