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

Microsoft Power Automate – Testing framework for desktop flows

We are announcing a testing framework for desktop flows in Microsoft Power Automate for desktop. This feature will reach general availability ...

7 hours ago

Run Power Automate From Command Button

Requirement Modern business applications demand automation, whether it is a heavy data driven model drive app or light weight canvas app. Micr...

15 hours ago

Favorite your Actions and Connectors for quick retrieval in Cloud Flows (Power Automate)

With this new update in Power Automate, it’s now easier for us to find and use the actions and connectors we need. We can also quickly access ...

6 days ago

How to update environment variables automatically with Power Automate

The other day, I cool question that I didn’t have the answer to, but got me super excited to find a solution. How can we … The po...

7 days ago

Power Automate: dayOfYear function

The “dayOfYear function” helps us determine which day of the year a specific date falls on. This might not be the first function t...

12 days ago

Power Automate: How to Get Your Flow’s Name

You might need to access your Flow’s name when building complex Flows for various reasons. You may create a custom logging system, want ...

13 days ago

Power Automate: coalesce function

The “coalesce function” is one of those hidden gems in Power Automate that can save you much time and make your flows more robust....

19 days ago

Power Automate Add Attendee To Meeting And Don’t Email Others

In Power Automate you can add a new attendee to a meeting without emailing all ... The post Power Automate Add Attendee To Meeting And DonR...

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