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
Microsoft Power Automate – Compare flow versions
We are announcing the ability to compare flow versions in Microsoft Power Automate. This feature will reach general availability on May 15, 20...
Power Automate – Test subflows with test suite in Power Automate for desktop
We are announcing the ability to create and run test cases for subflows, included in an upcoming test suite enhancement for Power Automate des...
Power Automate: Teams - When I am mentioned in a channel message Trigger
The Power Automate "When I am @mentioned in a channel message" Teams trigger fires for direct mentions in one channel. Polling based, no repli...
Power Automate: union Function
Learn how to use the "union" function in Power Automate to merge arrays and remove duplicates. Covers objects, type gotchas, loop mistakes, an...
Power Apps Code Apps – Trigger a Power Automate Flow
In this article, I walk you through how to add and trigger a Power Automate cloud flow from a Power Apps code app. What type of flows can be a...
Microsoft Copilot (Microsoft 365): Generate documents using forms in SharePoint with Power Automate
We are introducing a new Power Automate action that enables automated document generation using document generation (DocGen) forms in SharePoi...
Microsoft Power Automate – Support for normalized schema import for data ingestion
We are announcing the ability to ingest data in a normalized data structure format for process mining in Power Automate. This feature will rea...
Power Automate – Analyze your processes in the process intelligence experience
We are announcing the ability to analyze your processes in the process intelligence experience in Power Automate. This feature will reach gene...
Power Automate: Planner - Update a task (V2) Action
The 'Update a task (V2)' Planner action upgrades Power Automate with Percent Complete, Bucket Id, and 25 category fields, plus key Preview got...