PowerQuery pull back selected date

[mahmed1]

Hi All

I have set up powerQuery connection to pull back data from a database - At the minute it pulls back everything

Is it possible to only pull back data from a start date and end date in excel?

so say im A1 i have startdate and A2 i have enddate - Can i only pull back that data so im not pulling more data than needed?

And also in VBA - im refreshing all the powerQuery connections...I recorded a macro and the macro code first selects the sheet and then uses Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

I tried to not use the select method and just refresh it by using the following thisworkbook.connections(“tblQuery”).refresh but that fails
how can i avoid using select?

 

[sandy666]

in short:
1. use startdate and enddate to generate all dates in this range then merge this column with your database by date column
2. maybe this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim con As WorkbookConnection
Dim Cname As String

For Each con In ActiveWorkbook.Connections
    If Left(con.Name, 8) = "Query - " Then
    Cname = con.Name
        With ActiveWorkbook.Connections(Cname).OLEDBConnection
            .BackgroundQuery = False  'or true, up to you
            .Refresh
        End With
    End If
Next
End Sub

 

[mahmed1]

in short:
1. use startdate and enddate to generate all dates in this range then merge this column with your database by date column
2. maybe this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim con As WorkbookConnection
Dim Cname As String

For Each con In ActiveWorkbook.Connections
    If Left(con.Name, 8) = "Query - " Then
    Cname = con.Name
        With ActiveWorkbook.Connections(Cname).OLEDBConnection
            .BackgroundQuery = False  'or true, up to you
            .Refresh
        End With
    End If
Next
End Sub

thank you

sorry not sure what and the steps to the point1..I’m new to power query so apologies

 

[sandy666]

try
= {startdate..enddate}
then change list to table

 

[mahmed1]

Can that start date and end date reference cells in excel?

mans then how do I only pull back or keep dates that fall within the date range

 

[sandy666]

see this example

s666-PQ-CalendarExample.xlsm

Microsoft Excel Workbook

1drv.ms

 

[mahmed1]

Thank you - I’ll give that a go - how can I then use this to only pull back Data or keep dates that are found within that range so only pull back limited data

 

[sandy666]

in this file you've defined range of dates
you need to add source from database where you've column with dates (probably)
merge these tables by date columns Left Outer and it will give you records for defined dates
then remove not necessary column(s) and transform table as you wish

 

[sandy666]

here is an example with generic data

s666-PQ-CalendarExampleWithData.xlsm

Microsoft Excel Workbook

1drv.ms

 

[mahmed1]

Sorry Mate, only just seen that you had responded back - Thank you

If im understanding your logic...

Am i right in saying i need to do these steps to pull back selected data (It was so much easier with older version imo)

1) A1: Enter Start Date
2) B1: Enter End Date
3) Generate a list of dates in Excel between those 2 dates (say this table is called DateList)
4) Get data from database
5 Get data from Excel
6) Join date table to database date

Now how do i do a left outer join in PowerQuery?

In older query option - you can put start and end date from cell and load straight back - i wonder why they took that out