SOLVED

Macro to refresh Power Query when new data is added

%3CLINGO-SUB%20id%3D%22lingo-sub-1404246%22%20slang%3D%22en-US%22%3EMacro%20to%20refresh%20Power%20Query%20when%20new%20data%20is%20added%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1404246%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20spreadsheet%20which%20is%20uses%20power%20query%20to%20extract%20text%20from%20a%20cell.%20This%20is%20information%20is%20updated%20each%20day%2C%20is%20there%20a%20way%20i%20can%20use%20a%20macro%20so%20that%20the%20power%20query%20function%20will%20refresh%20when%20new%20data%20is%20added%2C%20or%20a%20button%20is%20enabled.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20kindly%20for%20any%20assistance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1404246%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1404500%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20refresh%20Power%20Query%20when%20new%20data%20is%20added%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1404500%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F673429%22%20target%3D%22_blank%22%3E%40calof11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPower%20Query%20converts%20the%20source%20data%20into%20an%20Excel%20Table%20if%20the%20source%20data%20range%20is%20not%20formatted%20as%20an%20Excel%20Table%20and%20this%20table%20has%20a%20name.%20Let's%20assume%20that%20name%20of%20the%20source%20Excel%20Table%20is%20%22Table1%22%20and%20it%20is%20on%20the%20Sheet1.%3C%2FP%3E%3CP%3ELet's%20also%20assume%20that%20the%20name%20of%20the%20Query%20Connection%20is%20%22Query%20-%20Table1%22%20which%20you%20want%20to%20refresh%20automatically%20whenever%20the%20data%20in%20the%20Table1%20gets%20changed.%20Refer%20to%20the%20screenshot%20below%20to%20know%20how%20to%20get%20the%20correct%20name%20of%20the%20Query%20Connection%20to%20update%20the%20Code%20that%20would%20be%20used%20to%20refresh%20the%20Query%20automatically.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20if%20the%20source%20table%20%22Table1%22%20is%20placed%20on%20Sheet1.%20right%20click%20the%20Sheet1%20Tab%20ans%20choose%20View%20Code%20and%20then%20place%20the%20code%20given%20below%20into%20the%20opened%20code%20window.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0ADim%20tbl%20As%20ListObject%0A%0A'Name%20of%20the%20source%20Excel%20Table%20on%20the%20worksheet%2C%20Power%20Query%20imports%20the%20data%20from%0ASet%20tbl%20%3D%20ActiveSheet.ListObjects(%22Table1%22)%0A%0AIf%20Not%20Intersect(Target%2C%20tbl.DataBodyRange)%20Is%20Nothing%20Then%0A%20%20%20%20'Replace%20the%20%22Query%20-%20Table1%22%20with%20the%20name%20of%20the%20query%20connection%20to%20refresh%0A%20%20%20%20ThisWorkbook.Connections(%22Query%20-%20Table1%22).Refresh%0AEnd%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20above%20code%20is%20for%20Sheet%20Change%20Event%20and%20that%20means%20whenever%20you%20change%20the%20data%20in%20the%20Table1%2C%20the%20%22Query%20-%20Table1%22%20will%20be%20refreshed%20automatically.%3C%2FP%3E%3CP%3EPay%20attention%20to%20the%20comments%20added%20in%20the%20code%20to%20know%20how%20you%20can%20tweak%20this%20code%20to%20make%20it%20work%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Existing%20Connections.jpg%22%20style%3D%22width%3A%20827px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F193224i146A7C0E3C3DA1A5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Existing%20Connections.jpg%22%20alt%3D%22Existing%20Connections.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1407180%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20refresh%20Power%20Query%20when%20new%20data%20is%20added%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1407180%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20kindly%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20have%20a%20go%20at%20updating%20my%20spreadsheet%20shortly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1407487%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20refresh%20Power%20Query%20when%20new%20data%20is%20added%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1407487%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F673429%22%20target%3D%22_blank%22%3E%40calof11%3C%2FA%3E!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi All,

 

I have created a spreadsheet which is uses power query to extract text from a cell. This is information is updated each day, is there a way i can use a macro so that the power query function will refresh when new data is added, or a button is enabled.

 

Thank you kindly for any assistance.

 

3 Replies
Highlighted
Best Response confirmed by calof11 (New Contributor)
Solution

@calof11 

 

Power Query converts the source data into an Excel Table if the source data range is not formatted as an Excel Table and this table has a name. Let's assume that name of the source Excel Table is "Table1" and it is on the Sheet1.

Let's also assume that the name of the Query Connection is "Query - Table1" which you want to refresh automatically whenever the data in the Table1 gets changed. Refer to the screenshot below to know how to get the correct name of the Query Connection to update the Code that would be used to refresh the Query automatically.

 

Now if the source table "Table1" is placed on Sheet1. right click the Sheet1 Tab ans choose View Code and then place the code given below into the opened code window.

 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tbl As ListObject

'Name of the source Excel Table on the worksheet, Power Query imports the data from
Set tbl = ActiveSheet.ListObjects("Table1")

If Not Intersect(Target, tbl.DataBodyRange) Is Nothing Then
    'Replace the "Query - Table1" with the name of the query connection to refresh
    ThisWorkbook.Connections("Query - Table1").Refresh
End If
End Sub

 

 

The above code is for Sheet Change Event and that means whenever you change the data in the Table1, the "Query - Table1" will be refreshed automatically.

Pay attention to the comments added in the code to know how you can tweak this code to make it work for you.

 

Existing Connections.jpg

 

 

 

Hi@Subodh_Tiwari_sktneer 

 

Thank you kindly for your help.

 

I will have a go at updating my spreadsheet shortly.

 

Many thanks,

Highlighted