Can you upload a sample file with the structure you described?

Catalin,

I get this data in the form of Excel workbook from folder in my organization periodically.

Here is the link:

https://drive.google.com/file/d/1iA9t5ZJwSg1CdwubX9yI9pIkq4wkdUOP/view?usp=sharing

thanks for you effort.

Srinivas

See an example attached.

I have separated the headers from data, joined them back, then appended the data.

(note: you can attach a file to forum message, links to external files will become broken in short time)

Catalin,

Here is the link to the folder that has the target files. These are what I will receive everyday and I need to unpivot them every time. Sometimes there will be 10 files like these. But all are uniform in layout, not a single alphabet miss the queue. We can easily unpivot a single file, but for multiple files, the normal unpivotting process won't work out.

So please download the two files and do the un-pivoting process taking the folder as the source. For me it did not workout. Please check.

Thanks

https://drive.google.com/drive/folders/1cJu_W-Xr1AeP1aCVzoYhWtsV5QBbW1fx?usp=sharing

that folder has one file only, not 2, please check again.

Please upload the files here in forum, no need to add links here.

There is an attachment button below the message box.

Anyway, I duplicated one of the files, check the attached file.

The process goes like this:

The main query gets the files from the folder. We add a new column with a formula that passes the content of the file to a function that transforms it to the format we need.

How to build this structure:

1. Create 4 separate queries: (Use simple names, no spaces)

- a query from folder (this will be the main query, will call the function: query from file)

- a query from file, that will list all sheets from that file (this will be converted to a function that calls the getsheetdata function and the getheaders function).

- a query that will process 1 sheet only to extract data (this will be converted to a function)

- a query that will process 1 sheet only to extract headers (this will be converted to a function)

2. Convert last 3 queries to functions

To convert a query to a function, you have to add a single line of code ABOVE the first "let" statement from the query:

(TheVariable)=> //use instead of TheVariable a relevant name for your operation, then replace inside query the hard typed text with this variable argument:

//your existing query:

let

Source=File.Contents(TheVariable) //this usually looks like: File.Contents("c:/Folder/test.xlsx") 

Depending on the data type you will pass to a function, you will have to remove first row (or rows) from the original query:

- if you pass a string, you don't have to remove rows, just replace the hard typed string with your function parameter.

- if you pass a table, locate the row in the query where the step requires a table as input, remove previous steps, then replace the reference with the variable parameter

if you pass binary content, locate the row in the query where the step requires binary data as input, remove previous steps, then replace the reference with the variable parameter

Example of binary data: File.Contents("c:/Folder/test.xlsx") (FileContents formula result is in binary format), so you have to replace the function name including path, not just the path.

Again, please upload the files here in forum. Do not provide links. Links will die...