ADF\Synapse Analytics - Replace Columns names using Rule based mapping in Mapping data flows
In real time, the column names from source might not be uniform, some columns will have a space in it, some other columns will not.
For example,
- Sales Channel
- Item Type,
- Region
- Country
- Unit Price
It is a good practice to replace all the spaces in a column name before doing any transformation for easy handling. This also helps with auto mapping, when the sink column names do not come with spaces!
Select transformation in data flow makes it simpler to automatically detect spaces in column names and then remove them for the rest of the dataflow.
Consider the below source, with the given column names.
Here as we can see, few columns have spaces, few columns like Region and Country do not have spaces in it.
Using the below configuration in select transformation, we can get rid of the spaces in the column names with a simple expression.
In the Input columns, Click on Add mapping button, and choose Rule-based Mapping.
Then give the below expression:
on Source1's column: true()
on Name as column: replace($$,' ','')
What it does?
It will return true() for all the columns which have ' ' (space) in it and replace it with '' (no space).
Upon data preview, we get to see the below result,
As we are seeing here, all the columns with spaces are coming without spaces in between.
If not for the Rule based mapping, one would have to manually remove space from all the columns. It would be a nightmare if the number of columns are more! Thanks to rule-based mapping!
Published on:
Learn moreRelated posts
Replace the Excel Online connector with the Microsoft Graph API in Power Automate
If you have worked with the Excel Online connector in Power Automate then you will most likely know the pains of this connector. In this post ...
How to Automate Formula Writing in Power Automate Using Copilot
As developers, we use Power Automate to handle everyday tasks and automate small parts of our work. Flows help reduce manual effort, improve a...
Data Retention Strategy in Power Pages – Automated Archival with Scheduled Power Automate Jobs
As Power Pages portals scale, the amount of data they generate grows exponentially—form submissions, bookings, cases, applications, event regi...
From Business Central to SharePoint: The Right Way to Save PDF Files in Power Automate
If you’re pulling PDF files from Microsoft Dynamics 365 Business Central and saving them to SharePoint using Power Automate, there’s one criti...
How to Build Environment-Aware Flows by Fetching CRM Metadata Dynamically in Power Automate
Stop relying on OData workarounds and use a professional API-based pattern instead. When building flows in Power Automate, you often need envi...
Action ‘Update_a_record’ failed: An error has occurred. No resources were found when selecting for update – Fixing Cross-Company Update Issues in Finance & Operations Using Dataverse Virtual Entities (Power Automate)
Recently, while trying to update the Projects table in Finance & Operations using the Fin & Ops Apps actions in Power Automate, we ran...
Power Automate Forward A Meeting Invite In Outlook
A Power Automate flow can forward a meeting invite to a new attendee in Outlook. ... The post Power Automate Forward A Meeting Invite In Outlo...
Power Automate Generative Actions
Power Automate Generative Actions Build Intelligent Automations with Natural Language and AI 📅 Released: Preview (2024-2025) 🎯 Status: Publi...
Create Excel File in OneDrive Using Power Automate and Insert Records Dynamically from Dataverse
Exporting Dataverse records into Excel is a common requirement in Dynamics 365 and Power Platform projects—whether for reporting, backup, or d...