Create new columns by using a specific column based on criterias

[betulurh]

Hello everyone, 

I am new in PowerBI and I have a question that I could not find the proper soluotion: 

I have table like this: 

Role	Risk	Operation
Accountant	A028;0113	Create
Accountant	A028;0113	Edit
Accountant	A028;0113	Delete
Accountant	C028;011A	Add
Accountant	C028;011A	Divide

I want to create new columns by using operation column based on role and risk column like this:

Role	Risk Identities	Operation 1	Operation 2	Operation 3
Accountant	A028;0113	Create	Edit	Delete
Accountant	C028;011A	Add	Divide	-

I would be really appreciate if you could help. Thanks in advance. 

[Ahmedx]

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

[betulurh]

Thank you for your reply and help! However, since I am new in PowerBI this answer was too complex to implement for me. That's why I did not mark the "Accept as Solution" button. Thanks again.

[Ahmedx]

see 2 variant

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOzi/NK0nMK1HSUXI0MLKwNjA0NAaynYtSE0tSlWJ18KhxTckswa/CJTUnFZspzlA1jiD1KSn4FbhklmWmAA2JBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Role = _t, Risk = _t, Operation = _t]),
    from = Table.TransformColumnTypes(Source,{{"Role", type text}, {"Risk", type text}, {"Operation", type text}}),
    from2 = Table.Group(from, {"Role", "Risk"}, {{"Count", (x)=> x[Operation]}}),
    f = (go)=>
Table.FromList (
   {go},
    ( x ) => x,
   List.Transform ( { 1 .. List.Count ( {go}{0} )  }, ( y ) => "Operation-" & Text.From ( y ) )
  ),
    Custom1 = Table.TransformColumns( from2,{"Count",f}),
    #"Expanded Count" = Table.ExpandTableColumn(Custom1, "Count", {"Operation-1", "Operation-2", "Operation-3"}, {"Operation-1", "Operation-2", "Operation-3"})
in
    #"Expanded Count"

https://1drv.ms/u/s!AiUZ0Ws7G26Rh0-lKldJxpmRvQ3X?e=ycpoG5

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26Rh05bb-MGZXagSih5?e=LCRCma

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOzi/NK0nMK1HSUXI0MLKwNjA0NAaynYtSE0tSlWJ18KhxTckswa/CJTUnFZspzlA1jiD1KSn4FbhklmWmAA2JBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Role = _t, Risk = _t, Operation = _t]),
    from = Table.TransformColumnTypes(Source,{{"Role", type text}, {"Risk", type text}, {"Operation", type text}}),
    fx =(y)=>
[
   t =  Table.Transpose( Table.FromColumns({y})),
   N= Table.ColumnNames(t),
   L = List.ReplaceValue(N, "Column", "Operation", Replacer.ReplaceText),
   Results = Table.RenameColumns( t,List.Zip( { N,L} ),MissingField.Ignore )][Results],

    Custom1 = from,
    from2 = Table.Group(Custom1, {"Role", "Risk"}, {{"Count",(x)=> x[Operation]}}),
    Custom2 = Table.TransformColumns(from2,{"Count",fx}),
    #"Expanded Count" = Table.ExpandTableColumn(Custom2, "Count", {"Operation1", "Operation2", "Operation3"}, {"Operation1", "Operation2", "Operation3"})
in
   #"Expanded Count"