Hello Everyone,
I need help to filter dates in power query based on cell value mentioned in excel.
Following is my sample sales dataset
i need to filter Order date column in power query based on value mentioned in excel sheet cells, For example if i put 01/04/2021 as Start Date and 31/05/2021 as End Date then the table will return the value between these two date range.
Following is my power query M code looks like:
let
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderDate", type date}, {"Region", type text}, {"Rep", type text}, {"Item", type text}, {"Units", Int64.Type}, {"Unit Cost", type number}, {"Total", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [OrderDate] >= #date(2022, 4, 1) and [OrderDate] <= #date(2022, 5, 31))
in
#"Filtered Rows"
I am looking for a solution to link the dates dynamically. Also sharing the excel workbook for your reference.
Thank you so much in advance.
Best Regards,
Shib
I need help to filter dates in power query based on cell value mentioned in excel.
Following is my sample sales dataset
Order Date | Region | Rep | Item | Units | Unit Cost | Total |
06-Jan-21 | East | Jones | Pencil | 95 | 1.99 | 189.05 |
23-Jan-21 | Central | Kivell | Binder | 50 | 19.99 | 999.50 |
09-Feb-21 | Central | Jardine | Pencil | 36 | 4.99 | 179.64 |
26-Feb-21 | Central | Gill | Pen | 27 | 19.99 | 539.73 |
15-Mar-21 | West | Sorvino | Pencil | 56 | 2.99 | 167.44 |
01-Apr-21 | East | Jones | Binder | 60 | 4.99 | 299.40 |
18-Apr-21 | Central | Andrews | Pencil | 75 | 1.99 | 149.25 |
05-May-21 | Central | Jardine | Pencil | 90 | 4.99 | 449.10 |
22-May-21 | West | Thompson | Pencil | 32 | 1.99 | 63.68 |
i need to filter Order date column in power query based on value mentioned in excel sheet cells, For example if i put 01/04/2021 as Start Date and 31/05/2021 as End Date then the table will return the value between these two date range.
Following is my power query M code looks like:
let
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderDate", type date}, {"Region", type text}, {"Rep", type text}, {"Item", type text}, {"Units", Int64.Type}, {"Unit Cost", type number}, {"Total", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [OrderDate] >= #date(2022, 4, 1) and [OrderDate] <= #date(2022, 5, 31))
in
#"Filtered Rows"
I am looking for a solution to link the dates dynamically. Also sharing the excel workbook for your reference.
Thank you so much in advance.
Best Regards,
Shib
Last edited: