topic Same data transformations to be done on multiple files? in Dataflow

Same data transformations to be done on multiple files?

alee023 — Thu, 07 Mar 2024 22:40:30 GMT

I receive a CSV file on a monthly basis, and I would like to perform the same exact transformation on every new file I receieve. For example, keep certain columns, do calculations based on those columns, etc. I only know how to set up Dataflow with the source pointing to a selected file.

Is something like pointing the source to a Sharepoint folder (where I will drop the monthly file) then running the dataflow possible? For example, I will upload Jan2024.csv to the folder, run the dataflow which will apply the transformations, then export the data to Excel. Then in February, I will remove the Jan2024.csv file and upload Feb2024.csv (since I'm not sure if dataflows know how to use the new data only), rerun the dataflow/pipeline, etc.

Or would something like this be handled on the pipeline level? For example, I use Copy in pipeline with the source pointing to a Sharepoint folder, copy to and replacing the previous month's data in Lakehouse, then create a new Dataflow with the source pointing to the Lakehouse?

Re: Same data transformations to be done on multiple files?

v-cboorla-msft — Fri, 08 Mar 2024 12:24:49 GMT

Hi alee023

Thanks for using Microsoft Fabric Community.

As I understand that you want to use the same type of transformations on every new .CSV file that you will receive on monthly basis.

If the schema of all the .CSV files are similar, then the two scenarios which you have mentioned above would work.

For your scenario, if simplicity is your main priority Option 1 (Dataflow with SharePoint Source) might be sufficient. No matter which approach you choose, you'll achieve the desired result of automatically transforming your monthly CSV files while maintaining a clear separation between data source management (SharePoint) and data transformation logic (Dataflow).

I hope this information helps. If you have any further questions please do let us know.

Thanks.

Re: Same data transformations to be done on multiple files?

v-cboorla-msft — Mon, 11 Mar 2024 15:15:05 GMT

Hi alee023

Thanks.

Re: Same data transformations to be done on multiple files?

v-cboorla-msft — Tue, 12 Mar 2024 14:01:30 GMT

Hi alee023

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread.

Thanks.

Re: Same data transformations to be done on multiple files?

divyamalhotra12 — Wed, 27 Mar 2024 01:59:20 GMT

Hi,

I am facing a similar issue. I have a dataset wherein I applied a few transformations such as change data type for all columns, choose columns etc. Now, I want the data to be updated and the same transformations to be applied there. For instance, I have a file called, 'Orders'. Now, I have an updated version of the same file in sharepoint. How do I proceed with updating the same in dataflow and then ensuring the transformations get applied to it.

I wanted to understand the steps to perform the same. Could you please help me with the same.

Re: Same data transformations to be done on multiple files?

alee023 — Wed, 27 Mar 2024 02:51:37 GMT

Here's what I did:

My files were uploaded to SharePoint, so my dataflow source was to the sharepoint folder. I'm not sure how this works if it were OneDrive.
Filter the dataset to the correct folder path - because the source doesn't allow you to connect to specific subfolders, so you deal with it in this step
Filter the dataset to get the most recent file (sort by date created, descending)
Find the 'Combine files' option in the menu. This will extract all the data from the file