Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need to filter a column have dates between DateTime.FixedLocalNow()) and Date.AddDays((DateTime.FixedLocalNow()), 91)).
But I cannot seem to get the right M query script.
Please help.
This is currently what I have done. But it doesn't seem to filter the column properly. The column is [end_date]
#"Filtered Rows2" = Table.SelectRows(#"Changed Type", each [end_date] > DateTime.FixedLocalNow()),
#"Filtered Rows3" = Table.SelectRows(#"Changed Type", each [end_date] < Date.AddDays((DateTime.FixedLocalNow()), 91))
Solved! Go to Solution.
Hi @AOT
Not sure if I've understood your requirement correctly, but I believe this should work as a replacement for your #"Filtered Rows2" step:
#"Filtered Rows2" = Table.SelectRows(#"Extracted Date1", each Date.From([end_date]) = Date.From(DateTime.FixedLocalNow()) or Date.IsInNextNDays(Date.From([end_date]), 91)),
I've used the Date.From() surrounds on the [end_date] column refs so you aren't required to use your "Extracted Date" step if you don't want to.
Remember to remove the comma at the end if this is the last step in your query
Pete
Proud to be a Datanaut!
Hi @AOT ,
If you want to make your code work, you should change your date to date.time. Or you can update your M code as below.
= Table.SelectRows(#"Changed Type", each [Date] >= Date.From(DateTime.FixedLocalNow()) and [Date] <= Date.From(Date.AddDays((DateTime.FixedLocalNow()), 91)))
Full code for your reference as well.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc65DcAwDMXQXVQHsL7sHJ5F8P5ruEgQUOUDG2aamlq4pq0jLYhODOIkLuImHmIS8qLyoDLxpnDsfejE+LE2", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >= Date.From(DateTime.FixedLocalNow()) and [Date] <= Date.From(Date.AddDays((DateTime.FixedLocalNow()), 91)))
in
#"Filtered Rows"
Hi @v-frfei-msft ,
When I put in the step, there are no syntax errors but I keep on getting this error. Do you know what this means?
Hi @AOT ,
Could you please share your sample data or pbix to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Hi @v-frfei-msft ,
This is the code so far
Source = Impala.Database("impalahs2dr.unix.marksandspencer.com:25003"),
spotfire_reporting_views_Schema = Source{[Name="spotfire_reporting_views",Kind="Schema"]}[Data],
t_irp_phase_new_sc_Table = spotfire_reporting_views_Schema{[Name="t_irp_phase_new_sc",Kind="Table"]}[Data],
#"Added Conditional Column" = Table.AddColumn(t_irp_phase_new_sc_Table, "articlev", each if [variant_no] = null then [article_id] else [variant_no]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"start_date", type datetime}, {"end_date", type datetime}}),
#"Extracted Date" = Table.TransformColumns(#"Changed Type",{{"start_date", DateTime.Date, type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Extracted Date", each [start_date] > Date.From(DateTime.FixedLocalNow())),
#"Extracted Date1" = Table.TransformColumns(#"Filtered Rows1", {{"end_date", DateTime.Date, type date}}),
#"Filtered Rows2" = Table.SelectRows(#"Extracted Date1", each [end_date] <= Date.From(Date.AddDays((DateTime.FixedLocalNow()), 84)) and [end_date] >= Date.From(DateTime.FixedLocalNow())),
Hi @AOT
Not sure if I've understood your requirement correctly, but I believe this should work as a replacement for your #"Filtered Rows2" step:
#"Filtered Rows2" = Table.SelectRows(#"Extracted Date1", each Date.From([end_date]) = Date.From(DateTime.FixedLocalNow()) or Date.IsInNextNDays(Date.From([end_date]), 91)),
I've used the Date.From() surrounds on the [end_date] column refs so you aren't required to use your "Extracted Date" step if you don't want to.
Remember to remove the comma at the end if this is the last step in your query
Pete
Proud to be a Datanaut!
Hi @AOT
If your column is a date data type then try adding Date.From( DateTime.FixedLocalNow() ) around this should work.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.