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

How to send an automated email in Dataverse using Power Automate?

This article outlines how to automate sending welcome emails to customers upon creating contact records in Dataverse using Power Automate. It ...

2 days ago

Introduction to Managing a WordPress blog using Power Automate – Part 1

My WordPress blog SharePains.com has been running on WordPress for a very long time now, and with so many posts it is a real challenge to make...

2 days ago

Power Automate: mod function

Power Automate’s “mod” function helps you find the remainder after dividing one number by another. While it might not be the...

4 days ago

Update Business Process Flow Stage using Excel Import with Power Automate – Dataverse / Dynamics 365

In some business scenarios, we might need to update the Business Process Flow (BPF) stage of a record during an Excel import — especially duri...

6 days ago

When an Item is Deleted trigger is not triggering in Power Automate

Today Connor and I found that a flow with the When an Item is deleted, was not being triggered. Why could this possibly happen?

10 days ago

Power Automate – Information regarding disablement of the ‘How often do you want to look for new items?’ setting

On March 31, 2025, we temporarily disabled the ‘How often do you want to check for new items?’ setting in Power Automate. We plan ...

11 days ago

Power Automate: dateDifference function

The “dateDifference” function is a powerful tool for when you need to calculate the difference between two dates. It gives you a reliabl...

11 days ago

How to use the Azure AI Foundry connector in Power Automate

Today I had a look into the Azure AI Foundry connector in Power Automate. My findings on this connector are in the post below.

12 days ago

How to Programmatically Add Option Set Values in Dynamics 365 with Power Automate

In every Dynamics 365 or Power Apps implementation, it’s common to require fields where users select values from predefined lists—such as Job ...

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