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 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
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
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
May 20, 2021Silver Contributor
Your screenshot shows only 1 column with letters and not 654 columns.
- KLolliMay 20, 2021Copper ContributorHi,
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.- LorenzoMay 20, 2021Silver Contributor
(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
- LorenzoMay 20, 2021Silver Contributor
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
- KLolliMay 20, 2021Copper ContributorDear 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