excelvbapowerqueryexcel-tableslistobject

Resize a table with formulas based on table input

I want to resize the table "ProductList" on my worksheet "Final" depending on the input from the source table "SourceTable" on the worksheet "Input" (fields derived from source table helped with formulas).

Eg.: Firstly, I want to refresh source table, then I want to resize the Table "ProductList" that has the range F1:J4 located on the sheet "Final" accordingly to the source table on the worksheet "Input" that has the current range A1:D7 growing monthly just by adding rows.

enter image description here

Help will be greatly appreciated.

Solution

  • The OP (see comments) is using PowerQuery to extract a table from a SQL source. An alternative to re-sizing the table via VBA is to simply amend the original PowerQuery code to include the additional Id column. Then re-sizing will happen automatically when the query is refreshed.

    In the PowerQuery Editor, you can add in a Custom column which contains a formula.

    enter image description here

    You can see the equation is:

    =[CoCd] & "_" & Number.ToText([LSCH]) & "_" & Number.ToText([Material]) & "_" & Number.ToText([Month])

    The string concatenation operator '&' only works on strings, so since my example data has numbers, I am using Number.ToText() to convert. Depending on the exact datatypes in the SQL source, this might not be necessary.

    Next, in the Editor, drag the newly created 'Id' column from right to left. This will generate a 'Reordered Columns' line in the 'Applied Steps' list.

    Finally, 'Close & Load' from the Home menu, and you should get this output in your sheet:

    enter image description here

    If you just want a subset of your SourceTable query in a table called ProductList, then you can set up a PowerQuery internally within the workbook to produce the new table from the source table, and then refresh that (you can set up a dependency on SourceTable so that will be refreshed first).