Results 1 to 5 of 5

Thread: Data to change from Columns to Rowns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2010
    Posts
    143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Data to change from Columns to Rowns

    Hi All,

    I have a data as below. Now I need the same with Cust ID, Cust Name and 3rd Column as Fiscal Month and get the Month column to Rows and 4th Column as Amount and get those in rows respectively. Can someone help

    Cust ID Cust Name Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun
    124 abc 654654 566 155 6496 65467 686 886 9963 966 9863 35 668
    416546 wer 48 56698 656 6633 666 6623 335 333 8 66 77
    665265 thj 6864 6289 66 566 6645 582 33 6366 65 366 98 546
    65654 yui 967
    9852 opz 646 339
    6823 tre 649 33694
    57 xyz 888 669
    783 pqr 698 3969

    Thanks,
    Mahi

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,936
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data to change from Columns to Rowns

    Data Range
    A
    B
    C
    D
    1
    Cust ID
    Attribute
    Value
    Custom
    2
    124
    Jul
    654654
    abc
    3
    124
    Aug
    566
    abc
    4
    124
    Sep
    155
    abc
    5
    124
    Oct
    6496
    abc
    6
    124
    Nov
    65467
    abc
    7
    124
    Dec
    686
    abc
    8
    124
    Jan
    886
    abc
    9
    124
    Feb
    9963
    abc
    10
    124
    Mar
    966
    abc
    11
    124
    Apr
    9863
    abc
    12
    124
    May
    35
    abc
    13
    124
    Jun
    668
    abc
    14
    416546
    Jul
    48
    wer
    15
    416546
    Aug
    56698
    wer
    16
    416546
    Sep
    656
    wer
    17
    416546
    Oct
    6633
    wer
    18
    416546
    Nov
    666
    wer
    19
    416546
    Dec
    6623
    wer
    20
    416546
    Jan
    335
    wer
    21
    416546
    Feb
    333
    wer
    22
    416546
    Apr
    8
    wer
    23
    416546
    May
    66
    wer
    24
    416546
    Jun
    77
    wer
    25
    665265
    Jul
    6864
    thj
    26
    665265
    Aug
    6289
    thj
    27
    665265
    Sep
    66
    thj
    28
    665265
    Oct
    566
    thj
    29
    665265
    Nov
    6645
    thj
    30
    665265
    Dec
    582
    thj
    31
    665265
    Jan
    33
    thj
    32
    665265
    Feb
    6366
    thj
    33
    665265
    Mar
    65
    thj
    34
    665265
    Apr
    366
    thj
    35
    665265
    May
    98
    thj
    36
    665265
    Jun
    546
    thj
    37
    65654
    Aug
    967
    yui
    38
    9852
    Jul
    646
    opz
    39
    9852
    Aug
    339
    opz
    40
    6823
    Jul
    649
    tre
    41
    6823
    Aug
    33694
    tre
    42
    57
    Jul
    888
    xyz
    43
    57
    Aug
    669
    xyz
    44
    783
    Jul
    698
    pqr
    45
    783
    Aug
    3969
    pqr
    Using Power Query, here is the Mcode

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cust ID", Int64.Type}, {"Cust Name", type text}, {"Jul", Int64.Type}, {"Aug", Int64.Type}, {"Sep", Int64.Type}, {"Oct", Int64.Type}, {"Nov", Int64.Type}, {"Dec", Int64.Type}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"May", Int64.Type}, {"Jun", Int64.Type}}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Cust ID"}, "Attribute", "Value"),
        #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Attribute] = "Cust Name" then [Value] else null),
        #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
        #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Attribute] <> "Cust Name"))
    in
        #"Filtered Rows"
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


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

    Cool Re: Data to change from Columns to Rowns

    shorter:

    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        UnpivotOC = Table.UnpivotOtherColumns(Source, {"Cust ID", "Cust Name"}, "Month", "Value")
    in
        UnpivotOC
    Last edited by sandy666; Oct 9th, 2019 at 05:09 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
    I don't use vba in any form!

  4. #4
    Board Regular
    Join Date
    Jan 2010
    Posts
    143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data to change from Columns to Rowns

    Thank you all. This 2nd code worked easily. Thank a lot
    alansidman and sandy

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

    Default Re: Data to change from Columns to Rowns

    you are welcome

    have a nice day
    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
    I don't use vba in any form!

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
  •