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 Add Copilot Studio Knowledge Files Using Power Automate

Copilot Studio knowledge files can be added to an Agent with a Power Automate flow. ... The post How To Add Copilot Studio Knowledge Files Usi...

1 day ago

AI Builder – Actions name changes in Power Automate

The names of the following AI Builder actions have been updated in the Power Automate flow designer: “Create text with GPT using a promp...

3 days ago

Copilot Agent Flows vs Power Automate Cloud Flows – key differences

Agent flows are a powerful way to automate repetitive tasks and integrate your apps and services. But how do they differ from Power Automate f...

3 days ago

Power Automate – Troubleshoot Copilot in cloud flows designer feature

We are announcing the ability to troubleshoot Copilot in cloud flows designer in Power Automate. This feature will reach general availability ...

7 days ago

Power Automate – Use your voice to interact with Copilot

This rollout will happen automatically by the specified dates with no admin action required before the rollout. Review your current configurat...

7 days ago

Power Automate – Configure Entra hybrid join for hosted machine groups

We are announcing the ability to configure Entra hybrid join for hosted machine groups in Power Automate. This feature will reach general avai...

7 days ago

How To Extract Tables From A PDF In Power Automate

Power Automate can extract tables from a PDF with the help of Azure AI Document ... The post How To Extract Tables From A PDF In Power Automat...

8 days ago

Power Automate – Secure credential retrieval in Power Automate for Desktop

We are announcing the Secure credential retrieval in Power Automate for Desktop feature for Power Automate. This feature enhances security and...

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