Forum Discussion
Helper IIIfilter automatic date in power BI language M
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"
Helper IIIeach [BookingDate] = Date.From (DateTime.LocalNow ()) ou [BookingDate] = Date.AddDays (Date.From (DateTime.LocalNow ()) , -1) ou [BookingData] = Data.AddDays (Date.From (DateTime.LocalNow ()), - 7) )
Community ChampionIt 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.
- MarcelBeug
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" - sakamotothais
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 = TypeThanks
- MarcelBeug
Without your real code, it's hard to judge on the error message.
However, you might have misplaced a parenthesis, like in this picture:
- sakamotothais
Hi,
Real Code:
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 [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"Thank you
- MarcelBeug
Thanks, that confirms my suspicion of a misplaced parenthesis.
- sakamotothais
I'm still missing something, I have not given it.
- MarcelBeug
Do you really expect us to figure out what you are missing???
Let me guess: your error message has gone (congratulations!).
Ah, you don't have records?
In your print screen we are really not able to see if there are any records before filtering that should have been kept.
We are volunteers trying to help you; not the ones that are causing you all your troubles.
So please try and be more reasonable.
- sakamotothais
I think it has nothing in my code, it's still simple, and I'm investigating to try to understand why the dates are not coming.
After all, he does not point out any mistakes, as you can see.
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 [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"And I have previous records yes
- MarcelBeug
You are still not sharing the relevant information.
Please share a screenshot from the previous step, proving that you have data in your table that should be kept after filtering.
Edit: OK after your edit of the previous post I see that you don't have dates in your BookingDate column, so that's why nothing is left after filtering.
- sakamotothais
already shared, look at the post again
without the filter I want to mount, making the filter slicer
- sakamotothais
Dude, it worked, excuse me for any inconvenience. Thank you, thank you.
Thank You