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

[jeongkim]

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. 

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

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? 

[V-yubandi-msft]

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]

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]

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]

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.

[jeongkim]

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

[MFelix · 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.

[MFelix]

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.

[jeongkim]

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

[MFelix]

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.

[jeongkim]

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. 

[MFelix]

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?