Forum Discussion

KLolli's avatar
KLolli
Copper Contributor
May 19, 2021

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

 

  • 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

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor

    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))
    • KLolli's avatar
      KLolli
      Copper Contributor
      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
      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Your screenshot shows only 1 column with letters and not 654 columns.

Resources