Forum Discussion
Webscraping - Change the date filter
Hello!
I want to do a webscrap in this website: Preços Históricos de GBP/JPY - Investing.com Portugal
But there is a date filter in it and i need to set the final date to today's date. How can i do this?
Thanks!
Hi EusebioJr_BR ,
If you are trying to always automatically query data 30 days forward from the current day's date, you can try the following M code:= Table.SelectRows(#"Changed Type", each [Date] >= Date.AddDays(DateTime.Date(DateTime.LocalNow()), -30) and [Date] <= DateTime.Date(DateTime.LocalNow()))
Here is the whole M code in the Advanced Editor:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdQ9igMxEEThuyg2jKpb85c6MTiadDFz/2vsJq4GvQ3Fi/Sh0ufTXs/rff20R1NftC/RY/wdxtruxxQ3x8G4OibjcAzGdBRjOHZGfWOejN3xQDzddrTDbUMzT5LHOkkd4yRxbJO0MU2SxjL5j8y3xQRzLmmXOOYWdokdzS6xodklVjS7xECzSySaXSLQ7BJCs0t0NLsILmEXwUV2EVxkF8Gl5iS41JoElxqT4FJbElxqSoJLLUlwqSGBpWYEFaPAxCQQMQg86nOZS41nLvVE8Mp92Xbfvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Moeda = _t, Date = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Moeda", type text}, {"Date", type date}, {"Value", Int64.Type}}), FilteredRows = Table.SelectRows(#"Changed Type", each [Date] >= Date.AddDays(DateTime.Date(DateTime.LocalNow()), -30) and [Date] <= DateTime.Date(DateTime.LocalNow())) in FilteredRows
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi EusebioJr_BR ,
Assuming this is your datasheet:You can use this DAX to create a calculated table called Calendar:
Calendar = CALENDAR(MINX(ALL('Table'), 'Table'[Date]), TODAY())Then create relationship:
Now you can use the Calendar table to create a slicer:Every day when you reopen or refresh the report, the right side of the slicer will automatically change to the current day's date.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Sorry, I wasn't clear in my question. I need the query to bring the current date. Currently when I update the information the query brings me information from the last 30 days. For example: from 09/01 to the present day. Below is the example of how the base comes (always from the last 30 days). In short: I need to lock the start date and the current date always to today.
Hi EusebioJr_BR ,
If you are trying to always automatically query data 30 days forward from the current day's date, you can try the following M code:= Table.SelectRows(#"Changed Type", each [Date] >= Date.AddDays(DateTime.Date(DateTime.LocalNow()), -30) and [Date] <= DateTime.Date(DateTime.LocalNow()))
Here is the whole M code in the Advanced Editor:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdQ9igMxEEThuyg2jKpb85c6MTiadDFz/2vsJq4GvQ3Fi/Sh0ufTXs/rff20R1NftC/RY/wdxtruxxQ3x8G4OibjcAzGdBRjOHZGfWOejN3xQDzddrTDbUMzT5LHOkkd4yRxbJO0MU2SxjL5j8y3xQRzLmmXOOYWdokdzS6xodklVjS7xECzSySaXSLQ7BJCs0t0NLsILmEXwUV2EVxkF8Gl5iS41JoElxqT4FJbElxqSoJLLUlwqSGBpWYEFaPAxCQQMQg86nOZS41nLvVE8Mp92Xbfvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Moeda = _t, Date = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Moeda", type text}, {"Date", type date}, {"Value", Int64.Type}}), FilteredRows = Table.SelectRows(#"Changed Type", each [Date] >= Date.AddDays(DateTime.Date(DateTime.LocalNow()), -30) and [Date] <= DateTime.Date(DateTime.LocalNow())) in FilteredRows
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Use the next code
let Source = Web.BrowserContents("https://pt.investing.com/currencies/gbp-jpy-historical-data"), #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.freeze-column-w-1.w-full.overflow-x-auto.text-xs.leading-4 > * > TR > :nth-child(1)"}, {"Column2", "TABLE.freeze-column-w-1.w-full.overflow-x-auto.text-xs.leading-4 > * > TR > :nth-child(2)"}, {"Column3", "TABLE.freeze-column-w-1.w-full.overflow-x-auto.text-xs.leading-4 > * > TR > :nth-child(3)"}, {"Column4", "TABLE.freeze-column-w-1.w-full.overflow-x-auto.text-xs.leading-4 > * > TR > :nth-child(4)"}, {"Column5", "TABLE.freeze-column-w-1.w-full.overflow-x-auto.text-xs.leading-4 > * > TR > :nth-child(5)"}, {"Column6", "TABLE.freeze-column-w-1.w-full.overflow-x-auto.text-xs.leading-4 > * > TR > :nth-child(6)"}, {"Column7", "TABLE.freeze-column-w-1.w-full.overflow-x-auto.text-xs.leading-4 > * > TR > :nth-child(7)"}}, [RowSelector="TABLE.freeze-column-w-1.w-full.overflow-x-auto.text-xs.leading-4 > * > TR"]), #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Data", type date}, {"Último", Int64.Type}, {"Abertura", Int64.Type}, {"Alta", Int64.Type}, {"Baixa", Int64.Type}, {"Vol.", type text}, {"Var. %", Percentage.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInCurrentDay([Data])) in #"Filtered Rows"If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos.
Thank you!
Sorry, I wasn't clear in my question. I need the query to bring the current date. Currently when I update the information the query brings me information from the last 30 days. For example: from 09/01 to the present day. Below is the example of how the base comes (always from the last 30 days). In short: I need to lock the start date and the current date always to today.
