Forum Discussion

Majulka's avatar
Majulka
New Member
4 years ago

Date range in source

Hi,

could anybody help me with solving problem with dates range in source?

 

I need to get from database named AccountData only dates (Date1) which are within range set in excel workbook.

In current excel workbook there are set dates uDateFrom=1.7.2021 and uDateTo=5.7.2021, those two dates are the range.

I am actually new to power query and even i tried to search i could not find a solution for it. The query i wrote is not working:

 

let
Source = Odbc.Query("dsn=usr_MJ", "use Data0003;#(lf)Select Account_number, Date1 from AccountData where (Date1>=Date.From(Excel.CurrentWorkbook(){[Name="uDateFrom"]}[Content]{0}[Column1]) and Date1<=Date.From(Excel.CurrentWorkbook(){[Name="uDateTo"]}[Content]{0}[Column1]))")
in
Source

 

I will be very thankfull for help

 

Martina

Need Help

1 Reply

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    Here is what you do:

    1. Create a new query to the Excel workbook.
      1. Filter it so it is on that cell then right-click on that cell and select DRILL DOWN. That will give you a scalar value for the first date.
      2. Call that query varStartDate
      3. Do the same as #1 above, but filter it so it is on the end date.
      4. Call that query varEndDate.
      5. Change your M code to the below:
    let
Source = Odbc.Query("dsn=usr_MJ") -----you may need to change this
FilterData = Table.SelectRows(Source, each [Date1] >= varStartDate and Date1<=varEndDate)
in
FitlerData

     I don't think you can pass a parameter from an excel file back to a native ODBC query like you are trying. The filter needs to happen in Power Query.