Forum Discussion

Shelley's avatar
Shelley
Icon for Post Prodigy rankPost Prodigy
8 years ago
Solved

Limit dateset by timeframe in Query Editor

Hi All, Man, I just don't get the M Language. Looking for help please.

I want to limit our dataset by the create date. I want everything five years backwards from today. The built-in selections don't seem to do this. The previous five years, doesn't include any of this year's data. I want today minus five years - every time the data is refreshed. I tried this:

 

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Order_Submit_Date] > (Date.AddYears(DateTime.LocalNow, -5)) as datetime)

 

I'm sure I've got something boogered up, but I'm not sure what it is. The error I'm receiving is: 

Expression.Error: The Date value must contain the Date component.
Details:
Function

 

Thanks! ImkeF is this an easy one for you to answer?

Need Help
  • ImkeF's avatar
    ImkeF
    8 years ago

    This error message usually means that you're missing a closing parenthesis :)

     

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Order_Submit_Date] > Date.AddYears(Date.From(DateTime.LocalNow()), -5) )

4 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Agree that this should be easier:

     

    Date.AddYears(Date.From(DateTime.LocalNow()), -5)
    • Shelley's avatar
      Shelley
      Icon for Post Prodigy rankPost Prodigy
      8 years ago

      Thanks ImkeF!

       

      I still have something wrong. Here's the whole advanced editor window. Now I'm receiving a different error:

      Expression.SyntaxError: Token Comma expected.

       

      I don't see where there should be a comma. Do I still have something written wrong in the code?

       

      let
      Source = Sql.Database("csmdataservice.cloudapp.net", "csm_master"),
      dbo_RepairTransaction = Source{[Schema="dbo",Item="RepairTransaction"]}[Data],
      #"Added Custom" = Table.AddColumn(dbo_RepairTransaction, "Custom", each if Text.Start([Contract_Number],1)="0" then Text.TrimStart([Contract_Number],"0") else [Contract_Number]),
      #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Contract_Number"}),
      #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Contract_Number"}}),
      #"Inserted Merged Column" = Table.AddColumn(#"Renamed Columns1", "Key", each Text.Combine({[End_Customer_Key], [Contract_Number]}, "|"), type text),
      #"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Key", "%BPIDContractKey"}}),
      #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"List_Price", Currency.Type}, {"Net_price", Currency.Type}, {"Calculated_List_Price", Currency.Type}, {"Unit_Received_Date", type date}, {"Order_Submit_Date", type date}}),
      #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Order_Submit_Date] > Date.AddYears(Date.From(DateTime.LocalNow()), -5)
      in
      #"Filtered Rows"

       

      Thanks! You're awesome.

       

      • ImkeF's avatar
        ImkeF
        Icon for Community Champion rankCommunity Champion
        8 years ago

        This error message usually means that you're missing a closing parenthesis :)

         

        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Order_Submit_Date] > Date.AddYears(Date.From(DateTime.LocalNow()), -5) )