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

Power Automate – Integrate Copilot in Process Mining analysis

We are announcing the Integrate Copilot in Process Mining analysis feature for Power Automate. This feature enables you to create views and dr...

1 day ago

How to capture user questions asked to Copilot in Dynamics 365 CRM using Power Automate

As Microsoft’s Copilot becomes an integral part of the Dynamics 365 CRM ecosystem, understanding how users interact with it has become essenti...

2 days ago

Use SDK for .NET to bypass Dataverse power automate flows

In this blog post, we’ll explore how to use the SDK for .NET (via Plug-ins or Console Applications) to bypass Power Automate flows with ...

2 days ago

Microsoft Power Automate – Flow run history is missing from the Automation Center

Reports notified us that as of April 1, 2025, at 12:00 AM UTC, some flow run history may be missing from the Automation Center. How does this ...

2 days ago

How to Fix Missing Line Breaks in Power Automate Desktop for RPA, Email, and Web Automation

If you’ve ever used Power Automate Desktop (PAD) for robotic process automation (RPA), you might have encountered missing line breaks in your ...

2 days ago

Set ‘Regarding’ to any (eligible) table in single update action in Power Automate

A helpful little tip for working with the ‘Regarding’ column in Dataverse Activities & Notes with Power Automate. Setting the value of the ‘...

3 days ago

Power Automate – Create and edit expressions with Copilot

We are announcing the Create and edit expressions feature for Power Automate. This feature allows you to create, edit, and fix your Power Auto...

3 days ago

Power Automate – Upcoming changes to flow sharing experience

Starting in June 2025, we are updating the prerequisites for sharing cloud flows. With this update, users must be members of the environment w...

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