Creating a connection to DB without loading all the data?

[HelpPlease123]

Hi guys,

As the title suggests... is that possible?

When I create a connection only to the DB, when I insert a pivot off the back of that connection it loads every row of data:

Is there any way to only 'load' the data that meet the filter criteria you apply to your table and to 'send the rest back'? kind of like the way you would if you were running queries in say an SQL, if the data doesn't meet the selection criteria it isn't loaded, but it's available for selection if you amend the criteria...

Any tips to make this more efficient would be appreciated.

n.b. all rows needed as this is a refreshable dashboard that can be filtered by stakeholders.

Thanks

 

[Darkzler]

I was thinking of Power Query throughout your message until I read your last row. I am not aware of any way to "unload" data as you describe, perhaps Power BI would be more suitable?

 

[HelpPlease123]

I was thinking of Power Query throughout your message until I read your last row. I am not aware of any way to "unload" data as you describe, perhaps Power BI would be more suitable?

Great thanks - what would your power query method do for me? Any tips to make the workbook run/update faster would be really useful still if you know of any...

 

[Darkzler]

Great thanks - what would your power query method do for me? Any tips to make the workbook run/update faster would be really useful still if you know of any...

Well I thought you could transform your data as much as possible in Power Query with for example "Group by", this lets me reduce the amount of rows greatly depending on the type of data. So I try to reduce the amount of rows and remove unnecessary data in Power Query, then I load it into a pivot table so that the data doesn't have to be presented in a sheet/table. After I've done that I pretty much just set the pivot table up as I want it in a standard view then add slicers so each user can select their own area and delve further into their data.

It doesn't solve your problem but I think it's a good start. That way I've reduced files from XXX Mb to XX Mb which makes it so much faster to work with.

An alternative would be to load all your data into the Data Model and use Power Pivot or different CUBE-functions if you want to manipulate the area between values etc. See this tutorial for CUBE: Excel CUBE Functions can do everything a PivotTable does and more!