Get & Transform: Changing Data Range for Query

Hi,

I have attached sample data that I would like to import into Power Query so that I can make some transformations to it before loading the results to a Table in an Excel worksheet (the "real" data is more complicated than this).

Each month I receive updated data in a worksheet and would like to refresh the PQ query and thus the associated Table to take account of it.

I update Sheet1 each month by pasting over with the new data which may have more or fewer rows and columns that the month before.

Ideally I would like to convert the data in Sheet1 to a Table as PQ seems to deal well with data in that format.

However, while the Table will expand to include rows and columns that weren't populated in the previous month when I paste over the new month's data, it won't shrink if there are fewer.

So I guess I have two options:

1. Each month before pasting in the new data, convert the table back to a range, paste in the new data and then convert the new range to a table before refreshing the query, or

2. Find out whether or not there is a way to make the range in the worksheet dynamic so that the query will adjust as the range expands or contracts.

Can someone please suggest how best to deal with this situation.

Thanks

How about adding the new data to your table so it expands to include historical and current data. Then highlight the rows of the historical data and delete it. Do this by highlighting the rows in the row number. Will this work for you?