Forum Discussion
Unpivot data source for multiple files before stacking all data files into one.
Hi Anonymous ,
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.
Hi Anonymous ,
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.
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 Anonymous ,
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?