Forum Discussion

KLolli's avatar
KLolli
Copper Contributor
May 19, 2021
Solved

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...
BI & Data Analysis
  • 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

     

    https://1drv.ms/x/s!AsuqEFbTJNgcgaAy4BA7qsu5MHYgyQ?e=aatBxh - see BetterQuery

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
May 19, 2021

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
May 20, 2021
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
    May 20, 2021
    Your screenshot shows only 1 column with letters and not 654 columns.
    • KLolli's avatar
      KLolli
      Copper Contributor
      May 20, 2021
      Hi,

      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.
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        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

         

        https://1drv.ms/x/s!AsuqEFbTJNgcgaAy4BA7qsu5MHYgyQ?e=aatBxh - see BetterQuery

Resources