cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JeffatHero Frequent Visitor
Frequent Visitor

Filter Query by LASTDATE

When querying a data source I need to filter the rows imported by a date column. This is analytics data where many rows will have the same date, the date the metric was measured. I'd like to limit the import to just the rows with the latest date. I know from reading the LASTDATE function and ALLEXCEPT function can be used. But, I don't know how to write this in the query code.

 

The column to be filtered is 'Google SERP Date'

 

The Query ================

let
Source = Excel.Workbook(File.Contents("C:\Users\jeff\OneDrive - herodesignstudio.com\SCRAM-top-keywords-last-30-days.xlsx"), null, true),
#"SCRAM-top-keywords-last-30-days_Sheet" = Source{[Item="SCRAM-top-keywords-last-30-days",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"SCRAM-top-keywords-last-30-days_Sheet",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type any}, {"Column16", type text}, {"Column17", type text}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type text}, {"Column22", type text}, {"Column23", type any}, {"Column24", type any}, {"Column25", type text}, {"Column26", type any}, {"Column27", type text}, {"Column28", type text}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type any}, {"Column38", type text}, {"Column39", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",5),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows"),
#"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Labels", "For Future Use", "For Future Use_1", "For Future Use_2", "Bing en-US Change (vs previous date)", "Bing en-US Verticals", "Bing en-US Position of Vertical(s) in SERP", "Bing en-US You Present", "Bing en-US Search Volume", "Bing en-US Mobile Friendly", "Bing en-US For Future Use", "Bing en-US For Future Use_3", "Google en-US Verticals", "Google en-US Position of Vertical(s) in SERP", "Google en-US You Present", "Google en-US Search Volume", "Google en-US Mobile Friendly", "Google en-US For Future Use", "Google en-US For Future Use_4", "Yahoo en-US Change (vs previous date)", "Yahoo en-US Verticals", "Yahoo en-US Position of Vertical(s) in SERP", "Yahoo en-US You Present", "Yahoo en-US Search Volume", "Yahoo en-US Mobile Friendly", "Yahoo en-US For Future Use", "Yahoo en-US For Future Use_5"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Google en-US Rank", "Google Change"}, {"Google en-US SERP Date", "Google SERP Date"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Google SERP Date", type date}, {"Yahoo en-US SERP Date", type date}, {"Bing en-US SERP Date", type date}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"none",Replacer.ReplaceValue,{"Bing en-US URL"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"none",Replacer.ReplaceValue,{"Google en-US URL"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,"none",Replacer.ReplaceValue,{"Yahoo en-US URL"}),
#"Renamed Columns1" = Table.RenameColumns(#"Replaced Value2",{{"Google en-US URL", "Google URL"}, {"Google Change", "Google Rank"}, {"Google en-US Change (vs previous date)", "Google Change"}})
in
#"Renamed Columns1"

1 REPLY 1
Highlighted
Super User
Super User

Re: Filter Query by LASTDATE

Just to clarify your misunderstanding about DAX Functions and M Query Language. DAX is the language of PowerPivot-"The calculation Engine" of PowerBI whereas pulling the data from data source involves the Query Editor which uses the different language than DAX and informally called "M". 

Both have different syntax and have completely different functions. 

 

For Your Scenario,

 

You can use Parameters to filter the values in your "Google SERP Date" column. Watch this DEMO.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.