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
The next frontier in enterprise intelligent automations with Microsoft Power Automate
Copilot in Power Automate makes it faster to build solutions that save valuable time and reduce costs, thereby achieving business outcomes. Th...
Power Automate – Power Fx integration with Power Automate for desktop
Get ready to experience the magic of low-code language with Power Automate for desktop! The exciting news is the integration of Power Fx scrip...
Copilot for Power Automate – Copilot provides more accurate answers to questions
The new feature of Copilot for Power Automate, "Copilot provides more accurate answers to questions," has been announced. This feature will le...
[Power Automate] Getting Unique Records or Items from an Excel Table
Level of Difficulty: Beginner – Senior. In today’s fast-paced world, managing data is important, and Excel is a common tool for organizi...
Create A Connection To ServiceNow In Power Automate
Power Automate can connect to ServiceNow to get records and perform other CRUD operations (create, ... The post Create A Connection To Service...
Microsoft Power Automate customer managed key support now generally available
We are excited to announce the general availability of Microsoft Power Automate customer managed key (CMK) support for environments with flows...
Power Automate – Copilot created flow regeneration feature
Power Automate is introducing a new feature on December 13, 2024 that will allow users to regenerate flows created by Copilot. This means that...
November 2024 update of Power Automate for desktop
Power Automate for desktop comes with new great additions in 2024 November’s release, including general availability of cloud connectors and P...
Power Automate Excel Write Value To A Specific Cell Or Range
Power Automate can write a value a specific cell in Excel without that cell being ... The post Power Automate Excel Write Value To A Specific ...