I have been using several excel files as the data sources for a PowerPivot report I publish quarterly. One of the tables has finally grown to over 1 million rows, which .xlsx does not support. Consequently, I cannot append this quarter's data to it and refresh my PowerPivot report. I've outgrown excel and need to graduate to storing the source data in either an Access database or MS Sql Server.
Here's the problem: it does not appear that PowerPivot allows you to change the type of a table to the data source. The Edit section of the connection does not allow me to change from Excel to Access (or any other connection type). If simply delete the current table in PowerPivot and replace it with one named identically but sourced from a different connection, I loose all of the measures I built that relied on my fact table.
How can I swap out the connection of my fact table while preserving all of the DAX measures I have built that reference this table?
Here's the problem: it does not appear that PowerPivot allows you to change the type of a table to the data source. The Edit section of the connection does not allow me to change from Excel to Access (or any other connection type). If simply delete the current table in PowerPivot and replace it with one named identically but sourced from a different connection, I loose all of the measures I built that relied on my fact table.
How can I swap out the connection of my fact table while preserving all of the DAX measures I have built that reference this table?
