locked
Transpose single column to multiple columns based on another column value RRS feed

  • Question

  • Hi all

    I've a dataset I'm importing consisting of 11 columns where a single record [Column A] is repeated dependent on the number of individual records in another column [Column D].

    Column A | Column B | Column C | Column D 

           a      |       x       |        j       |      1

           a      |       x       |        j       |      2

           a      |       x       |        j       |      3

           b      |       y       |        k      |      5

           b      |       y       |        k      |      6

    I'd like to use Power Query to convert the above to what is below, transposing column D our across further columns so that only a single record [Column A] can be created:

    Column A | Column B | Column C | Column D | Column E | Column F

           a      |       x       |        j       |      1        |       2       |       3

           b      |       y       |        k       |      5       |       6       |    [blank]

    Can anyone offer any suggestions on how this could be accomplished?

    Thanks,

    Steve

    Tuesday, April 21, 2020 8:53 PM

Answers

  • Excel 365 Pro Plus with PowerPivot and Power Query.
    Fleshed it out with many dates and values,
    to what you might see in business.
    Arranged by sequence of occurrence.
    http://www.mediafire.com/file/fjafukra78xb0of/04_21_20.xlsx/file
    http://www.mediafire.com/file/rv6cdy5np0wzjd1/04_21_20.pdf/file



    Wednesday, April 22, 2020 3:26 AM
  • Hi, Steve.

    This variant of code made for example by Herbert Seidenberg

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        GroupedRows = Table.Group(Source, {"A", "B", "C"}, {{"list", each [Amt], type list}}),
        AddedTemp = Table.AddColumn(GroupedRows, "temp", each Table.FromRows({ {[A],[B],[C]} & [list] }, {"A","B","C"} & List.Transform({1..List.Count([list])}, Text.From) ), type table ),
        Combine = Table.Combine( AddedTemp[temp] )
    in
        Combine
    • Proposed as answer by Lz._ Wednesday, April 22, 2020 8:28 AM
    • Marked as answer by Imke FeldmannMVP Sunday, June 14, 2020 5:28 AM
    Wednesday, April 22, 2020 7:14 AM

All replies

  • Excel 365 Pro Plus with PowerPivot and Power Query.
    Fleshed it out with many dates and values,
    to what you might see in business.
    Arranged by sequence of occurrence.
    http://www.mediafire.com/file/fjafukra78xb0of/04_21_20.xlsx/file
    http://www.mediafire.com/file/rv6cdy5np0wzjd1/04_21_20.pdf/file



    Wednesday, April 22, 2020 3:26 AM
  • Hi, Steve.

    This variant of code made for example by Herbert Seidenberg

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        GroupedRows = Table.Group(Source, {"A", "B", "C"}, {{"list", each [Amt], type list}}),
        AddedTemp = Table.AddColumn(GroupedRows, "temp", each Table.FromRows({ {[A],[B],[C]} & [list] }, {"A","B","C"} & List.Transform({1..List.Count([list])}, Text.From) ), type table ),
        Combine = Table.Combine( AddedTemp[temp] )
    in
        Combine
    • Proposed as answer by Lz._ Wednesday, April 22, 2020 8:28 AM
    • Marked as answer by Imke FeldmannMVP Sunday, June 14, 2020 5:28 AM
    Wednesday, April 22, 2020 7:14 AM
  • Thanks for this....works great.
    Wednesday, April 22, 2020 8:57 PM
  • Tried this variant also and this handily removes the need for the pivot table.

    Many thanks.

    Wednesday, April 22, 2020 8:58 PM