Oh gosh. a big question; many things can vary. The trite answer is to go and get yourself a primer on Power Query, or find an online tutorial.

You could try and copy your real data onto the existing table, it should automatically expand to accommodate the new data (make sure it keeps the same table name as I used), then try and refresh - you might be lucky, otherwise, supply a workbook with a larger set of colums (it doesn't need to all 3000 rows, just enough to give a valid results table (50-100 rows?).

As far as new/future projects are concerned, then it's my trite answer I'm afraid. For me to try and explain all the steps taken it's going to be virtually the same as a tutorial and I'm just not prepared to give the oodles of time it will need.

I did try copying my real data onto the existing table w/o any success.
Attached is a sample data (a strip down version). Sheet2 is the expected result.
Over the weekend, I watched few introductions to power query videos. But, Iíve yet to learn enough to apply it to what Iím trying to accomplish.

Thank you for the revision and solution to my post. This is exactly what I was trying to accomplish. As for the number of columns, I removed irrelevant columns to simplify things and it's going to stay this way.

Thank you for sharing the discussion about fill down in excelguru. I never experienced this issue before, but I found the discussion to be very educational.

My goal moving forward is to learn power query and power BI as much as possible.