Happy new year everyone! We are kicking off the new year with an update to Power BI dataflows, focused on the dataflows editor.
Power BI dataflows was introduced to public preview in November, and was well received. Since then we’ve been working to increase dataflows adoption even more by introducing new and exciting features.
Today we’re happy to announce many great improvements within the dataflows self-service data prep capabilities, including new data connectors and existing connectors improvements, native SQL queries support, and Power Query Online transformations support.
Read here for how to get started with Power BI dataflows for self-service data prep.
Data connectors:
Dataflows now supports 44 connectors, and counting!
Since Power BI dataflows released we’ve been adding new data connectors continuously. The support for new connectors is a key goal for us, and we are striving (to begin with) for parity with the connectors that are supported in Power BI Desktop.
In the past month, we’ve added UI support for the following connectors:
- MySQL
- Teradata
- Azure Data Explorer
- Active Directory
- Smartsheet
- Sybase
And here is even better news: we’ve added support for many more connectors behind the scenes, even if not available yet in the UI. Here’s the list of additional supported connectors:
- Amazon Redshift
- SAP Business Warehouse
- Azure analysis Services
- Adobe Analytics
- ODBC
- OLE DB
- Folder
- SharePoint Online folder
- SharePoint folder
- Hadoop HDFS
- Azure HDInsight (HDFS)
- Hadoop file HDFS
- Informix (beta)
- Vertica
How can you use these data connectors when they’re not supported in the UI? You can create the connection in Power BI Desktop using the data connectors UI, then copy the M script that was generated to the Power Query Online in the Power BI dataflows editor.
You can read a step by step guide in this article.
Existing connectors improvements
Based on customer requests, we’re also improving existing connectors. Recently we added support on Web Page data connector for Basic authentication, so you can use it providing just a user name and password.
You can find the full list of supported connectors in this link.
Help us to prioritize new data connectors by voting in this Power BI Ideas page.
SQL Native queries:
We have heard from many of you that this is an essential feature and are glad to share it with all of you. We added support for running native database queries. Native database queries save you time when you already have a SQL query written and you want to reuse it within dataflows. If you’re an expert in writing SQL queries, but not yet an expert in using Power Query interface, this can be a big time saver.
This feature is especially useful for using complex queries, where the heavy computation and data filtering can be done on the server to which you’re connected.
To use SQL native queries, you can start by choosing Blank query from the list of connectors, then write the SQL Code.
Another option is to use Power BI Desktop to create the native query, then copy the M script that encapsulates the M and paste it into the Power Query Online Advanced Editor.
The copy can be done in the same way described earlier for copying queries for connectors that aren’t supported in Power Query UI.
Power Query Online transformations:
If you use Power BI Desktop to create queries, we have more good news for you!
The Power Query Online engine, which dataflows use while refreshing, now fully supports all transformations which are supported in Power Query Desktop. Even transformations you don’t yet see in the PQO UI experience.
There are two ways to make this work:
- If you’re a BI Pro and know how to write M, just go for it and write the M transformations in the advanced editor in Power Query Online.
- You can use Power BI Desktop to build the queries and to use the rich UI, then copy the queries (as described in this blog post) to Power Query Online.
To check for support of new and additional transformations, you can, run Record.ToTable(#shared) in Power Query Online to get the full list of supported transformations.
*There’s an exception to the statement above: fuzzy matching (a preview feature in Power BI Desktop) is still not supported in Power Query Online.