Forum Discussion

AOT's avatar
AOT
Frequent Visitor
6 years ago
Solved

Help with M power query

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.   T...
Need Help
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    6 years ago

    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

AOT's avatar
AOT
Frequent Visitor
6 years ago

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?

 

 
 
v-frfei-msft's avatar
v-frfei-msft
Community Support
6 years ago

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.

 

  • AOT's avatar
    AOT
    Frequent Visitor
    6 years ago

    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())),

    • BA_Pete's avatar
      BA_Pete
      Super User
      6 years ago

      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