Split a column as rows into a new table

[TobiasHolm]

Hi

We have a large table with a lot of columns. One of them contains the number of incoming, resolved and waiting tasks in that month. Another column contains the information, as to whether it is incoming, resolved or wating.

We would link to take this first column and use it as rows in new table where the second column becomes the three rows. Maybe keeping the month, but that is not important.

For example:

Month	Designation	Tasks
Jan	Incoming	1
Jan	Resolved	2
Jan	Wating	3
Feb	Incoming	4
Feb	Resolved	5
Feb	Waiting	6

Should become:

Month	Incomning	Resolved	Waiting
Jan	1	2	3
Feb	4	5	6

None of the tables are calculated so we can use both Power Query and DAX.

Thank you

[Anonymous · Accepted solution]

Hi @TobiasHolm 

You can put the following code to Adcanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JR8sxLzs/NzEsHMg2VYnVgwkGpxfk5ZakpQKYRknB4YmYJRLExWNQtNQnVDBMkYSQzTJGEEWaYKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Designation = _t, Tasks = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Designation", type text}, {"Tasks", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Designation]), "Designation", "Tasks", List.Sum)
in
    #"Pivoted Column"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TobiasHolm]

Thanks. Exactly what we were looking for!