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
Power Automate – Improved Power Automate licensing dashboard
We are announcing an improved licensing dashboard in Power Automate. This feature will reach general availability on August 30, 2026. How does...
Microsoft Power Automate – Create and visualize custom KPIs in the process intelligence experience
We are announcing the ability to create and visualize custom KPIs in the process intelligence experience in Microsoft Power Automate. This fea...
Microsoft Power Automate – Create and visualize custom KPIs in the process intelligence experience
We are announcing the ability to create and visualize custom KPIs in the process intelligence experience in Microsoft Power Automate. This fea...
Creating AI-Powered Workflows Using Power Automate and Azure AI
Introduction Artificial Intelligence is revolutionising business automation by enabling organisations to build workflows that not only automat...
Power Automate: Office 365 Outlook - Get emails Action
Returns an array of emails from a mailbox folder. Filter fields only scan the first 250 emails.
Power Automate: convertTimeZone function
Converts a timestamp between two time zones, daylight saving included. Output drops the zone marker.
Power Automate mobile apps, the Excel add-in and personal accounts are going away
Power Automate is dropping the mobile app, the Excel add-in and personal accounts. What changes and when.
SharePoint Permissions in Power Automate
Which account really runs each action, and why a flow that works for you returns 403 for everyone else.
Power Automate: Office 365 Outlook - Export email Action
Exports an Outlook email as a binary EML file by Message ID. Attachments are included inside the file.