filter automatic date in power BI language M

[sakamotothais]

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 from yesterday d-1 and the date from seven days ago d-7. And always when the day is over and the next comes he updates alone.

I thought of something like:

date.adddays ([date], today ()) & date.adddays ([date], - 1) & date.adddays ([date], - 7)

It's possible?

thanks

[v-shex-msft]

HI @sakamotothais,

You can enter query editor and add date filter on date column, then open the advanced editor and modify the filter formula to below sample bold part:

#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Date] = Date.From(DateTime.LocalNow()) or [Date]= Date.AddDays(Date.From(DateTime.LocalNow()),-1) or [Date] = Date.AddDays(Date.From(DateTime.LocalNow()), - 7))

Regards,

Xiaoxin Sheng

[sakamotothais]

Hi

I just did not understand the step of and add date filter on date column

let
Source = Sql.Database("10.0.6.70,1433", "DailyBookings34"),
DMBookings_BookingByLeg = Source{[Schema="DMBookings",Item="BookingByLeg"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(DMBookings_BookingByLeg,{"BookingID", "Status", "RecordLocator", "BookingPromoCode", "ChannelType", "SourceOrganizationCode", "BookingDate", "BookingDateAjustada", "PaxType", "BalanceDue", "ClassOfService", "CabinOfService", "FareClassOfService", "ProductClassCode", "FareBasis", "LegNumber", "DepartureDate", "CarrierCode", "FlightNumber", "DepartureStation", "ArrivalStation", "ChargeType", "ChargeAmount", "ChargeAmountBRL"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "DepMonth", each Date.Month([DepartureDate])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Segment", each Text.Combine({[DepartureStation],[ArrivalStation]})),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each [Status] >= 2 and [Status] <= 3),
#"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Source", each if [SourceOrganizationCode] = "AD" then "Internal" else "Agency" ),
#"Added Custom2" = Table.AddColumn(#"Added Conditional Column", "BkMonth", each Date.Month([BookingDate])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "BkDay", each Date.Day([BookingDate])),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "BkYear", each Date.Year([BookingDate])),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "DepYear", each Date.Year([DepartureDate])),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "DepDay", each Date.Day([DepartureDate])),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom6", each [FareClassOfService] <> "NC" and [FareClassOfService] <> "NR" and [FareClassOfService] <> "NU" and [FareClassOfService] <> "XA" and [FareClassOfService] <> "XH"),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [BalanceDue] <= 0),
#"Added Custom7" = Table.AddColumn(#"Filtered Rows2", "Personalizado", each [FlightNumber]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom7",{"Personalizado"}),
#"Filtered Rows3" = Table.SelectRows(#"Removed Columns", each [CarrierCode] <> "CH"),
#"Added Conditional Column1" = Table.AddColumn(#"Filtered Rows3", "Rule Revenue", each if [ChargeType] = 0 then "1" else if [ChargeType] = 1 then "-1" else if [ChargeType] = 7 then "-1" else "0" ),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Revenue", each if [Rule Revenue] = "0" then [ChargeAmount] else if [Rule Revenue] = "7" then [ChargeAmount] else if [Rule Revenue] = "1" then [ChargeAmount] else null ),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Segments", each if [ChargeType] = 0 then "1" else if [ChargeType] = -1 then "1" else if [ChargeType] = 4 then "0" else "1" ),
#"Added Custom8" = Table.AddColumn(#"Added Conditional Column3", "BkHour", each Time.Hour([BookingDate])),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "BkMinute", each Time.Minute([BookingDate])),
#"Added Conditional Column4" = Table.AddColumn(#"Added Custom9", "BkQuarter", each if [BkMinute] <= 15 then "1" else if [BkMinute] <= 30 then "2" else if [BkMinute] <= 45 then "3" else "4" ),
#"Filtered Rows4" = Table.SelectRows(#"Added Conditional Column4", each [BkHour] >= 0)
in
#"Filtered Rows4"

Thanks for the feedback,

Thanks

[v-shex-msft]

HI @sakamotothais,

Please refer to below steps:

1. Find out the date column and choose filter.

2. Input one date to click on ok to close the filter panel.

3. Modify the filter formula.

4. Click on  '√'  to finish edit.

Regards,

Xiaoxin Sheng

[sakamotothais]

Hi

Display a logic 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]

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]

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]

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]

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]

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

[MarcelBeug · Accepted solution]

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]

Dude, it worked, excuse me for any inconvenience. Thank you, thank you.

Thank You