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.
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.
Then give the below expression:
on Source1's column: true()
on Name as column: replace($$,' ','')
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,
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 moreRelated posts
Simplifying Power Automate Connection Reference Updates with PowerShell
Managing Dataverse solutions and Power Automates often involves tasks like extracting, unpacking, and updating components. These tasks can be ...
December 2024 update of Power Automate for desktop
Power Automate for desktop comes with new great additions in 2024 December’s release, including the introduction of the ability to repair flow...
Power Automate – Use workflows on calendar events in Teams and Outlook announcement
We are announcing the use of workflows on calendar events in Teams and Outlook Web feature. This feature allows you to create flows on existin...
Sequential Approval in Power Automate
Sequential Approval is a powerful feature in Microsoft Power Automate that allows organizations to ensure decisions are made in a structured, ...
Power Automate – Share desktop flows including connectors with embedded connection references
The share embedded connector actions feature for Power Automate desktop is being released. This feature enables owners to select which connect...
Automating PDF Generation with Dynamics 365 CRM Data Using JSON Mapping and Encodian in Power Automate
In this blog, I will showcase how to seamlessly integrate Dynamics 365 CRM with Encodian to automate the creation of PDFs populated with CRM d...
Sequential Approval Power Automate
Power Automate Error: Cannot write more bytes to the buffer than the configured maximum buffer size: 104857600
We got an issue in the Production environment, where the Power Automate is not sending the reminder emails to the Contacts and got the below e...
Power Automate – Use Copilot to analyze cloud flow runs
We are introducing the Copilot in Automation Center feature for Power Automate. This allows users to ask natural language questions about clou...