Loading...

ADF\Synapse Analytics - Replace Columns names using Rule based mapping in Mapping data flows

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.

 

Screenshot 2024-01-25 at 3.11.13 PM.png

 

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.

Screenshot 2024-02-22 at 4.42.57 PM.png

Then give the below expression:

on Source1's column: true()

on Name as column: replace($$,' ','')

 

Screenshot 2024-01-25 at 3.10.04 PM.png

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,

 

Screenshot 2024-01-25 at 3.17.50 PM.png

 

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 more
Azure Synapse Analytics Blog articles
Azure Synapse Analytics Blog articles

Azure Synapse Analytics Blog articles

Share post:

Related posts

10 Types of For Loops Structures in Power Automate

For loops are one of the basic structures in coding. There are however many different types of loops. How can we implement various types of lo...

1 day ago

Power Automate – HTTP and Teams webhook trigger flows are moving to new URLs

As of August 2025, Power Automate flows and Agent flows (Copilot Studio) with HTTP triggers or Teams Webhook triggers that have logic.azure.co...

1 day ago

Power Automate – Automate browsers using direct control without extensions

We are announcing the ability to automate browsers using direct control without extensions in Power Automate. This feature will reach general ...

3 days ago

Power Automate: How to change the run history from 28 days

By default, Power Automate will retain the history of the runs for 28 days. I don’t have direct confirmation of this, but since 28 days transl...

9 days ago

Power Automate for desktop – Assign custom names to actions in desktop flows

We are announcing the ability to assign custom names to actions in desktop flows in Power Automate for desktop. This feature will reach genera...

9 days ago

Power Automate – Enable version control for desktop flows

We are announcing the ability to enable version control for desktop flows, allowing users to identify changes and their origins with version c...

9 days ago

Power Automate – Quantify time and money savings delivered by automations

We are announcing the ability to quantify time and money saved by automations delivered at flow, environment, and tenant level in Power Automa...

11 days ago

Wave 1 Highlights: What Power Automate & Power BI Offer You This Year

The Microsoft Power Suite has been redefining digital transformation for businesses across industries. Every year, Microsoft introduces fresh ...

16 days ago

Basic – DocuSign for Power Automate

DocuSign is a cloud-based electronic signature platform that lets individuals and businesses sign, send, and manage documents digitally. In th...

20 days ago
Stay up to date with latest Microsoft Dynamics 365 and Power Platform news!
* Yes, I agree to the privacy policy