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
Round Robin assignments in Power Automate
When distributing tasks or tickets across a team, fairness matters, and so does flexibility. With Power Automate, we can implement a round rob...
Dynamics 365 Field Service : Use Power Automate cloud flows to automate tasks and enhance Field Service
The field service landscape is evolving fast. Customers demand quicker responses, technicians need seamless access to information, and busines...
How to generate an Excel ID to access data in Power Automate
Everyone uses Excel in one way or another, so the next logical step is to integrate it with Power Automate and create some automation on ̷...
Can Power Automate Run Python Scripts?
Python is one of the most popular programming languages for data analysis, automation, and artificial intelligence. It’s my favorite programin...
Proactive SLA Risk Notifications in Dynamics 365 Field Service Using Power Automate & Plugins
In the world of Field Service, maintaining Service Level Agreements (SLAs) is critical for customer satisfaction and compliance. But technicia...
“Automating Renewal Reminder Emails in Dynamics 365 with Power Automate (Handling UTC Date Filtering)”
Managing subscription renewals is critical for any business offering SaaS products or long-term service agreements. Missing renewal alerts can...