To dynamically filter PQ table...
You'd use variable (or calculation) in filter condition.
For Month end date for current month...
EoMonth = Date.EndOfMonth(DateTime.Date(DateTime.LocalNow()))
Then you would use it like below.
Sample M:
let
EoMonth = Date.EndOfMonth(DateTime.Date(DateTime.LocalNow())),
Source = List.Dates(#date(2019,1,1),365,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Column1] <= EoMonth)
in
#"Filtered Rows"
Or if you need to pass on to SQL statement. You need to use Text.From() to convert date type data to string, as you can only concatenate Text with Text.
EDIT: You can see small article I wrote about using variable in SQL query (Native query) using PowerQuery and Named Range on a sheet (See 5. Using variable in Native Query).
https://chandoo.org/forum/threads/us...s-notes.35658/
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks