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

Flows getting triggered multiple times / missing callbackregistration record – Power Automate / Dataverse

Recently, we observed that one of our flows was getting triggered multiple times in our UAT environment; however, the flow was working properl...

21 hours ago

Power Automate – Information about ‘Retry with AI vision’ functionality in Power Automate for desktop

We are announcing the introduction of the Retry with AI vision feature in error handling for UI and browser automation within Power Automate f...

1 day ago

How to Automate Post-Case Resolution Surveys in Dynamics 365 with Copilot and Power Automate (Preview)

Exceptional customer service doesn’t end when a support ticket is closed. In many cases, the most insightful feedback comes after the re...

1 day ago

Power Automate with a Service Principal (App Registration)

A “service account” for Power Automate with a Service Principal (App Registration) Dataverse—implemented as…

1 day ago

Power Automate – Reference previous prompts in Copilot for Power Automate desktop

We are announcing the ability to reference previous prompts in Copilot for Power Automate desktop in Power Automate. This feature will reach g...

2 days ago

Power Pages Solution Architecture Patterns: When to Use Liquid vs. JavaScript vs. Power Automate

In Power Pages, you can implement logic in multiple layers — Liquid (server-side), JavaScript (client-side), or Power Automate (external workf...

7 days ago

New Tool : ‘Cloud Flow Viewer’ for Power Automate Solutions

I’ve added Cloud Flow Viewer tool to my hobby project JayVish Problem Statement: Let’s say you’ve received a Dataverse solution—managed ...

7 days ago

Advanced Error Handling Patterns in Power Automate

When you start building a lot of Flows, you’ll start noticing that only checking values and if the actions succeeded only takes you so f...

8 days ago

Power Automate Cloud Flow | Disable an action

Did you know you can disable a flow action without removing it entirely by turning on the Enable Static Result toggle? When the flow runs, thi...

8 days ago

How to Use the “Search Rows (Preview)” Action in Power Automate for Smarter Dataverse Queries

If you often build automation flows in Power Automate, you’ve probably used the “List rows” action to pull data from Microsoft Dataverse. It’s...

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