What’s New in Dataflows and Data Integration – September 2019

Miguel Llopis, Principal PM Lead - Power Query and Dataflows, Πέμπτη, 19 Σεπτεμβρίου 2019

We’ve released lots of new features and enhancements to our Data Integration capabilities in PowerApps recently, enabling users to seamlessly connect to, transform and combine data from a wide range of data sources and load it into the Common Data Service, from where it can be used for creating apps, automated flows or analytics.

Here’s a recap of everything that’s new:

“Data Integration” renamed to “Dataflows”
Support for Analytical dataflows
New Connectors: PDF Files, Teradata, MySQL and PostgreSQL
Merge Queries – Visual join kind selection
Fill Up/Down
Move Columns left/right/beginning/end
Replace Errors
List Transforms: Statistics, Sort, Keep/Remove/Reverse items
Select Related Tables as part of Get Data
M Intellisense support in Advanced Query Editor
More descriptive error messages within Power Query Online
Support for going Back from “Map Entities” to the “Edit Queries” stage.

You can continue reading below for more details about each of these new capabilities.

“Data Integration” renamed to “Dataflows”

We’re renaming the “Data Integration” tab in the PowerApps navigation bar to “Dataflows”. Data Integration projects are now also called “Dataflows”. This change provides naming consistency with our Dataflows capabilities available across the Power Platform.

Support for Analytical dataflows

This month we’re introducing an enhancement that allows creation of Dataflows containing only Analytical entities. This option can be selected within the “New dataflow” screen.

Analytical dataflows allow free-form entities to be created, but also provide built-in experiences for harmonizing those entities into standard Common Data Model entity schemas, using the “Map to standard entity” dialog accessible from the Power Query Editor. Dataflow entities are also stored in CDM Folders within Azure Data Lake, allowing integration with other Azure Data and AI services, analytics and insights scenarios.

Analytical dataflows can also be used as intermediate building blocks for other dataflows. This allows organizations to create multiple layers of refinement and specialization of entities within an environment that can be ultimately loaded into the Common Data Service.

Please note that this is just an initial release of these new Analytical dataflows capabilities. We’re planning to make significant additions to this area over time. Stay tuned.

You can find more information about Analytical dataflows in our documentation articles: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-and-use-dataflows

New Data Connectors – PDF Files, Teradata, MySQL and PostgreSQL

Four new data connectors have been recently enabled within Dataflows, allowing users to get data from an increasing number of data sources, spanning across Files, Databases, Azure and other online services.

You can find these connectors within the “Choose data source” screen in the Dataflows wizard.

New Data Transformations

Over the past few weeks several new data transformation capabilities have been added within the Power Query Online Editor stage of the Dataflows authoring wizard, including:

Merge Queries – Visual join kind selection: The Merge Queries dialog now provides more visual ways to select the join kind for a given merge operation. This dialog can be accessed from the “Combine Tables” menu in the Power Query Editor toolbar.
Fill Up/Down: A new Fill section has been added to the “Transform Column” menu in the Power Query Online Editor toolbar, allowing customers to propagate up/down non-empty cell values to all consecutive empty cells. These two transformations can also be accessed from the right-click context menu for a column in the data preview.

Move Columns left/right/beginning/end: We’ve added options to allow users to easily move columns within the data preview, either one position to the left or right, or all the way to become the left-most or right-most column in the table. Note that these options are available also when selecting multiple columns at once. These capabilities can be accessed via the “Transform Column” menu in the toolbar or the column context menu in the data preview.

Replace Errors: Another new transformation added this month to the “Transform Column” menu allows users to quickly replace error values within a column with another value, such as a default. Error values can happen under many circumstances, such as parsing errors when trying to convert a column from type text to number or date, for example. By using Replace Errors, users can ensure that there’s a good fall back value to be loaded. Alternatively, users can leverage the Keep Errors and Remove Errors options under the “Reduce Rows” menu to narrow down rows in a table to only the ones with or without errors.

List Transforms: Statistics, Sort, Keep/Remove/Reverse items and more – When working with lists in the Power Query Editor, users can apply several different transformations (check the full M reference for details), but up until a few weeks ago the Power Query Editor did not expose such options via the UX. With our recent updates, most commonly used transformations are now easily accessible from the Power Query Editor toolbar.

Other authoring improvements

We’ve made a few other improvements across different areas of the Dataflows Authoring experience:

Select Related Tables as part of Get Data: Earlier this month, we released an update that lights up the “Select Related Tables” capability within the Navigator (or Choose data) stage of the Power Query experience. This option allows users to easily identify and select all tables that have relationships in the data source to the currently selected tables. This new “Select Related Tables” option is available for those connectors that expose relationships information, such as relational databases (SQL Server, Oracle, MySQL, etc.), OData and other sources.

M Intellisense support in Advanced Query Editor and Formula Bar: One of the most frequently requested features from users has been Intellisense support when authoring M code in the Power Query Editor. Recently we enabled M Intellisense in the Advanced Query Editor (accessible via the right-click menu on queries within the Queries pane) and the formula bar.

More descriptive error messages within Power Query Online: We have improved the error message information within the Power Query Editor, so that error details from the M Engine are now also exposed to users. This enables better diagnostics of errors.

Support for going Back from “Map Entities” to the “Edit Queries” stage: Another very frequently requested usability enhancement from Dataflows users was the ability to go back from the “Map Entities” to the Power Query Editor screen within the Dataflows authoring wizard. This is a common action given that users might have forgotten certain columns or transformations to be applied before they can successfully map to target entities in the Common Data Service.

That’s all for this month. We hope that you find these features useful and are looking forward to your feedback.

Please let us know if you have any suggestions for how to improve Dataflows by using the Dataflows category in the PowerApps Ideas Forum: https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas/label-name/dataflows