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: Renew App Registration Client Secret and Update Dataverse Environment Variable Value

Last week, we learned how to create an email notification to remind us if App Registration in Azure expires. Today, we will learn how to apply...

5 days ago

Resolved: Fix connections issue in Power Automate Flow

I got the below connection issues in all the Power Automate flow all of a sudden after opening the Flow. And I followed the below steps and re...

5 days ago

Approvals in Power Apps with Power Automate, Teams, and Adaptive Cards

Table of Contents Introduction Are you looking for a simple and extensible no-code solution for handling approvals in Power Apps? The post App...

7 days ago

Microsoft Power Automate: A Low-Code Solution for Automating Your Processes

Table of Contents Introduction The recurring changes in technology trends are constantly reshaping the business landscape. The race for growth...

7 days ago

Notification For Failed Flow Runs In Power Automate

If you create flows for your organisation, or for clients on projects, you’ve no doubt improved your skills over the years. This might m...

8 days ago

How to Maximize Your Business Efficiency with Power BI, Power Apps, Power Automate?

In today’s fast-paced world, to remain ahead of the edge as a business means ensuring you are ahead in all parameters. Like automating workflo...

9 days ago

Power Automate Save File To A Local Drive

Power Automate can save a file to a local drive but there is a considerable ... The post Power Automate Save File To A Local Drive appeared fi...

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