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
Handling Dataverse Customer Columns in Power Automate
In Dataverse, a Customer column is a special type of lookup. Unlike a standard lookup, it can reference either an Account or a Contact. That f...
Understanding Power Automate flow performance
In this post I'm looking at performance of Power Automate flows. For some flows this may not really matter too much, however when you have a n...
How to Automate Project Assignment in Dynamics 365 Using Power Automate and Power Apps Code Apps
This blog demonstrates how to automate project assignments in Microsoft Dynamics 365 using Microsoft Power Automate and Power Apps Code Apps, ...
Power Automate: Select Action
The Select action reshapes arrays in Power Automate without loops. Faster than Apply to each, with text mode and calculated fields for clean t...
New Designer Run After Settings in Power Automate
I will start this post with a warning about the New Designer Run After settings. The steps described in this post do not make it easier to und...
Power Automate: range function
The 'range' function in Power Automate creates arrays of consecutive integers. Use it to replace counter variables and skip the count-vs-end t...
Power Automate: Teams - When I'm @mentioned
The Power Automate "When I'm @mentioned" Teams trigger fires for chat and channel mentions in near real time. Webhook based, fires on replies,...
Power Automate – Debug flows using variable default values
We are announcing the ability to debug flows using variable default values in Power Automate. This feature will reach general availability on ...
Power Automate – Export object-centric process mining data to Microsoft Fabric semantic model
We are announcing the ability to export object-centric process mining data to the Microsoft Fabric semantic model in Power Automate. This feat...