Forum Discussion

RichardP's avatar
RichardP
Helper I
7 years ago
Solved

Creating a column to help pivot data

Hi all,

 

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

 

Order NoLine ItemIndex
0001Red Book1
0002Blue Book2
0002Green Book3
0003Blue Book4
0004Yellow Book5
0004Purple Book6
0004Green Book7
0004Blue Book8

 

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

 

Order NoLine Item 1Line Item 2Line Item 3Line Item 4
0001Red Book   
0002Blue BookGreen Book  
0003Blue Book   
0004Yellow BookPurple BookGreen BookBlue 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

Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    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.

     

3 Replies

  • jthomson's avatar
    jthomson
    Solution Sage
    7 years ago

    Try searching for ranking within a group to get you started

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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"