Last month, several new features were introduced into Microsoft’s Data Integration capabilities within PowerApps. This empowered users to connect, transform, and combine data from a range of sources and load it into the Common Data Service. From there, the data can be used to create apps, automate flows, and produce analytics. I am going to outline the improvements within data integration below.
Here’s a list of everything that is new:
– Data Integration is now referred to as dataflows
– Support to analytical dataflows
– New Connectors: PDF files, Teradata, MYSWL, 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, and 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
Data Integration = Dataflows
The Data Integration tab in the PowerApps navigation bar is now called “dataflows”. The same applies to all Data Integration projects – they are now called dataflows as well. Microsoft states that the change of name is due to consistency with their dataflow’s capabilities within the Power Platform.
Support for Analytical Dataflows
A new enhancement has been added that allows dataflows containing only Analytical entities to be created. A user can select this option through the “New Dataflow” screen. These dataflows, referred to as Analytical Dataflows, allow free-form entities to be created and provide built-in experiences for merging those entities into standard Common Data Model schemas. They are stored in CDM Folders within Azure Data Lake and can integrate with both Azure and AI services. Analytical Dataflows can be used as building blocks for other dataflows as well. Users can create layers of entities with the end goal of loading them into the Common Data Service. Microsoft has made it known that this is just the beginning of Analytical Dataflows – over time there will be many additions in this area.
New Data Connectors
Four new data connectors have been added within dataflows – PDF files, Teradata, MySQL, and PostgreSQL. Users can retrieve data from a wide variety of sources, such as Files, Databases, Azure, and other services. You can find the new connectors by going to “Choose data source” within the Power Query screen in the Dataflows Wizard.
New Data Transformations
Merge Queries: The Merge Queries dialog now gives users multiple visual ways to select the join kind for a merge operation. Users can access this dialog from the “Combine Tables” menu in the Power Query Editor toolbar.
Fill Up/Down: A “fill” section has been added to the “Transform Column” menu in the Power Query Online Editor toolbar. This allows users to propagate up/down non-empty cell values to empty cells.
Move Columns left/right/beginning/end: Users can move columns within the data preview one position to the left or right. They can also move several columns at once. This can be accessed through the “Transform Column” menu in the toolbar.
Replace Errors: Error values in columns can now quickly be replaced with another value. If a user faces a parsing error, for example, the error can be fixed. Users can ensure that the correct values will be loaded due to the simplification of this process.
Additional Improvements
Select Related Tables: The “Select Related Tables” capability that was recently added is found in the Navigator stage of the Power Query function. Users can easily identify tables that have relationships to currently selected tables. This capability is available for connectors that expose relationship information, such as relational databases, like SQL Server, Oracle, MySQL, OData, and more.
M Intellisense support: Microsoft shared that this is the MOST requested feature from users. The Intellisense support, found in Advanced Query Editor and formula bar, is now enabled when authoring M code in the Power Query Editor.
Descriptive Error Messages: The Power Query Editor error messages have been improved to share more detail from the M Engine. This can help users properly identify and fix advanced errors.
Retracting from the “Edit Queries” stage: This is yet another popular request from dataflow users. Now it is possible to go back from the “Map Entities” stage to the Power Query Editor screen within the Dataflows Authoring Wizard. If a user has forgotten a column or transformation, they can modify the dataflow before adding the entities in the Common Data Service.
It’s safe to say that the new PowerApps features have made Data Integration more user-friendly. It is exciting to see where Microsoft will continue to take dataflows, as the overall capabilities have come so far in such a short amount of time. To engage with the PowerApps Community or suggest an idea of your own, head to the Dataflow Forum.
Let us know if you need any assistance on an upcoming PowerApps project, or if you simply have a question about the updates above!
