Converting specific columns to vertical one record

[dankar]

I have similar to this table with many columns and thousand of rows (patients)

- as you can see each patient have 6 tests(test1 to test6) with different 6 result.
- all other columns have the same result.
- I need to switch the results from vertical to horizontal so I the table can show one record for each patient. like table2

Table 1:

Before:

FILE NO	RCVD DATE	COUNTRY	ID_NO	PID_NO	TEST	RESULT	REPORT DATE
1	5/1/2018	USA	1234567890	ABCDEFGH	TEST1	NOT TESTED	5/25/2018
1	5/1/2018	USA	1234567890	ABCDEFGH	TEST2	NOT TESED	5/25/2018
1	5/1/2018	USA	1234567890	ABCDEFGH	TEST3	POSITIVE	5/25/2018
1	5/1/2018	USA	1234567890	ABCDEFGH	FINAL RESULT	POSTTIVE	5/25/2018
1	5/1/2018	USA	1234567890	ABCDEFGH	TEST4	NOT TESTED	5/25/2018
1	5/1/2018	USA	1234567890	ABCDEFGH	TEST5	NOT TESTED	5/25/2018
1	5/1/2018	USA	1234567890	ABCDEFGH	TEST6	PENDING	5/25/2018

<tbody>
</tbody>

Table 2

After:

FILE NO

RCVD DATE

COUNTRY

ID_NO

PID_NO

TEST1

TEST2

TEST3

TEST4

TEST5

TEST6

FINAL RESULT

REPORT DATE

1

5/1/2018

USA

1234567890

ABCDEFGH

NOT TESTED

NOT TESTED

POSITIVE

NOT TESTED

NOT TESTED

PENDING

POSITIVE

5/25/2018

<tbody>
</tbody>

 

[dankar]

thank you for the links.
I have one more question plz.
If I want to add more columns to your code, I need to type the column name for each one in {"COLUMN NAME", type text} or there is another way like to select the fields and then excel transfer it to code

Thank you

 

[sandy666]

If I want to add more columns to your code

You mean what?

you've 8 columns so where you want to add more columns?

FILE NO	RCVD DATE	COUNTRY	ID_NO	PID_NO	TEST	RESULT	REPORT DATE
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST1	NOT TESTED	25/05/2018​
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST2	NOT TESED	25/05/2018​
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST3	POSITIVE	25/05/2018​
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	FINALRESULT	POSTTIVE	25/05/2018​
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST4	NOT TESTED	25/05/2018​
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST5	NOT TESTED	25/05/2018​
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST6	PENDING	25/05/2018​

 

[sandy666]

you can add more rows in source table, like (yellow)

FILE NO	RCVD DATE	COUNTRY	ID_NO	PID_NO	TEST	RESULT	REPORT DATE
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST1	NOT TESTED	25/05/2018​
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST2	NOT TESED	25/05/2018​
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST3	POSITIVE	25/05/2018​
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	FINALRESULT	POSTTIVE	25/05/2018​
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST4	NOT TESTED	25/05/2018​
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST5	NOT TESTED	25/05/2018​
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST6	PENDING	25/05/2018​
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST7	PENDING	25/05/2018​
2​	06/01/2018​	USA	1234567891​	ABCDEFGH	TEST8	NOT TESTED	26/05/2018​

firsrt yellow has only TEST added , the rest is the same
second yellow has TEST added and all the rest is different so in the result you will get two rows
just add what you want and refresh green table or use Ctrl+Alt+F5

FILE NO

RCVD DATE

COUNTRY

ID_NO

PID_NO

TEST1

TEST2

TEST3

TEST4

TEST5

TEST6

FINALRESULT

REPORT DATE

TEST7

TEST8

1​

05/01/2018​

USA

1234567890​

ABCDEFGH

NOT TESTED

NOT TESED

POSITIVE

NOT TESTED

NOT TESTED

PENDING

POSTTIVE

25/05/2018​

PENDING

2​

06/01/2018​

USA

1234567891​

ABCDEFGH

26/05/2018​

NOT TESTED

I'll be back in a few hours so if you've more questions try describe in detail what you want to achieve

 

[dankar]

the excel table was exported from access query.(contains the main columns I need)
I was asking if I created a bigger table which included these columns and another ones also.

I will use your code and alter it to add the new columns, so I was asking do I need to type each new columns in the same format you used,or there is an easier way..like for example selecting the columns and excel will translate it the code you gave me..
thank you

 

[sandy666]

with new columns

FILE NO	RCVD DATE	COUNTRY	ID_NO	PID_NO	TEST	RESULT	REPORT DATE	NewColumn	NewColumn1
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST1	NOT TESTED	25/05/2018​	DATA	DATA1
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST2	NOT TESED	25/05/2018​	DATA	DATA1
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST3	POSITIVE	25/05/2018​	DATA	DATA1
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	FINALRESULT	POSTTIVE	25/05/2018​	DATA	DATA1
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST4	NOT TESTED	25/05/2018​	DATA	DATA1
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST5	NOT TESTED	25/05/2018​	DATA	DATA1
1​	05/01/2018​	USA	1234567890​	ABCDEFGH	TEST6	PENDING	25/05/2018​	DATA	DATA1

result

FILE NO

RCVD DATE

COUNTRY

ID_NO

PID_NO

TEST1

NewColumn

NewColumn1

TEST2

TEST3

TEST4

TEST5

TEST6

FINALRESULT

REPORT DATE

1​

05/01/2018​

USA

1234567890​

ABCDEFGH

NOT TESTED

DATA

DATA1

NOT TESED

POSITIVE

NOT TESTED

NOT TESTED

PENDING

POSTTIVE

25/05/2018​

why don't you test it?! It's easy, even with MS Access

I strongly recommend to read information from links in previous post and here also: Unpivot Columns

 

[dankar]

I'm really grateful for your help.

Surly I will read the links you sent, but the issue for now that I need to show the result tomorrow.

I already tested the adding more columns before I see your reply
I just fallowed your code and added the new columns.


Honestly I needed this to be solved in Access but no one gave me a solution in the access forum except you when I posted it here.

Can I use the same code in access ? please can you explain to me how to do it in Access .

again thank you so much!

 

[sandy666]

No, You can't use this code IN MS Access directly

You can get table from access db via Power Query and use appropriate M-code and on the end show result in the sheet

 

[sandy666]

if you want all in MS Access try this forum: https://www.mrexcel.com/forum/microsoft-access/

 

[dankar]

I already posted it in this link but no one gave a correct answer,its ok to use Excel.

sorry to bother you again.

I noticed in my data result that there is another field that have different results.

in my main example : "REPORT DATE " could also have different entries like the "Result" which preventing from having one record for every patient.

I tried to add it to your code but I got an error :

"Expression.Error: We cannot convert the value "REPORT DATE" to type Function.
Details:
Value=REPORT DATE
Type=Type

This is what I did ( I added "REPORT DATE") here:

Pivot = Table.Pivot(Type, List.Distinct(Type[TEST]), "TEST", "RESULT","REPORT DATE")

Is what I did wrong?

Thank you.

 

[sandy666]

ab ovo:

I hope your source data is an Excel Table
so ....
select any cell in your source table,
then
ribbon - Data tab - From Table

it will open new window (Power Query Editor)
set proper data type for each column then
select TEST column and from the ribbon - Transform - Pivot Column
choose : RESULT
Advanced options: don't aggregate - ok
then Close&Load to..