Creating a column to help pivot data

[RichardP]

Hi all,

Does anyone have advice on how to go about pivoting some data?  My current data looks like this:

Order No	Line Item	Index
0001	Red Book	1
0002	Blue Book	2
0002	Green Book	3
0003	Blue Book	4
0004	Yellow Book	5
0004	Purple Book	6
0004	Green Book	7
0004	Blue Book	8

What I'm trying to pivot to is this structure:

Order No	Line Item 1	Line Item 2	Line Item 3	Line Item 4
0001	Red Book
0002	Blue Book	Green Book
0003	Blue Book
0004	Yellow Book	Purple Book	Green Book	Blue Book

My problem is that there are varying numbers of Line Items per Order, so how do I create a column in the source data to use as the "Values column" to create the new columns?

Thanks in advance for your help 🙂

Richard

[PattemManohar · Accepted solution]

@RichardP  Please try this in Power Query Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUQpKTVFwys/PBjINlWJ1wOJGQI5TTmkqTMIIWcK9KDU1DyZjDJMxRtNiApMwAXIiU3Ny8sthUqbIUgGlRQU5cF1myFIoFpkjyyBbZKEUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order No" = _t, LineItem = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order No", Int64.Type}, {"LineItem", type text}, {"Index", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order No"}, {{"AllRows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "LineItem", each [AllRows][LineItem]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"LineItem", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "LineItem", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"LineItem.1", "LineItem.2", "LineItem.3", "LineItem.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"LineItem.1", type text}, {"LineItem.2", type text}, {"LineItem.3", type text}, {"LineItem.4", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"AllRows"})
in
    #"Removed Columns"

For Reference, Here is the overview of the steps that are implemented as above.

[Anonymous]

another way using the pivot function with an appropriate aggregation function

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjAwMFTSUQpKTVFwys/PBjINlWJ1wOJGQI5TTmkqTMIIWcK9KDU1DyZjDJMxRtNiApMwAXIiU3Ny8sthUqbIUgGlRQU5cF1myFIoFpkjyyBbZKEUGwsA", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type text) meta [Serialized.Text = true])
    in
      type table[#"Order No" = _t, #"Line Item" = _t, Index = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Order No", Int64.Type}, {"Line Item", type text}, {"Index", Int64.Type}}
  ),
  #"Removed Columns" = Table.RemoveColumns(#"Changed Type", {"Index"}),
  #"Pivoted Column" = Table.Pivot(
    Table.TransformColumnTypes(#"Removed Columns", {{"Order No", type text}}, "it-IT"), 
    List.Distinct(
      Table.TransformColumnTypes(#"Removed Columns", {{"Order No", type text}}, "it-IT")[#"Order No"]
    ), 
    "Order No", 
    "Line Item", 
    (t) => Text.Combine(t, "#")
  ),
  #"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
  #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
  #"Split Column by Delimiter" = Table.SplitColumn(
    #"Transposed Table", 
    "Column2", 
    Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), 
    {"Column2.1", "Column2.2", "Column2.3", "Column2.4"}
  )
in
  #"Split Column by Delimiter"

[jthomson]

Try searching for ranking within a group to get you started