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
Lessons Learned in the Automation Workshop: RPA, APIs, and the Rise of CUAs
When you bring together a room full of seasoned RPA developers, a handful of API specialists, and introduce Microsoft’s vision for Computer-Us...
How to make your Excel connector retry in Power Automate
Last week, I came across an issue with actions failing in the Excel connector. Making try Excel Connector retry actions, was slightly harder t...
Power Automate - [Dataverse]
Power Automate is used to automate operations through triggers and actions.Triggers: Added, Modified, Deleted, Manual (Instant), ScheduledActi...
Power Automate – Analyze process mining results with a web client
The Analyze process mining results with a web client feature for Power Automate has reached general availability. This feature allows you to v...
Fixed – Flow not getting triggered / incorrect callback registration record (Power Automate / Dataverse)
Recently, we faced an interesting issue, where we updated an existing flow in our Dev, basically we removed the Filter Rows condition and depl...
Power Automate – Organize desktop flows using tags
We are announcing the ability to organize desktop flows using tags in Power Automate. This feature will reach general availability on Septembe...
Asynchronous flows and Concurrency Control in Power Automate
In this post I will look at Asynchronous flows vs Synchronous flows, Concurrency controls and multiple response actions in Power Automate flow...
2 Actions to run script in Excel from Power Automate flows
Within Power Automate, it has been possible to run scripts in Excel using the Run Script actions. Did you know that the is a Run Scripts from ...
Power Automate Cloud Flows in Power Pages
What are Cloud Flows in Power Pages? Cloud flows allow you to: You configure this using the Power Pages + Power Automate integration via the “...