Merging a SQL Table and an Excel Table

[kopp123]

Hello, I'm just entering the world of connecting to SQL via Excel, so please bear with me if I don't get any of this terminology right. I'm trying to merge live sales data that I import from SQL with static cost values that I track within the same workbook.

In my previous iteration of this workbook, I would manually update the sales information every so often, but it was a pain and immediately out of date. That said, it was easy enough to paste in the new data in columns A-D and then the table would auto calculate columns E-G via INDEX(MATCH()) and SUMIF() functions.

I have the SQL data importing into columns A-D, but any formulas I add in columns E-G don't automatically fill down when the SQL data is refreshed. I can't figure out how to do a join with a table in the same workbook (I'd really like to keep this all in a single file for storage and sharing reasons).

When I've done similar efforts in Power BI, I would add a new column in the modelling tab, but that doesn't seem to work here. Any help or resources as I dig into this area? Thanks much!

 

[kopp123]

OK, so I think I figured this out, it's pretty straightforward.

I created the local data on a new sheet, made it a table, and renamed it. With the table selected, go to the Data ribbon, select From Table (in the Get & Transform section). That will bring the table in as a new source and allow you to join to the other SQL sources as part of your queries.

Let me know if there's a better way to do this. Thanks.