ADF Mapping Data Flows with Cached sink lookup - unable to make field headers lowercase

martyn johnson 1 Reputation point
2023-01-03T19:58:02.99+00:00

Hi - hope you can help. I have a scenario where .csv files from client change and headers can change. I thought Data Flows might work with schema drift as looked like a good fit. I also have created a lookup file (.json) I want to use to translate the incorrect column header to the correct heading. I have used cached sink.

Issue I have is that I cannot seem to get a match as the headers in my lookup file are lowercase, and the one coming from the customer are a mix of upper/lower. I want to be able to take the current headers and force to lower case as seen below. I want lookup(name) to be lowercase so that it finds a match in prevcolumn, but it doesn't seem to work if I try

!isNull(sink2#lookup(lower(name)).prevcolumn)

275823-dataflow.jpg

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
6,285 questions
{count} votes

1 answer

Sort by: Most helpful
  1. AnnuKumari-MSFT 19,116 Reputation points Microsoft Employee
    2023-01-05T09:51:28.37+00:00

    Hi @martyn johnson ,

    Welcome to Microsoft Q&A platform and thanks for posting your question here.

    As I understand your issue, you are trying to convert the header of source into lower case so that matching could be performed .

    You can make use of Select transformation to update the column names like below:

    276423-image.png

    Hope this will help. Please let us know if any further queries.


    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.
      Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    No comments