Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jeongkim
Post Prodigy
Post Prodigy

Unpivot data source for multiple files before stacking all data files into one.

Hi,

 

Im using Listed Columns to get data without specifying column names and numbers.

My data comes every week by new excel files respectively and the thing is sometimes column names or numbers or order could change quite often. 

 

e..g not real just imagine worst case

W1

ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF

 

W2

ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF ColumnG

 

W3

ColumnA ColumnC ColumnD ColumnE ColumnF

 

W4

ColumnC ColumnB ColumnD ColumnE ColumnF ColumnA

 

W5

ColumnC ColumnZ ColumnD ColumnE ColumnF ColumnA

 

 

I cannot stack all the file data since columns are changing its order and name.

Can we unpivot them before Expand data table as below from each files?

if all the values are unpivotted at the beginning then it will be no issue with column number/order changes.

But as I mentioned, every week new files come and should be cumalative, I cannot command this query every week. 

 

 

WeekCategoryValue
1ColumnA00
1ColumnB00
1ColumnC 
 (skipped)00
5ColumnC00
5ColumnZ00
5ColumnD00

 

 

This is the sample of my data that staking all files but number/name of column header are changing. 

Only 2 Category cases(5G and LTE) shown in snapshot but more than 30 types of it so I cannot split data table for each category neither.  although I split them, unexpectable column name/number changes would happen in the future. 

 

I think only way is unpivotting this data to the format as above table and then stacking all files. 

Any suggestion pls? 

 

jeongkim_1-1745310467508.png

 

 

1 ACCEPTED SOLUTION

Hi @jeongkim ,

 

I understand the request you have however I believe I'm not being very clear on how Power Query can solve the problem.

 

When you get data from a file you first must open that file and then make the several steps you need to make the changes, this means that you need to open each file and proceed to the pivot of the tables and then unpivot based on all the names you will get.

 

This can be achieve automating the process of accessing each file using a custom function that will allow you to return the data inside each file in the format you refer:

Week Category Value
1 ColumnA 00
1 ColumnB 00
1 ColumnC  
  (skipped) 00
5 ColumnC 00
5 ColumnZ 00
5 ColumnD 00

 

After this you can continue with additional steps.

 

The custom function may allow you to do a more simpler or complex treatment of the data based on if you want have many steps replicated or just some simple changes and then the complex ones to be applied to the full set of files after the first interaction.

 

If you allow me the question once again what is the type of file you are dealing with belive it's not reference on your initial post and I can send you an example of what I mean.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

11 REPLIES 11
V-yubandi-msft
Community Support
Community Support

Hi @jeongkim ,

Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.

V-yubandi-msft
Community Support
Community Support

Hi @jeongkim ,

We haven’t received a response yet and want to ensure the solution met your needs. If you need any further assistance, feel free to reach out we’d be happy to help. If everything is working as expected, kindly mark it as Accepted as solution.

 

Thank You.

V-yubandi-msft
Community Support
Community Support

Hi @jeongkim ,

Could you please review the recent solution shared by @MFelix , and let me know if you have any questions or need further clarification?
Thank you for your valuable inputs @MFelix .

V-yubandi-msft
Community Support
Community Support

Hi @jeongkim ,

@MFelix , has provided a good solution that aligns with your requirements. Do let us know if you need any additional information or further assistance. Have you been able to resolve this issue?

If yes, kindly mark the helpful response as the accepted solution and give it a 'Kudos' to assist other community members in finding it more easily.

 

it's not that I asked, my qeury is not asking how to do unpivot.

Hi @jeongkim ,

 

I understand the request you have however I believe I'm not being very clear on how Power Query can solve the problem.

 

When you get data from a file you first must open that file and then make the several steps you need to make the changes, this means that you need to open each file and proceed to the pivot of the tables and then unpivot based on all the names you will get.

 

This can be achieve automating the process of accessing each file using a custom function that will allow you to return the data inside each file in the format you refer:

Week Category Value
1 ColumnA 00
1 ColumnB 00
1 ColumnC  
  (skipped) 00
5 ColumnC 00
5 ColumnZ 00
5 ColumnD 00

 

After this you can continue with additional steps.

 

The custom function may allow you to do a more simpler or complex treatment of the data based on if you want have many steps replicated or just some simple changes and then the complex ones to be applied to the full set of files after the first interaction.

 

If you allow me the question once again what is the type of file you are dealing with belive it's not reference on your initial post and I can send you an example of what I mean.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix
Super User
Super User

Hi @jeongkim ,

 

If you use the unpivot without defining the column names you are able to get what you need something similar to this:

 

= Table.UnpivotOtherColumns(Previous_Step_Name, {}, "Attribute", "Value")

 

Not sure what is the type of file you are refering a XLSX or a CSV but depending on it you may need some additional steps.

 

What you can do is a Files From folder and then make the transformation using the Function using the previou code has the last step.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



My query is how to unpivot data in backend before expanding data table. 

Hi @jeongkim ,

 

If you don't get inside the file you cannot unpivot your data since Power Query needs to have access to the data before doing transformations to it.

 

Using a custom function you can do it for each file without impacting all the other and then get the data unpivot as a final result.

 

What type of files are you working with? (CSV?, Excel?) I can do a example file for you to check if it works for you.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



my purpose is not opening the files manually for automation linking to power automate. 

Thanks though, it seems there is no solution for my query. 

HI @jeongkim ,

 

I apologize if I did not make myself clear when I refer to open each file is not manually you can use Power Query to make the unpivot of the data before appending them all. 

What you need to create is a custom function in the power query that does the unpivot for the file.

I ask you again do you want a mockup of this type of setup?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors