Power BI dataflows January updates.
- Amazon Redshift
- SAP Business Warehouse
- SAP HANA
- Analysis Services
- Azure analysis Services
- Google Analytics
- Adobe Analytics
- OLE DB
- SharePoint Online folder
- SharePoint folder
- Hadoop HDFS
- Azure HDInsight (HDFS)
- Hadoop file HDFS
- Informix (beta)
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.
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 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.