Forum Discussion

Algonar's avatar
Algonar
New Member
4 years ago
Solved

Split column into X by value in another column

Hi everyone,

 

I have a set of data with multiple columns including 2 date-specific columns. 2 of the other columns include:

 

1. Value

2. Value type

 

Value type can be gross, cost or net

 

I want to split column "Value" into these 3 types, while retaining the date details so that instead of having

TypeDateValue
GrossJan 110
CostJan 16
NetJan 14

 

I'll have

 

DateGrossCostNet
Jan 11064

 

This is the desired outcome.

 

Right now, when splitting into 3 columns manually, I get multiple rows for the same date with 0 in other values, i.e.:

 

DateGrossCostNet
Jan 11000
Jan 1060
Jan 1004

 

Can anyone help get to the desired outcome?

 

Thank you!

Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    4 years ago

    Hi Algonar ,
    Here are the pics for pivoting your table. in reverse order.
    Original table, change column order, pivot column.
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel

     

    Pivot and result

4 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hello @Algonar ,
    The best way to do this is in power query by going to power query and pasting the code below into the advanced editor for a blank query.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KLy5W0lEy1PVKzAPRBkqxOtFKzvnFJUiiZmBBv1RkMROl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Date", type date}, {"Value", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Type", "Date", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Type]), "Type", "Value", List.Sum)
in
    #"Pivoted Column"

     


    Let me know if you have any questions.

    If this solves your problems, mark it as the solution, so that others can easily find it. Congratulations 👍are also nice.
    Nathaniel

    • Nathaniel_C's avatar
      Nathaniel_C
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Hi Algonar ,
      Here are the pics for pivoting your table. in reverse order.
      Original table, change column order, pivot column.
      Let me know if you have any questions.

      If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
      Nathaniel

       

      Pivot and result