2 files - adding additional row with column names and adding new columns in the same time

Hi,

I am new - of course I am - that is why I ask this question.

I have an issue with combining 2 files together - an example attached.

1 file contains multiple columns that have names and some rows with numbers/text in them

2nd file is a 2 row file. It contains the same names of the columns as the 1st file, plus some more columns with names. Also the second file contains another row - 2nd row are "explanations" for the column names in the 1st row.

The trick is to combine them together in a way to have all the names from the second file, all the explanations from the second file and also - the hardest part for me now - to move the data that is in the 1st file under specific name, to the proper name/column - that will be added/used from the second file.

If Anyone could help - I would be grateful.

Br, Maciej

Example1.xlsx

Transformation

Join

Preparation

Accepted answers

Bennel_Wilson

Hi @MaciejL ,

Apologies for the error. I had accidentally uploaded a different workflow that I had created to bring an absolutely unstructured data into an analyzable form., hence the complication in the workflow.

The workflow for your query was actually a very simple one, similar to what @mattreynolds had posted earlier. Attached below is my actual workflow for your query.

Regards

Benn.

P.S: If this solves your query, kindly mark it as Solution.

Combine files.yxmd

All comments

mattreynolds

Well I'm 0-and-1 on recommending Union as an approach tonight but this seems to replicate your output. Depending on the requirements, may be able to use a Dynamic Rename (Developer Tools) if the values from the second file aren't needed in the data. Key thing may be setting the output order in the union.