Paramaterized Power Query/PowerPivot data import problem

[monsoonnut]

Hi I have created a powerpivot report in Excel 2016 that imports data from several parameterized SQL Stored Procedures.
The purpose it to allow individual users to only view relevant data; the model is quite data heavy so I want to limit the number of records at source.


I have created 2 versions of the report both of which have problems:


Version 1: Created using power query to send the parameters - this works well but most users have Internet Explorer 8 and are unable to update to IE 11 as it is not compatible with some legacy sytems so they cannot refresh the data.


Version 2: Created importing the data directly into Powerpivot - I have tried to use VBA to update the parameterized queries but I cannot work out if there is a way to do this as the connection command text is greyed out and I can't find a way to update the table properties in the powerpivot model.


I guess my question is, is there a way to import data without using PowerQuery in Excel 2016 (as I would have done in Excel 2013) so I can amend the connections with VBA or have I entered a bit of a data black hole?


Any help greatly appreciated.

 

[gazpage]

If you have SQL, do you have SSAS? If so you could just create your model there and use RLS to control who sees what.

Otherwise, I suppose there is no chance of getting your users Power BI? Then you could use Power BI templates.