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 a...
- May 20, 2021
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