Forum Discussion
KLolli
May 19, 2021Copper Contributor
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
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
- Detlef_LewinSilver Contributor
E2: =UNIQUE(A2:A7) F1: ="DATE "&SEQUENCE(,MAX(COUNTIFS(A2:A7,E2#))) F2: =TRANSPOSE(FILTER($B$2:$B$7,$A$2:$A$7=E2))
- KLolliCopper ContributorDear 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- Detlef_LewinSilver ContributorYour screenshot shows only 1 column with letters and not 654 columns.