nested if, i think in power query

[rafterse]

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 Task	Work Group
initial task a	secondary task a		workgroup A
initial task B	secondary task B	tertiery task B	Workgroup B

[v-xinruzhu-msft]

Hi 

[rafterse]

hi\

[v-xinruzhu-msft · Accepted solution]

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.

[rafterse]

thanks i'll give it a go