Copy Power Query result data to a Table object

%3CLINGO-SUB%20id%3D%22lingo-sub-3345519%22%20slang%3D%22en-US%22%3ECopy%20Power%20Query%20result%20data%20to%20a%20Table%20object%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3345519%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20Excel%20365%2C%20in%20a%20workbook%20I%20have%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Worksheet%20in%20which%20I%20have%20a%20Power%20Query%20running%2C%20the%20PQ%20is%20taking%20a%20sub-set%20of%20data%20from%20a%20different%20file.%3C%2FP%3E%3CP%3EAt%20the%20moment%20the%20PQ%20filter%20is%20static%20although%20I%20need%20to%20change%20it%20so%20the%20single%20filter%20it's%20using%20will%20be%20based%20on%20a%20value%20I%20will%20choose%20on%20the%20worksheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20result%20of%20the%20PQ%20is%20made%20out%20of%20four%20columns%20with%20a%20changing%20number%20of%20rows%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20On%20a%20separate%20worksheet%2C%20I%20have%20a%20Table%20object%20which%20has%20five%20columns%20(one%20more%20field%20which%20needs%20to%20be%20manually%20entered)%20and%20I%20need%20to%20auto-populate%20the%20four%20columns%20from%20the%20PQ%20(the%20table%20is%20empty)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20little%20experience%20in%20PQ%20manipulations%20using%20VBA%2C%20can%20someone%20please%20help%20with%20either%20guidance%20or%20VBA%20code%20to%20help%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3ERonnen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3345519%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3345812%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Power%20Query%20result%20data%20to%20a%20Table%20object%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3345812%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1385022%22%20target%3D%22_blank%22%3E%40rnnbr%3C%2FA%3E%26nbsp%3BPerhaps%20best%20if%20you%20upload%20a%20file%20or%20a%20link%20to%20a%20shared%20file%20one%20OneDrive%20(or%20similar)%2C%20as%20it's%20difficult%20to%20picture%20what%20you%20want%20to%20do%20otherwise.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3345964%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Power%20Query%20result%20data%20to%20a%20Table%20object%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3345964%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20I%20couldn't%20upload%20a%20file%20but%20I've%20built%20below%20something%20that%20I%20hope%20can%20explain%20better%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20screen%20capture%20of%20the%20worksheet%20below%20contains%20the%20results%20of%20a%20PQ%2C%20you%20can%20see%20four%20columns%3A%20Name%2C%20Lead%2C%20Position%2C%20location%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rnnbr_0-1652121828268.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F370278i19B8AB3618307E49%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22rnnbr_0-1652121828268.png%22%20alt%3D%22rnnbr_0-1652121828268.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThis%20is%20the%20next%20worksheet%20in%20which%20I%20want%20the%20PQ%20data%20to%20populate%2C%20it%20is%20easy%20to%20see%20which%20data%20goes%20where%20as%20the%20column%20names%20are%20the%20same%2C%20apart%20from%20Priority%20Position%2C%20which%20has%20to%20be%20entered%20manually%20after%20the%20table%20has%20been%20populated%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(the%20future%20question%20is%20how%20to%20amend%20the%20PQ%20via%20VBA%20to%20change%20the%20filter%20by%20the%20lead%20entered%20in%20say%20cell%20E1)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rnnbr_1-1652121862248.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F370280i80271008E6D5047C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22rnnbr_1-1652121862248.png%22%20alt%3D%22rnnbr_1-1652121862248.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20easier%20to%20understand%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3348287%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Power%20Query%20result%20data%20to%20a%20Table%20object%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3348287%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1385022%22%20target%3D%22_blank%22%3E%40rnnbr%3C%2FA%3E%26nbsp%3BYes%20and%20no.%20I%20see%20what%20your%20are%20working%20on%20but%20don't%20really%20understand%20what%20you%20need.%20You%20have%20a%20table%20that%20comes%20out%20of%20PQ%20(the%20green%20one)%20and%20then%20you%20want%20to%20include%20it%20in%20the%20blue%20one%20where%20extra%20information%20is%20added.%20Perhaps%20you%20want%20to%20add%20a%20column%20to%20a%20PQ%20generated%20table%20and%20keep%20this%20additional%20information%20synchronised%20when%20you%20refresh%20PQ.%20If%20so%2C%20that's%20possible%20if%20you%20follow%20some%20basic%20steps.%20Google%20for%20%22self%20referencing%20table%20power%20query%22%20and%20you'll%20find%20many%20resources%20that%20describe%20the%20process.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3356065%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Power%20Query%20result%20data%20to%20a%20Table%20object%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3356065%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bfor%20various%20reasons%20I%20need%20to%20populate%20the%20Table%2C%20not%20only%20that%20there%20is%20more%20logic%20on%20it%20but%20it%E2%80%99s%20then%20being%20used%20by%20other%20worksheets%2C%20I%E2%80%99ll%20try%20to%20start%20uncovering%20how%20to%20loop%20through%20the%20PQ%20results%20and%20copy%20the%20data%20to%20the%20Table%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all,

 

I'm using Excel 365, in a workbook I have :

 

1. Worksheet in which I have a Power Query running, the PQ is taking a sub-set of data from a different file.

At the moment the PQ filter is static although I need to change it so the single filter it's using will be based on a value I will choose on the worksheet. 

 

The result of the PQ is made out of four columns with a changing number of rows

 

2. On a separate worksheet, I have a Table object which has five columns (one more field which needs to be manually entered) and I need to auto-populate the four columns from the PQ (the table is empty)

 

I have little experience in PQ manipulations using VBA, can someone please help with either guidance or VBA code to help? 

 

Thank you,

Ronnen

4 Replies

@rnnbr Perhaps best if you upload a file or a link to a shared file one OneDrive (or similar), as it's difficult to picture what you want to do otherwise.

@Riny_van_Eekelen 

Hi, I couldn't upload a file but I've built below something that I hope can explain better:

 

The screen capture of the worksheet below contains the results of a PQ, you can see four columns: Name, Lead, Position, location

rnnbr_0-1652121828268.png

This is the next worksheet in which I want the PQ data to populate, it is easy to see which data goes where as the column names are the same, apart from Priority Position, which has to be entered manually after the table has been populated  

 

(the future question is how to amend the PQ via VBA to change the filter by the lead entered in say cell E1)

rnnbr_1-1652121862248.png

 

Is this easier to understand? 

@rnnbr Yes and no. I see what your are working on but don't really understand what you need. You have a table that comes out of PQ (the green one) and then you want to include it in the blue one where extra information is added. Perhaps you want to add a column to a PQ generated table and keep this additional information synchronised when you refresh PQ. If so, that's possible if you follow some basic steps. Google for "self referencing table power query" and you'll find many resources that describe the process. 

@Riny_van_Eekelen for various reasons I need to populate the Table, not only that there is more logic on it but it’s then being used by other worksheets, I’ll try to start uncovering how to loop through the PQ results and copy the data to the Table