I have been helping several customers recently with their reporting performance issues. The reports themselves performed well, but the data refresh took forever.
There are two simple steps in Power Query for Power BI and Excel (Get and Transform in Excel 2016) that can drastically reduce your data refresh times.
After making your connection to your data source and choosing a table, you can simply choose ‘Close & Apply’ to load your table and all its data.
Loading entire tables will bloat your reports with lots of data you will never use. If you know what data you will be reporting on in your report, you can limit the number of rows returned to just the type of data needed.
Let say you are creating a report of your milestone tasks in Project. Before clicking ‘Close & Apply’ and loading the entire table, locate the TaskIsMilestone column and choose only rows that are marked ‘TRUE’.
Click on the down arrow in the column heading and make sure TRUE is checked and FALSE is unchecked and click OK.
In your APPLIED STEPS section, you will notice a new step called ‘Filtered Rows’. If the results aren’t what you are looking for, simply delete the step and start over.
If you know what columns you want in your report, you can remove unneeded columns using the ‘Choose Columns’ button. You should keep any ID columns in your data to make sure you can accurately merge and join tables.
I recommend unchecking ‘(Select All Columns)’ and choosing just the columns you need. Notice that I selected all the columns that ended in ‘Id’. Once you choose the columns needed, click OK. Notice your applied steps now has an additional step called ‘Remove Other Columns’.
If in the course of building your report, you find you need additional columns or rows, just edit your query and click on the appropriate applied step to edit your selection.
The amount of time savings for refreshing your data can be staggering, especially for anyone who is remotely refreshing data. In one report I worked on, the refresh data time went from 15 minutes to 15 seconds!
