Automate dataflow transformations using a single mapping sheet to easily connect, clean, and organise data from different sources in Microsoft Power Apps.  You can clean, change, and organise this data, then save it in Microsoft Dataverse. 

Dataflows help you automatically bring data together and prepare it for use in apps, reports, or business tasks within the Power Platform. 

Getting Started with Dataflows


A dataflow is made up of several tables that you can build and manage right inside your Power Apps environment. You can easily add new tables, make changes to existing ones, and set up automatic refresh schedules—all from the same place where the dataflow was created.  

Using SharePoint Sites for Automated Dataflow Transformation 

You can use SharePoint sites to automate data transformation by working with two Excel files stored on different sites. One file contains the raw data that needs to be changed, and the other file has a mapping sheet that tells how to rename or adjust columns for the final output. 

While Excel and XML can also be used for this task, SharePoint makes the process smoother and more automated. 

Efficient Data Transformation with a Single Mapping Sheet 

When dealing with many files on SharePoint, transforming data again and again can be time-consuming. A better way is to use one mapping sheet that includes all the transformation rules. This single sheet can be reused across different files using one query, making the process faster and more consistent. 

For example, if your data has a column called “Shift” with values like “Shift 1,” “Shift 2,” etc., the mapping sheet can convert these into names like “Day,” “Evening,” and so on—across thousands of records—all in one go. 

Applying Transformations in the Destination Sheet 

In the destination Excel sheet, you can add a custom column and use a query to apply the transformation. This query looks at the mapping sheet and automatically fills the new column with the correct values. This way, you don’t have to manually update anything. 

To make this process smooth and accurate, we use a specific query that handles the transformation efficiently. 

Let 
Source = SharePoint.Files(“your share point URL“, [ApiVersion = 15]), 
MappingTable = Table.SelectRows(Mapping, (row) => row[Source Column Name] = [Shift])[Value]{0}, 
Result = Table.SelectRows(Mapping, (row) => row[Source Column Name] = [Shift])[Value]{0} 
in 
Result 

Here: 

  • Mapping → The table name. 
  • Shift → The destination column name where original data should be placed. 
  • Source Column Name → The column name from the mapping sheet, representing the original data. 
  • Value → The transformed values that should replace the source data. 

Viewing the Transformed Data in the Destination Sheet 

Once you add the custom column and run the query, a new column appears in the destination sheet showing the updated values. These values are based on the rules from the mapping sheet. This confirms that the data has been changed correctly and automatically, just as planned. 

Streamlining Data Transformation with a Single Mapping Sheet in SharePoint 

By using one centralized mapping sheet stored in SharePoint, you can manage data transformations across multiple Excel files more efficiently. This method simplifies the process by keeping all transformation rules in one place, reducing repetition, and ensuring consistent results. With the help of custom columns and queries, data in destination sheets can be transformed automatically saving time and minimizing errors. 

Important Note 

One limitation to be aware of is that you can’t use multiple files from the same SharePoint site in a single transformation. However, you can use files from different SharePoint sites in one dataflow. In this case, a single mapping sheet from one site can still be used to manage transformations across all sites, keeping the process centralized and efficient. 

Final Words 

Automating data transformations in dataflows using a single mapping sheet is a smart way to handle complex data tasks. It helps you stay organized, improves accuracy, and cuts down on manual work. By taking advantage of SharePoint and Power Platform tools, you can make your data processes faster and more reliable. 

Need help getting started? Reach out to our team at support@stallions.solutions —we’re here to support your data transformation journey!