Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Converting specific columns to vertical one record

  1. #1
    Board Regular
    Join Date
    Mar 2016
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Converting specific columns to vertical one record

    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

    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

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,819
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: convering spicific coulmns to vertical one record

    try

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


    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Type = Table.TransformColumnTypes(Source,{{"FILE NO", Int64.Type}, {"RCVD DATE", type date}, {"COUNTRY", type text}, {"ID_NO", Int64.Type}, {"PID_NO", type text}, {"TEST", type text}, {"RESULT", type text}, {"REPORT DATE", type date}}),
        Pivot = Table.Pivot(Type, List.Distinct(Type[TEST]), "TEST", "RESULT"),
        Reorder = Table.ReorderColumns(Pivot,{"FILE NO", "RCVD DATE", "COUNTRY", "ID_NO", "PID_NO", "TEST1", "TEST2", "TEST3", "TEST4", "TEST5", "TEST6", "FINALRESULT", "REPORT DATE"})
    in
        Reorder
    Last edited by sandy666; Jul 9th, 2019 at 09:49 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular
    Join Date
    Mar 2016
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: convering spicific coulmns to vertical one record

    Quote Originally Posted by sandy666 View Post
    try

    [COLOR=#FFFFFF ]FILE NO[/COLOR] [COLOR=#FFFFFF ]RCVD DATE[/COLOR] [COLOR=#FFFFFF ]COUNTRY[/COLOR] [COLOR=#FFFFFF ]ID_NO[/COLOR] [COLOR=#FFFFFF ]PID_NO[/COLOR] [COLOR=#FFFFFF ]TEST[/COLOR] [COLOR=#FFFFFF ]RESULT[/COLOR] [COLOR=#FFFFFF ]REPORT DATE[/COLOR] [COLOR=#FFFFFF ]FILE NO[/COLOR] [COLOR=#FFFFFF ]RCVD DATE[/COLOR] [COLOR=#FFFFFF ]COUNTRY[/COLOR] [COLOR=#FFFFFF ]ID_NO[/COLOR] [COLOR=#FFFFFF ]PID_NO[/COLOR] [COLOR=#FFFFFF ]TEST1[/COLOR] [COLOR=#FFFFFF ]TEST2[/COLOR] [COLOR=#FFFFFF ]TEST3[/COLOR] [COLOR=#FFFFFF ]TEST4[/COLOR] [COLOR=#FFFFFF ]TEST5[/COLOR] [COLOR=#FFFFFF ]TEST6[/COLOR] [COLOR=#FFFFFF ]FINALRESULT[/COLOR] [COLOR=#FFFFFF ]REPORT DATE[/COLOR]
    1
    05/01/2018
    USA
    1.23E+09
    ABCDEFGH TEST1 NOT TESTED
    25/05/2018
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH NOT TESTED NOT TESED POSITIVE NOT TESTED NOT TESTED PENDING POSTTIVE
    25/05/2018
    1
    05/01/2018
    USA
    1.23E+09
    ABCDEFGH TEST2 NOT TESED
    25/05/2018
    1
    05/01/2018
    USA
    1.23E+09
    ABCDEFGH TEST3 POSITIVE
    25/05/2018
    1
    05/01/2018
    USA
    1.23E+09
    ABCDEFGH FINALRESULT POSTTIVE
    25/05/2018
    1
    05/01/2018
    USA
    1.23E+09
    ABCDEFGH TEST4 NOT TESTED
    25/05/2018
    1
    05/01/2018
    USA
    1.23E+09
    ABCDEFGH TEST5 NOT TESTED
    25/05/2018


    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Type = Table.TransformColumnTypes(Source,{{"FILE NO", Int64.Type}, {"RCVD DATE", type date}, {"COUNTRY", type text}, {"ID_NO", Int64.Type}, {"PID_NO", type text}, {"TEST", type text}, {"RESULT", type text}, {"REPORT DATE", type date}}),
        Pivot = Table.Pivot(Type, List.Distinct(Type[TEST]), "TEST", "RESULT"),
        Reorder = Table.ReorderColumns(Pivot,{"FILE NO", "RCVD DATE", "COUNTRY", "ID_NO", "PID_NO", "TEST1", "TEST2", "TEST3", "TEST4", "TEST5", "TEST6", "FINALRESULT", "REPORT DATE"})
    in
        Reorder
    thank you for your reply and help.

    will yo please explain how to apply it.

    do I need to type table1 in a specific cell before the table. Thank you again

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,819
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: convering spicific coulmns to vertical one record

    You'll need PowerQuery (Get&Transform)

    your source data should be a table (Excel Table!)
    then from Data tab select From Table and in a new Window (Power Query Editor) find and open Advanced Editor then replace code there with code from the post

    btw. don't quote whole post !
    Last edited by sandy666; Jul 9th, 2019 at 10:44 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    Board Regular
    Join Date
    Mar 2016
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting specific columns to vertical one record

    thank you and sorry for that!

    I went though the steps and I got the following although "'FILE NO" is there:

    Expression.Error: The column 'FILE NO' of the table wasn't found.
    Details:
    FILE NO

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,819
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Converting specific columns to vertical one record

    The code is based on your example from the first post.
    You can see the steps in the code, so just do them for your original data or post a real example of your source data.

    Power Query is case sensitive so FILE NO is not the same as File No and check for the spaces before and after the headers

    Headers must be exactly the same in the code like in source table
    and Table Name must be the same as in the code (and vice versa), eg.
    Code:
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Last edited by sandy666; Jul 9th, 2019 at 11:54 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  7. #7
    Board Regular
    Join Date
    Mar 2016
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting specific columns to vertical one record

    sorry for the inconvenience.

    I fixed the hears, I look silly with this..where shall I insert "Table1" to name the table :

    Expression.Error: We couldn't find an Excel table named 'Table1'.
    Details:
    Table1

  8. #8
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,819
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Converting specific columns to vertical one record

    select your whole source range then use Ctrl+T (with has headers option checked)
    then go to Name Manager (Ctrl+F3) and see the name of this table then use this name in the code

    if your source range is a Table then check name of this table and use it in the code
    Last edited by sandy666; Jul 9th, 2019 at 12:18 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  9. #9
    Board Regular
    Join Date
    Mar 2016
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting specific columns to vertical one record

    Thank you sooo much! worked perfectly !

  10. #10
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,819
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Converting specific columns to vertical one record

    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •