Forum Discussion

TobiasHolm's avatar
TobiasHolm
Frequent Visitor
3 years ago
Solved

Split a column as rows into a new table

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:

 

MonthDesignationTasks

Jan

Incoming

1
JanResolved2
JanWating3
FebIncoming4
FebResolved5
FebWaiting6

 

Should become:

MonthIncomningResolvedWaiting
Jan123
Feb456

 

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

 

Thank you

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.