SOLVED

Transform a table in Excel: transpose vs pivot and repeaded cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2370237%22%20slang%3D%22en-US%22%3ETransform%20a%20table%20in%20Excel%3A%20transpose%20vs%20pivot%20and%20repeaded%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2370237%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20with%20transforming%20a%20table%3A%20from%20the%20example%20on%20the%20left%2C%20to%20the%20one%20on%20the%20right.%3C%2FP%3E%3CP%3EI%20tried%20pivot%20column%20but%20where%20more%20dates%20are%20present%20for%20the%20same%20name%2C%20I%20had%20an%20error.%3C%2FP%3E%3CP%3EI%20added%20an%20additional%20column%20and%20then%20pivot%2C%20but%20ended%20up%20with%20thousands%20of%20columns%2C%20one%20for%20each%20individual%20date.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22KLolli_1-1621462635117.png%22%20style%3D%22width%3A%20685px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281955i5965A91E1F1CCABA%2Fimage-dimensions%2F685x250%3Fv%3Dv2%22%20width%3D%22685%22%20height%3D%22250%22%20role%3D%22button%22%20title%3D%22KLolli_1-1621462635117.png%22%20alt%3D%22KLolli_1-1621462635117.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EMany%20thanks%20for%20your%20time%20and%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKL%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2370237%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2370388%22%20slang%3D%22en-US%22%3ERe%3A%20Transform%20a%20table%20in%20Excel%3A%20transpose%20vs%20pivot%20and%20repeaded%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2370388%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F644120%22%20target%3D%22_blank%22%3E%40KLolli%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EE2%3A%20%3DUNIQUE(A2%3AA7)%0AF1%3A%20%3D%22DATE%20%22%26amp%3BSEQUENCE(%2CMAX(COUNTIFS(A2%3AA7%2CE2%23)))%0AF2%3A%20%3DTRANSPOSE(FILTER(%24B%242%3A%24B%247%2C%24A%242%3A%24A%247%3DE2))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2371132%22%20slang%3D%22en-US%22%3ERe%3A%20Transform%20a%20table%20in%20Excel%3A%20transpose%20vs%20pivot%20and%20repeaded%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2371132%22%20slang%3D%22en-US%22%3EDear%20Detlef%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20very%20much%20for%20your%20reply!%3CBR%20%2F%3EI%20have%20data%20in%20cells%20F2%3AXX1000%20and%20I%20typed%20the%20first%20formula%20in%20E2%3DUNIQUE(F2%3AXX2)%20but%20it%20gave%20me%20a%20%23Spill!%20error.%20Will%20read%20more%20to%20find%20out%20what%20I%20am%20doing%20wrong.%3CBR%20%2F%3E%3CBR%20%2F%3EMany%20thanks%2C%3CBR%20%2F%3E%3CBR%20%2F%3EKL%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2371271%22%20slang%3D%22en-US%22%3ERe%3A%20Transform%20a%20table%20in%20Excel%3A%20transpose%20vs%20pivot%20and%20repeaded%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2371271%22%20slang%3D%22en-US%22%3EYour%20screenshot%20shows%20only%201%20column%20with%20letters%20and%20not%20654%20columns.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2371420%22%20slang%3D%22en-US%22%3ERe%3A%20Transform%20a%20table%20in%20Excel%3A%20transpose%20vs%20pivot%20and%20repeaded%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2371420%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3EEssentially%20I%20start%20from%20a%20table%20like%20the%20one%20on%20the%20left%2C%202%20columns%2C%20one%20for%20names%20and%20one%20for%20dates%20and%20I%20want%20a%20%22result%20table%22%20like%20the%20one%20on%20the%20left.%3CBR%20%2F%3EI%20managed%20to%20get%20a%20table%20that%20has%20many%20rows%20(one%20for%20each%20date%20in%20column%20B)%20using%20Power%20query-pivot%20colunm%20and%20I%20applied%20your%20Unique%20formula%20to%20that%20(now%20will%20try%20on%20the%20original%20table).%20I%20managed%20to%20get%20it%20work%2C%20and%20it%20does%20the%20job%2C%20now%20my%20problem%20is%20that%20I%20don't%20know%20how%20to%20automate%20this.%3CBR%20%2F%3EI%20need%20tables%20in%20order%20to%20be%20able%20to%20update%20automatically%20the%20data%20every%20time%20I%20receave%20a%20new%20extract%2C%20that's%20why%20I%20was%20hoping%20there%20was%20a%20solution%20via%20Power%20query.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2371634%22%20slang%3D%22en-US%22%3ERe%3A%20Transform%20a%20table%20in%20Excel%3A%20transpose%20vs%20pivot%20and%20repeaded%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2371634%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F644120%22%20target%3D%22_blank%22%3E%40KLolli%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Demo.png%22%20style%3D%22width%3A%20570px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282153i830340E85DE978DA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Demo.png%22%20alt%3D%22Demo.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E(one%20way)%20Power%20Query%20%2F%20M%20code%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20ChangedTypes%20%3D%20Table.TransformColumnTypes(Source%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22Alpha%22%2C%20type%20text%7D%2C%20%7B%22Date%22%2C%20type%20date%7D%7D%0A%20%20%20%20)%2C%0A%20%20%20%20GroupedRows%20%3D%20Table.Group(ChangedTypes%2C%20%7B%22Alpha%22%7D%2C%0A%20%20%20%20%20%20%20%20%7B%22DATA%22%2C%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20Table.Transpose(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Table.SelectColumns(_%2C%20%7B%22Date%22%7D)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%2C%20type%20table%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20)%2C%0A%20%20%20%20ColumnNames%20%3D%20List.TransformMany(%7B%22Column%22%2C%20%22DATE%20%22%7D%2C%0A%20%20%20%20%20%20%20%20each%20%7B1..List.Max(List.Transform(GroupedRows%5BDATA%5D%2C%20Table.ColumnCount))%7D%2C%0A%20%20%20%20%20%20%20%20(x%2C%20y)%3D%26gt%3B%20x%20%26amp%3B%20Text.From(y)%0A%20%20%20%20)%2C%0A%20%20%20%20SplittedList%20%3D%20List.Split(ColumnNames%2C%20List.Count(ColumnNames)%2F2)%2C%0A%20%20%20%20ExpandedData%20%3D%20Table.ExpandTableColumn(GroupedRows%2C%20%22DATA%22%2C%0A%20%20%20%20%20%20%20%20List.First(SplittedList)%0A%20%20%20%20)%2C%0A%20%20%20%20RenamedColumns%20%3D%20Table.RenameColumns(ExpandedData%2C%0A%20%20%20%20%20%20%20%20List.Zip(%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%20List.First(SplittedList)%2C%20List.Last(SplittedList)%20%7D%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20)%2C%0A%20%20%20%20TypedDate%20%3D%20Table.TransformColumnTypes(RenamedColumns%2C%0A%20%20%20%20%20%20%20%20List.Transform(List.Last(SplittedList)%2C%20each%20%7B_%2C%20type%20date%7D)%0A%20%20%20%20)%0Ain%0A%20%20%20%20TypedDate%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20avail.%20%3CA%20href%3D%22https%3A%2F%2F1drv.ms%2Fx%2Fs!AsuqEFbTJNgcgaAy4BA7qsu5MHYgyQ%3Fe%3Dvx2DYE%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehere%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

 

I need help with transforming a table: from the example on the left, to the one on the right.

I tried pivot column but where more dates are present for the same name, I had an error.

I added an additional column and then pivot, but ended up with thousands of columns, one for each individual date.

KLolli_1-1621462635117.png

Many thanks for your time and help.

 

KL

 

9 Replies

@KLolli 

E2: =UNIQUE(A2:A7)
F1: ="DATE "&SEQUENCE(,MAX(COUNTIFS(A2:A7,E2#)))
F2: =TRANSPOSE(FILTER($B$2:$B$7,$A$2:$A$7=E2))
Dear Detlef,

Thank you very much for your reply!
I have data in cells F2:XX1000 and I typed the first formula in E2=UNIQUE(F2:XX2) but it gave me a #Spill! error. Will read more to find out what I am doing wrong.

Many thanks,

KL
Your screenshot shows only 1 column with letters and not 654 columns.
Hi,

Essentially I start from a table like the one on the left, 2 columns, one for names and one for dates and I want a "result table" like the one on the left.
I managed to get a table that has many rows (one for each date in column B) using Power query-pivot colunm and I applied your Unique formula to that (now will try on the original table). I managed to get it work, and it does the job, now my problem is that I don't know how to automate this.
I need tables in order to be able to update automatically the data every time I receave a new extract, that's why I was hoping there was a solution via Power query.

@KLolli 

Demo.png

(one way) Power Query / M code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(Source,
        {{"Alpha", type text}, {"Date", type date}}
    ),
    GroupedRows = Table.Group(ChangedTypes, {"Alpha"},
        {"DATA", each
            Table.Transpose(
                Table.SelectColumns(_, {"Date"})
            ), type table
        }
    ),
    ColumnNames = List.TransformMany({"Column", "DATE "},
        each {1..List.Max(List.Transform(GroupedRows[DATA], Table.ColumnCount))},
        (x, y)=> x & Text.From(y)
    ),
    SplittedList = List.Split(ColumnNames, List.Count(ColumnNames)/2),
    ExpandedData = Table.ExpandTableColumn(GroupedRows, "DATA",
        List.First(SplittedList)
    ),
    RenamedColumns = Table.RenameColumns(ExpandedData,
        List.Zip(
            { List.First(SplittedList), List.Last(SplittedList) }
        )
    ),
    TypedDate = Table.TransformColumnTypes(RenamedColumns,
        List.Transform(List.Last(SplittedList), each {_, type date})
    )
in
    TypedDate

 

Sample avail. here

best response confirmed by KLolli (Occasional Contributor)
Solution

@KLolli 

On reflection the following is more straightforward and less complex to understand + Columns names are fully dynamic: whatever the names of your 2 columns are in the source table this will work as long as the column containing the dates is the 2nd column

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SourceColumns = Table.ColumnNames(Source),
    FirstColumn = List.First(SourceColumns),
    GroupedRows = Table.Group(Source, FirstColumn,
        {"DATA", each Table.FromRows(
                { {Table.FirstValue(_)} & Table.Column(_, List.Last(SourceColumns)) }
            ), type table
        }
    ),
    CombinedTables = Table.Combine(GroupedRows[DATA]),
    DateColumns = List.Transform({1..Table.ColumnCount(CombinedTables)-1}, each
        "DATE " & Text.From(_)
    ),
    RenamedColumns = Table.RenameColumns(CombinedTables,
        List.Zip({
            Table.ColumnNames(CombinedTables),
            {FirstColumn} & DateColumns
        })
    ),
    TypedDate = Table.TransformColumnTypes(RenamedColumns,
        List.Transform(DateColumns, each {_, type date})
    )
in
    TypedDate

 

Sample updated - see BetterQuery

Dear L z.,

Thank you so much for the time you spent to solve my problem.
I am not an expert and I will try to figure out how to apply your solution, and I'll let you know if I'll manage to do that, but in the meantime, I just want to thank you again!

KL
Dear L z.,

Thank you so so much! I manage to use it and it work perfectly with my data set.
Now will try to learn better what each step is/mean.
Once more, thank you! Fantastic!
KL

@KLolli Glad I could help & Thanks for posting back. Just in case PQ doc is here