SOLVED

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

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. Many thanks for your time and help.

KL

9 Replies

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

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

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

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

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

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

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

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.

# Re: Transform a table in Excel: transpose vs pivot and repeaded cells (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

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

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

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

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

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

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

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

@KLolli Glad I could help & Thanks for posting back. Just in case PQ doc is here
{"ft_lang_label":"__label__en","ft_lang_prob":0.61230516,"math_prob":0.7763218,"size":2697,"snap":"2021-21-2021-25","text_gpt3_token_len":786,"char_repetition_ratio":0.17081322,"word_repetition_ratio":0.12426036,"special_character_ratio":0.31664813,"punctuation_ratio":0.23649907,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9775587,"pos_list":[0,1,2,3,4,5,6],"im_url_duplicate_count":[null,null,null,2,null,2,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2021-06-18T04:09:19Z\",\"WARC-Record-ID\":\"<urn:uuid:3293767e-077f-40fd-aa7e-1d4f5ea2e63d>\",\"Content-Length\":\"599014\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:769b59cd-f1a4-409b-b3bd-384151079ca5>\",\"WARC-Concurrent-To\":\"<urn:uuid:dfe6165c-74de-4e17-b315-06dccb3d8c1c>\",\"WARC-IP-Address\":\"23.67.106.105\",\"WARC-Target-URI\":\"https://techcommunity.microsoft.com/t5/excel/transform-a-table-in-excel-transpose-vs-pivot-and-repeaded-cells/td-p/2370237\",\"WARC-Payload-Digest\":\"sha1:D2BIIXDS4ETPOTATT5D6DRBDKU4FDXWQ\",\"WARC-Block-Digest\":\"sha1:XII54KF6FKEYKZCIM2V5SW2GPOPLR6L4\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2021/CC-MAIN-2021-25/CC-MAIN-2021-25_segments_1623487634616.65_warc_CC-MAIN-20210618013013-20210618043013-00041.warc.gz\"}"}