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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

sandy666

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

Watch MrExcel Video

Forum statistics

Threads
1,102,140
Messages
5,484,974
Members
407,476
Latest member
PDT Inc

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top