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
Your flow has a new trigger URL in Power Automate
Recently I've been receiving email reporting Your flow has a new trigger URL. In this post I will address this issue and how to avoid your flo...
Open an app in Power Automate Desktop
When you open an app in Power Automate Desktop, and you need to run this Power Automate Desktop flow multiple times it becomes important to ha...
Automating Business PDFs Using Azure Document Intelligence and Power Automate
In today’s data-driven enterprises, critical business information often arrives in the form of PDFs—bank statements, invoices, policy document...
Default retry settings in Power Automate
When actions in Power Automate flows go wrong, they may rerun depending on the failure occurred. One of the errors that may trigger is the 429...
Compress photos in Power Automate
One of my clients, takes many photos as part of their data entry process using a Canvas app. We need to compress the photos as the collected d...
Create MS Graph subscriptions in Power Apps without Power Automate
A while back I wrote a post about MS Graph subscriptions in Power Automate. Today I managed to do this directly within Power Apps.
Input and Output Parameters for Child Flows in Power Automate
In this post, I will look at the input and output parameter types for child flows. Recently I hit a couple of small issues with the various d...
5 fundamental Power Automate ideas that will make your flows better!
It's time to have a look at some of my older posts. In this post I'm going to have another look at these Power Automate ideas. Back in 2017/20...