Hi!
I'm connecting MS Excel to MS SQL Server via PowerQuery. The database is 9 years old. I need only last 2, 3, 4 years of data or given subset of data. (Refreshing 9 years takes to much time ...).
-->Is there a way to limit the data range (date) while creating connection to MS SQL Server?
--> Is there a way to chose given variable value to read/display while creating connection to MS SQL Server?
--> Can it be done with point and click interface or only programming interface?
--> Where to find point and click or programming interface?
--> If programming interface is M or DAX programming language?
--> Is there any training that covers all this subjects?
Kind regrads, Luke
Ps. If these subjects have already been discussed on the forum sorry for spamming and please point me to the proper post. Thank you for your time and attention.
Hi Luke,
Q. -->Is there a way to limit the data range (date) while creating connection to MS SQL Server? & --> Is there a way to chose given variable value to read/display while creating connection to MS SQL Server?
A. When you connect Power Query to a data source it only gets a preview of the first 1000 rows of data and displays that in the Power Query editor. It does this to ensure the editor is responsive to changes you make while editing the data.
In the editor you can filter out data you don't need, like the previous 5 years. You can do the filtering using the filter drop down buttons on the column headers. Find the column containing the date/year attribute and filter out the periods you don't want.
When you 'Close and Load' the data, Power Query will only load the data you've selected.
If you've done this and refreshing is still taking a long time, then it could be another issue. This thread has some tips on other areas you can look at to improve refresh times.
Q. --> Can it be done with point and click interface or only programming interface?
A. Yes, you can do this in the Power Query editor window using the filter buttons as described above.
Q. --> Where to find point and click or programming interface?
A. The Power Query editor window opens when you connect to data and select 'Transform'. You can also open the Power Query editor from the Data tab > Get Data > Launch Power Query editor.
Q. --> If programming interface is M or DAX programming language?
A. Power Query uses the M language.
Q. --> Is there any training that covers all this subjects?
A. I have a comprehensive Power Query course here.
I hope that helps.
Mynda
