eWEEK content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Microsoft has issued an update to Power Query, an Excel add-on that connects to the company’s self-service Power BI offering.
Power BI for Office 365, first introduced in 2013 and formally launched last year, is a cloud-based business intelligence platform that allows rank-and-file office workers to explore and glean insights from large data sets using the popular Excel spreadsheet program. Now, Microsoft is focused on opening up the platform to more data sources and fine-tuning the user experience.
First among the new Power Query updates is a new Open Database Connectivity (ODBC) connector, announced Miguel Llopis, a Microsoft Power BI program manager.
“This new connector can be found under ‘From Other Sources > From ODBC’ and it allows users to connect to their generic ODBC providers,” he said in a statement. “This makes it possible for users to bring in data via Power Query from several data sources that are not natively supported.”
A new dialog box asks users to input connection parameters and an SQL statement. Llopis cautioned that users will want to avoid including their credentials during this step.
“Note that credentials should still be entered on the credential page, rather than in the Connection String field above, to ensure credentials are not inadvertently shared with the query,” he explained. “Similar to other SQL Statement connections, Power Query will not be able to optimize query steps after the SQL statement so it is suggested that you optimize the original statement as much as possible (i.e. include ‘group by’, ‘join’ operations, etc.).”
The January Power Query update also tackles navigating the growing list of Power BI data sources.
“With the addition of several new data sources in the past few months, we started to hit issues related to the amount of entries under the ‘From Other Sources’ dropdown menu,” causing the list to become unmanageable, Llopis said. To remedy this, the Power BI team has “decided to create a new top-level menu for Azure-based data sources to balance the number of entries in each dropdown menu.” Although the number of data sources remains unchanged this time around, the tweak “improves navigation, discoverability and ease of access for our data sources lists in the ribbon.”
To improve responsiveness, Power Query now offers the option to turn off relationship detection while loading queries into the Data Model.
“These relationships might exist in the data source (i.e. SQL Server) or might be implicitly created by the user as part of their query steps. For instance, merging two queries would make Power Query detect a relationship between these two queries, which would be automatically created in the Data Model when these queries are loaded,” Llopis said. The new workbook-specific option reduces load times for users who don’t require the functionality or don’t need it from the start.
Rounding out the new features are column and row counts in the Query Editor Preview, a confirmation dialog for deleting queries to prevent accidental data loss and a new workbook settings dialog box that takes the place of ribbon menu items that formerly provided that functionality.