Forum Discussion

rafterse's avatar
rafterse
Helper I
2 years ago
Solved

nested if, i think in power query

Hey all ,

i'm struggeling with this one 

i want to create a sequence of columns based on the workgroup column grouping 

Data

Work Group   Task 
Work Group a  Initial task a
Work Group a  Secondary task a
Work Group B  Initial task B
Work Group B  secondary task B
Work Group B  tertiery task B

 

thanks in advance 

output needed 

1st task 2nd Task 3rd TaskWork Group 
initial task a   secondary task a   workgroup A   
initial task B  secondary task B  tertiery task B  Workgroup B  
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi rafterse 

    You can create a blank query and input the following code to advanced editor.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs8vylZwL8ovLVBIVFBQ0lHyzMssyUzMUShJLM5WSFSK1cGiJjg1OT8vJbGoEocqJ0yTnLCrKUY1CYeqktSiksxUJEWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work Group" = _t, Task = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Work Group", type text}, {"Task", type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Task", Text.Proper, type text}}),
    #"Added Custom" = Table.AddColumn(#"Uppercased Text", "Task_type", each let a=Text.PositionOf([Task],"Task")
in Text.Start([Task],a-1)),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Task_type]), "Task_type", "Task")
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.

     

4 Replies

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi rafterse 

        You can create a blank query and input the following code to advanced editor.

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs8vylZwL8ovLVBIVFBQ0lHyzMssyUzMUShJLM5WSFSK1cGiJjg1OT8vJbGoEocqJ0yTnLCrKUY1CYeqktSiksxUJEWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work Group" = _t, Task = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Work Group", type text}, {"Task", type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Task", Text.Proper, type text}}),
    #"Added Custom" = Table.AddColumn(#"Uppercased Text", "Task_type", each let a=Text.PositionOf([Task],"Task")
in Text.Start([Task],a-1)),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Task_type]), "Task_type", "Task")
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.