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"
