Importing tables from external Excel workbooks into data model

[jsa1987]

I have been tinkering with PowerPivot on and off for a while, but I'm only now starting to use it in ways that speed up my work.

In the latest project I want to convert to a PowerPivot-based workflow, one of the data sources is an Excel table (with loan balances, attributes, etc.) from another Excel workbook. What is the best way to do this? This is a workflow I need to go through monthly, so I'd like the PowerPivot table to be refreshable.

Ideally I'd set up a data connection, but as far as I can tell, Use Get External Data From Other Sources is not a good solution, because my headers are around line 5 of the source worksheet, and there is some data below the actual Table on the worksheet. And there doesn't seem to be a way to use External Connections to connect to an Excel Table rather than a worksheet. I'd love to be wrong about that.

I also tried Add to Data Model but that doesn't seem to work with an external table or range.

Here are what I see as my options:

a) Copy the table into my PowerPivot workbook. The main downsides to this are having to manually update for any corrections in the source workbook; each month I will have to paste over the existing table; and the additional data being contained in the PowerPivot workbook.

b) Paste the table into a PowerPivot table in my PowerPivot workbook. I think if I do this, I can paste and replace the table in every month, or every time it changes. That doesn't seem so bad.

Any suggestion on a best practice would be appreciated!

 

[StrafeXL]

I'm assuming you're using the word table in a generic sense rather than an actual Excel table (i.e. Insert...Table). I work with a lot of source systems where the exported Excel data is ugly. I use Power Query (Download Microsoft Power Query Preview for Excel from Official Microsoft Download Center) to ETL the data and use that as my data source.

Microsoft Data Explorer Preview for Excel - YouTube
Note that Power Query used to be called Data Explorer.

 

[jsa1987]

I'm assuming you're using the word table in a generic sense rather than an actual Excel table (i.e. Insert...Table). I work with a lot of source systems where the exported Excel data is ugly. I use Power Query (Download Microsoft Power Query Preview for Excel from Official Microsoft Download Center) to ETL the data and use that as my data source.

Microsoft Data Explorer Preview for Excel - YouTube
Note that Power Query used to be called Data Explorer.

I am referring to the latter sense, an Excel table (Insert Table or Ctrl-T). The data is pretty clean but it would be nice to refer to the Table itself rather than have to refer to cell ranges that change every month (not to mention I currently set up a new file every month ... if I could just refer which Table I'm referencing each month, that would be great).

Not sure if that changes your suggestion at all. I started checking out Power Query shortly after I posted the above message, but it didn't seem to have a mechanism for saving the steps so I could use them repeatably each month, so I moved on. I ended up (as a temporary solution) copying the table into my PowerPivot workbook and using the copy as a linked table. I tried pasting into a PowerPivot table, but I would have had to do that in many pieces, which seemed inefficient.

Maybe the answer is to use the same file each month (e.g. call it "Loans Current Month") and save off the prior month each month rather than changing the current month's file name each month, then I could use PowerQuery to bring in that data each time I'm ready for the PowerPivot piece. Basically I don't want to have to set up new PowerPivot calculated columns, calculated fields, etc. each time the source changes.

Thanks!!

 

[StrafeXL]

Hmm, then I'm confused because generally a table is used to solve the problem of a cell range that changes. And you wouldn't normally have headers 5 rows down, unless the 5 rows you're referring to are not part of the table, in which case they don't matter because you can just refer to the table.

Anyway, Power Query scripts are absolutely repeatable. That's the whole point. It might be helpful to see the worksheet you're working from, even a mock up.

 

[jsa1987]

Sorry it has taken me a while to respond. I was wrapped up in various quarter-end processes.

I think you nailed it with your second sentence. The five rows (on closer inspection it's actually three rows, not that it matters) are not part of the Table; what I haven't figured out (and I've certainly been trying) is how to refer directly to the Table.

Here's an example of a much smaller table but one that still demonstrates the issue that arises. A4:C6 is an Excel Table named Loans.

Thanks!!

 

[Art15651]

Try defining your data area as a Name. This should make it visible when you import it; it does for me.