Forum Discussion

sakamotothais's avatar
sakamotothais
Icon for Helper III rankHelper III
8 years ago
Solved

filter automatic date in power BI language M

Hi,   Need help. I'm trying to leave my auto filter, so it updates itself. My connection is direct and I am using the language M I want it to always select the current date today, the date fro...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    You can take a look at my post again.

     

    So my suggestion would be to adjust your code to take the date from your BookingDate; untested:

     

    let
    Source = Sql.Database("10.0.6.70,1433", "DailyBookings34"),
    DMBookings_BookingByLeg = Source{[Schema="DMBookings",Item="BookingByLeg"]}[Data],
    #"Filtered Rows" = Table.SelectRows(DMBookings_BookingByLeg, each Date.From([BookingDate]) = Date.From (DateTime.LocalNow ()) or Date.From([BookingDate]) = Date.AddDays (Date.From (DateTime.LocalNow ()) , -1) or Date.From([BookingDate]) = Date.AddDays (Date.From (DateTime.LocalNow ()), -7))
in
    #"Filtered Rows"
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
8 years ago

Please share your code, so we can see where the error comes from.

 

That would be a better idea than cross posting on Technet.

sakamotothais's avatar
sakamotothais
Icon for Helper III rankHelper III
8 years ago
each [BookingDate] = Date.From (DateTime.LocalNow ()) ou [BookingDate] = Date.AddDays (Date.From (DateTime.LocalNow ()) , -1) ou [BookingData] = Data.AddDays (Date.From (DateTime.LocalNow ()), - 7) )

 

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

    It doesn't look like code that would result in the error you mentioned:

    ou is an unknown keyword in M, you use different fieldnames "BookingData" and "BookingDate"...

     

    Anyhow, the following code - the first line being creation of test data - is working fine with me:

     

    let
    Source = #table(type table[BookingDate = date],List.Zip({List.Dates(#date(2017,9,1),30,#duration(1,0,0,0))})),
    #"Filtered Rows" = Table.SelectRows(Source, each [BookingDate] = Date.From (DateTime.LocalNow ()) or [BookingDate] = Date.AddDays (Date.From (DateTime.LocalNow ()) , -1) or [BookingDate] = Date.AddDays (Date.From (DateTime.LocalNow ()), - 7) )
in
    #"Filtered Rows"

     

    Note that you need to refresh your queries daily; it won't update automatically at midnight.

    • sakamotothais's avatar
      sakamotothais
      Icon for Helper III rankHelper III
      8 years ago

      No, I'm using the direct so it's updated every 15 minutes.

       

      And when I apply the code it presents the error:

       

      Expression.Error: Can not convert the #datetime value (2017, 9, 22, 11, 9, 0.4822464) to the Logical type.
      Details:
           Value = 9/22/2017 11:09:00 AM
           Type = Type

       

       

      Thanks

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

        Without your real code, it's hard to judge on the error message.

         

        However, you might have misplaced a parenthesis, like in this picture: