Split column into X by value in another column

[Algonar]

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

Type	Date	Value
Gross	Jan 1	10
Cost	Jan 1	6
Net	Jan 1	4

I'll have

Date	Gross	Cost	Net
Jan 1	10	6	4

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.:

Date	Gross	Cost	Net
Jan 1	10	0	0
Jan 1	0	6	0
Jan 1	0	0	4

Can anyone help get to the desired outcome?

Thank you!

[Nathaniel_C]

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 · Accepted solution]

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

[Algonar]

Thank you Nathaniel!

[Nathaniel_C]

Hi @Algonar - you are welcome!
Let me recommend Supercharge Power BI, and the latest version of M is for Data Monkey, if you will be working in this field...on Amazon for less than 30 each.
Nathaniel