Clean Data Multiple Workbooks Varying Columns

[*shudder*]

Hi Guys,

I am trying to combine data from multiple workbooks with differing number of columns and the data isn't in a table.

Examples of the type if data I an trying to combine:

Book1

No.	1
Name	Name1
DoB	DoB1
Department	Dept1

Book2

No.	1	2	3	4
Name	Name1	Name2	Name3	Name4
DoB	DoB1	DoB2	DoB3	DoB4
Department	Dept1	Dept1	Dept2	Dept3

I have what various videos about and spent a few hours looking at transpose and unpivot options to no avail.

Any guidance would be much appreciated.


Stuart

 

[*shudder*]

I am looking to stack the columns so the result should look like this:

Name	Name1
DoB	DoB1
Department	Dept1
Name	Name2
DoB	DoB2
Department	Dept1
Name	Name3
DoB	DoB3
Department	Dept2
etc..

All of the individual 'names' are unique values.

 

[I_cant_read]

Hi, the way I would approach that would be something like this...

1.)Make two Queries which unpivot the first and second table. (So you would have Query1 result of unpivot of Table 1 and Query 2 result of unpivot of Table 2)
2.)Once you made the two tables append the data.
Add a conditional column which returns the ff: Name returns 1/ DoB returns 2/ Department 3 (naming it as "Card Sort") (if column name No. = "Name" Output 1 etc...)
After creating the conditional column sort your data first by attribute then by Card Sort (See screenshot as reference)
Then Add Column > Index column which begins with 1.

And then maybe have a filter like Index <=6 for Query 3
Then another filter which is >=7 for Query 4
(this is vaguely dividing your dataset into two)

Next would be adding another Index(2) for Query 3 and Query 4
Finally merge Query 3 and Query 4 using the newly created Index "Index(2)"

Hopefully this is fine for a solution.

 

[*shudder*]

2.)Once you made the two tables append the data.
Add a conditional column which returns the ff: Name returns 1/ DoB returns 2/ Department 3 (naming it as "Card Sort") (if column name No. = "Name" Output 1 etc...)
After creating the conditional column sort your data first by attribute then by Card Sort (See screenshot as reference)
Then Add Column > Index column which begins with 1.

Sorry, I am lost on this bit. What is ff?

I can get the data from a single workbook with the following process:

1) Add Index
2) Unpivot Columns
3) I then have the attribute and index to associate the records i.e.

Attribute (column)

Index

Question

Values (unpivot)

(I tried creating a custom column concatenating these attribute/ index but I get an error)

How do I get this to apply across multiple sources (workbooks)?

I have about 150 workbooks and anything from 1 to 50 response columns. The only thing that is fixed is the question set at 62 rows.

 

[I_cant_read]

FF/Following

You could invoke a custom function to access each table/get data from file and tinker around it.

()=>

Something like a list of the excel files you want to extract, then invoking each file with the custom function. (i usually do this with hyperlinks though and not excel files)

M Language Functions - PowerQuery M

Describes using functions in the Power Query M formula language

docs.microsoft.com