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
How to send an automated email in Dataverse using Power Automate?
This article outlines how to automate sending welcome emails to customers upon creating contact records in Dataverse using Power Automate. It ...
Introduction to Managing a WordPress blog using Power Automate – Part 1
My WordPress blog SharePains.com has been running on WordPress for a very long time now, and with so many posts it is a real challenge to make...
Power Automate: mod function
Power Automate’s “mod” function helps you find the remainder after dividing one number by another. While it might not be the...
Update Business Process Flow Stage using Excel Import with Power Automate – Dataverse / Dynamics 365
In some business scenarios, we might need to update the Business Process Flow (BPF) stage of a record during an Excel import — especially duri...
When an Item is Deleted trigger is not triggering in Power Automate
Today Connor and I found that a flow with the When an Item is deleted, was not being triggered. Why could this possibly happen?
Power Automate – Information regarding disablement of the ‘How often do you want to look for new items?’ setting
On March 31, 2025, we temporarily disabled the ‘How often do you want to check for new items?’ setting in Power Automate. We plan ...
Power Automate: dateDifference function
The “dateDifference” function is a powerful tool for when you need to calculate the difference between two dates. It gives you a reliabl...
How to use the Azure AI Foundry connector in Power Automate
Today I had a look into the Azure AI Foundry connector in Power Automate. My findings on this connector are in the post below.
How to Programmatically Add Option Set Values in Dynamics 365 with Power Automate
In every Dynamics 365 or Power Apps implementation, it’s common to require fields where users select values from predefined lists—such as Job ...