PowerPivot Linked Table and Key Values

[twilson]

Hi- new to the forum. I have a table in Excel that is dynamic-changes based on parameters. We'll call this table "ExcelDynamicTable." I have linked this table to a table in Powerpivot. We will call this "PPTable." Column A in PPTable has my primary key and I want to use it to create relationships with other tables.

When I update my ExcelDynamicTable sometimes it returns more rows- sometimes smaller rows (hence it is Dynamic). When it returns smaller rows, the PPTable refreshes and holds over the blank values-- so when I try to create relationships is says it does not have unique values! (It is counting the blank cells as non-unique fields). I've tried naming my ExcelDynamicTable a dynamic named range- that doesn't work as PowerPivot only uses the first reference of the first instance of named range.

Has anyone else experienced any similar problems? Any thoughts or help would be very much appreciated.

 

[RoryA]

I assume your table is still actually defined as a Table? Does it get data from somewhere else, or is it manually entered? If the former, do the additional blank rows still appear as part of the table in Excel? (and is there a reason for not connecting directly to the source in PP?)

 

[twilson]

Thanks for getting back to me. I have resolved the issue by creating another table that uses the dynamic table as a data source. In answer to your questions though- yes it is defined as a table and it gets it's source from a separate table which is a connected to a sql server database. The reason I am not directly connected to the sql table is because I have to perform calculations on the connected table. (I have to project dates which are beyond the current date.) In other words the table is simply a prepping stage for the data. Thanks again for your reply. It is good to know help is out there!