Get transformed data from Power Query

[ale259]

Hello,

Thank you for this great community. I have a question regarding the transformed data in Power Query. In my previous example, I have transformed several Excel files through Power Query to do the ETL process. I am having questions regarding the loading process. Since all of this transformed files are stored in 1 folder, and I use this folder as the base source for the combined Excel masterfile, I wonder, how to only keep the transformed Excel file only. What I mean it, event though in the folder, it shows I have only 3 sheets (at the moment, but in the future there will be more). But when I try to consolidate all the files, the number of tables are more than the number of Excel file, since the excel file is transformed with Power Query, so my guess is it loads all tables, including the original one. 

I have some photos for illutrations:

Original files: 3 files:

Expanded table: 4 tables.

Would there be a way to only get the transformed table from the Excel file, or would be be a way in the original excel sheet, to only keep the transformed table. This would make the consolidating process much smoother and automatic. I hope this makes sense. Thanks so much. 

[v-tsaipranay · Accepted solution]

Hi @ale259 ,

Thank you for reaching out to the Microsoft Fabric Community.

The issue happens because Power Query is loading both the original data and the transformed tables from your files this happens by default when you combine files from a folder. To ensure only the transformed tables load into your master file, you can filter them directly in Power Query. After loading the folder (Data > Get Data > From Folder), go to the "Name" or "Item" column that lists each sheet or table, and apply a filter to keep only the transformed tables.

This will ensure only the cleaned, final data is pulled into your master file, and the raw tables are excluded. If you prefer to clean up the source files directly, you can move the transformed data to a dedicated sheet and delete any raw data sheets, ensuring only the transformed sheets remain. Either approach will make your consolidation process smoother and ensure only the transformed data loads, even as you add new files in the future.

I hope my suggestions give you good idea, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[v-tsaipranay]

Hi @ale259 ,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

If my response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[lbendlin]

It doesn't make sense to me. Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?