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 a...
  • Lorenzo's avatar
    Lorenzo
    May 20, 2021

    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

Resources