PowerQuery pull back selected date

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,146
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?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,315
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
VBA Code:
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

Well-known Member
Joined
Mar 28, 2009
Messages
2,146
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
VBA Code:
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
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,146
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
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,146
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

Well-known Member
Joined
Oct 24, 2015
Messages
6,315
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,014
Messages
5,508,798
Members
408,694
Latest member
LightBright

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top