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 No Line Item Index 0001 Red Book 1 0002 Blue Book 2 0002 Gree...
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.

     

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"