pietlinden (7/2/2015)
I think you can, it's just that the interface is extremely unfriendly. (Another reason it works much better against a DW).After posting this, I got off my lazy butt and found this in BI Tools for Excel Analysts
In VBA, you modify the connection's CommandText property.
Sub RefreshQuery()With ActiveWorkbook.Connections("Facility Services")
.OLEDBConnection.CommandText = "SELECT * FROM [SalesByEmployee] WHERE [Market] = '" & Range("C2").Value & "'"
.Refresh
End With
(Just seems silly to bring back all the records from the table and then discard most of them...)
Yeah...at that point though, your changing connection properties and not necessarily dealing with Power Pivot. It would have been nice if one could have done it Power Pivot natively, but I guess Power Query would fulfill that role in future anyways.