Filter table by field >= T-14

[Anonymous]

Hi, 

I have a dataset from a SQL DB that feeds a report and a couple of paginated reports. 

As a new requirement, for all reports I now only need to show the data where the field Phase2PlannedStartDate is equal or later than Today()-14 or blank. I have created a measure for T-14, but I haven't found a way of using that in the query editor to filter the column: 

How do I apply this condition?

I'm not very familiar with M. I added this line to the advanced editor (the syntax check is okay):

#"Filtered by T-14" = Table.SelectRows([Phase2PlannedStartDate] >= "T-14" or [Phase2PlannedStartDate] = null)

I also tried this: 

#"Filtered by T-14" = Table.SelectRows([Phase2PlannedStartDate] >= (Date.AddDays(DateTime.LocalNow(), -14)) or [Phase2PlannedStartDate] = null)

but considering that T-14 as per today would be June 25, 2021 the query doesn't work:

Any help with this would be greatly appreciated! 

~Alienvolm

[CNENFRNL]

#"Filtered by T-14" = Table.SelectRows(#"Name of Previous Step", let t=Date.From(DateTime.LocalNow()) in each [Phase2PlannedStartDate] >= t-#duration(14,0,0,0) or [Phase2PlannedStartDate] is null or [Phase2PlannedStartDate] = "")

[Anonymous]

Hi @CNENFRNL, 

I tried your code and I got this error: 

Here's the complete query code for your reference: 

let
    Source = Sql.Databases("sql-shared-prod-us.database.windows.net"),
    #"JourneyAutomation " = Source{[Name="JourneyAutomation"]}[Data],
    dbo_JourneyUser = #"JourneyAutomation "{[Schema="dbo",Item="JourneyUser"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_JourneyUser,{{"TenantID", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"JourneyPathID", "isInActive"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"CurrentPhase", Text.Proper, type text}})
in
    #"Capitalized Each Word"

Thanks in advance! 

[v-angzheng-msft]

Hi, @Anonymous 

try this:

= Table.SelectRows(#"Previous Step", each [DateTime] >= DateTime.LocalNow()-#duration(14,0,0,0)or [DateTime]=null)

 result:

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @v-angzheng-msft,

I tried that and it doesn't seem to work either:

Also, after applying the change, I'm unable to view the table in Data view in Power BI Desktop (I can select the table but I see the data for a different table). 

This is the code with the modification (just in case I did something wrong:

let
    JourneyUser = let
    Source = Sql.Databases("sql-shared-prod-us.database.windows.net"),
    #"JourneyAutomation " = Source{[Name="JourneyAutomation"]}[Data],
    dbo_JourneyUser = #"JourneyAutomation "{[Schema="dbo",Item="JourneyUser"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_JourneyUser,{{"TenantID", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"JourneyPathID", "isInActive"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"CurrentPhase", Text.Proper, type text}}),
    #"Filtered by T-14" = Table.SelectRows(#"Capitalized Each Word", each [Phase2PlannedStartDate] >= DateTime.LocalNow()-#duration(14,0,0,0)or [Phase2PlannedStartDate]=null)
in
    #"Capitalized Each Word",
    #"Filtered Rows" = Table.SelectRows(JourneyUser, each true)
in
    #"Filtered Rows"

Thanks for your help! 

~Alienvolm

[v-angzheng-msft]

Hi, @Anonymous 

Try to modify the last return value to the JourneyUser like below:

let
    JourneyUser = let
    Source = Sql.Databases("sql-shared-prod-us.database.windows.net"),
    #"JourneyAutomation " = Source{[Name="JourneyAutomation"]}[Data],
    dbo_JourneyUser = #"JourneyAutomation "{[Schema="dbo",Item="JourneyUser"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_JourneyUser,{{"TenantID", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"JourneyPathID", "isInActive"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"CurrentPhase", Text.Proper, type text}}),
    #"Filtered by T-14" = Table.SelectRows(#"Capitalized Each Word", each [Phase2PlannedStartDate] >= DateTime.LocalNow()-#duration(14,0,0,0)or [Phase2PlannedStartDate]=null)
in
    #"Capitalized Each Word",
    #"Filtered Rows" = Table.SelectRows(JourneyUser, each true)
in
    JourneyUser

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, 

With this code:

let
    Source = Sql.Databases("sql-shared-prod-us.database.windows.net"),
    #"JourneyAutomation " = Source{[Name="JourneyAutomation"]}[Data],
    dbo_JourneyUser = #"JourneyAutomation "{[Schema="dbo",Item="JourneyUser"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_JourneyUser,{{"TenantID", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"JourneyPathID", "isInActive"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"CurrentPhase", Text.Proper, type text}}),
    #"Filtered by T-14" = Table.SelectRows(#"Capitalized Each Word", each [Phase2PlannedStartDate] >= DateTime.LocalNow()-#duration(14,0,0,0)or [Phase2PlannedStartDate]=null)
in 
    #"Capitalized Each Word",
    #"Filtered Rows" = Table.SelectRows(JourneyUser, each true)
in 
    JourneyUser

I get this: 

The same is if I eliminate any of the two rows after the first "in".

If I eliminate the first "in" statement alltogether, the syntax is correct:

let
    Source = Sql.Databases("sql-shared-prod-us.database.windows.net"),
    #"JourneyAutomation " = Source{[Name="JourneyAutomation"]}[Data],
    dbo_JourneyUser = #"JourneyAutomation "{[Schema="dbo",Item="JourneyUser"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_JourneyUser,{{"TenantID", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"JourneyPathID", "isInActive"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"CurrentPhase", Text.Proper, type text}}),
    #"Filtered by T-14" = Table.SelectRows(#"Capitalized Each Word", each [Phase2PlannedStartDate] >= DateTime.LocalNow()-#duration(14,0,0,0)or [Phase2PlannedStartDate]=null)
in 
    JourneyUser

But then I get this:

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Sorry for my mistake, I wasn't clear on your final code until your last reply.
While I'm no expert on PowerQuery, one thing to note is that Let and in come in pairs.
Think of LET as the definition function and IN as the return value then it's clear.

I show the M code for my sample data as follows.

In your case, i think the code will be this:

let
    Source = Sql.Databases("sql-shared-prod-us.database.windows.net"),
    #"JourneyAutomation " = Source{[Name="JourneyAutomation"]}[Data],
    dbo_JourneyUser = #"JourneyAutomation "{[Schema="dbo",Item="JourneyUser"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_JourneyUser,{{"TenantID", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"JourneyPathID", "isInActive"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"CurrentPhase", Text.Proper, type text}}),
    #"Filtered by T-14" = Table.SelectRows(#"Capitalized Each Word", each [Phase2PlannedStartDate] >= DateTime.LocalNow()-#duration(14,0,0,0)or [Phase2PlannedStartDate]=null)
in 
    #"Filtered by T-14"    //return the last result

Not sure if it's useful for you, here is my sample file

Best Regards,
Community Support Team _ Zeon Zheng

[Anonymous]

Thank you @v-angzheng-msft!

That worked... and also thank you for the explanation on the M syntax. Do you know any resources that I could use for M queries in Power BI? I don't need to use it often, but it would be nice to have some beginners guidance. 

Thanks a lot! 

~Alienvom 

[v-angzheng-msft]

Hi, @Anonymous 

Here are some references that may be helpful

https://docs.microsoft.com/en-us/powerquery-m/
https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let

https://excelchamps.com/power-query/

https://chandoo.org/wp/power-query-tutorial/

https://www.excelguru.ca/blog/category/power-query/

https://blog.crossjoin.co.uk/category/power-query/

Best Regards,
Zeon Zheng

[Anonymous]

Thank you! 🙂