Power query - Add string if identical string found above

[beltalowda]

Hi,

Im fairly new to this game so sorry if this is not a good question. 

I have this huge dataset with multiple excel sheets that are structured with date and operation as seen below:

Date		Operation
01.02.2022		A
03.02.2022		A
07.02.2022		A
12.02.2022		A
14.02.2022		B
19.02.2022		B
21.02.2022		B
25.02.2022		C
26.02.2022		C
04.03.2022		C
05.03.2022		A
07.03.2022		A
12.03.2022		A

So we start with operation "A", proceede with operation "B", then "C" before we go back to operaion "A". 

I really want to add a new coloumn where I change the name of operation A to "A (2)" if that operation have been done before.

like this:

Date		Operation		Operation modified
01.02.2022		A		A
03.02.2022		A		A
07.02.2022		A		A
12.02.2022		A		A
14.02.2022		B		B
19.02.2022		B		B
21.02.2022		B		B
25.02.2022		C		C
26.02.2022		C		C
04.03.2022		C		C
05.03.2022		A		A (2)
07.03.2022		A		A (2)
12.03.2022		A		A (2)
13.03.2022		C		C (2)
15.03.2022		A		A (3)

I have been able to do this with some really messy VBA code previously, but wanted to do this with power query editor. Really happy if someone could point me in the rigth direction 

edit:

Changed the visuals of the table to improve readability

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc67DcAwCEXRXagtOTz8ScrEY1jef404BVKA9kg87pyEzBkHQIluWukD8dA9MIIUlUfl8gIOUlWGSrMiuXio9rf4PAl5W8SvcJjhZvO29CDn72q9", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Operation = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Operation", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Operation"}, {{"Temp", each _, type table [Date=nullable date, Operation=nullable text, Index=number]}}),
    //Function Start
            fxProcess=(Tbl)=>
        let
            #"Added Index" = Table.AddIndexColumn(Tbl, "Index.1", 0, 1, Int64.Type),
            #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try [Index]-#"Added Index"[Index]{[Index.1]-1} otherwise 2),
            #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] > 1)),
            #"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "Index.2", 1, 1, Int64.Type),
            #"Removed Columns" = Table.RemoveColumns(#"Added Index1",{"Index", "Index.1", "Custom"}),
            #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Operation modified", each if [Index.2]=1 then [Operation] else [Operation]&" ("&Text.From([Index.2])&")"),
            #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Index.2"})
        in
            #"Removed Columns1",
    //Function End
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcess", each fxProcess([Temp])),
    #"Expanded fxProcess" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxProcess", {"Date", "Operation modified"}, {"Date", "Operation modified"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded fxProcess",{"Temp"}),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Operation", "Date"}, #"Removed Columns", {"Operation", "Date"}, "Removed Columns", JoinKind.LeftOuter),
    #"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Operation modified"}, {"Operation modified"}),
    #"Filled Down" = Table.FillDown(#"Expanded Removed Columns",{"Operation modified"})
in
    #"Filled Down"

[beltalowda]

WOW thanks.

That did work. I am just a humble physicist, and my usual strategy is to throw enough stuff on the wall until something sticks. Its infinetly better than anything I could put together. I did one minor adjusment, and added a line to align the date format with my system. Im one european date format. Pasted my adjustment below if anybody is interested! Thanks Vijay

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc67DcAwCEXRXagtOTz8ScrEY1jef404BVKA9kg87pyEzBkHQIluWukD8dA9MIIUlUfl8gIOUlWGSrMiuXio9rf4PAl5W8SvcJjhZvO29CDn72q9", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Operation = _t]),
#"Change Type" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-US"),
#"Changed Type1" = Table.TransformColumnTypes(#"Change Type",{{"Date", type date}, {"Operation", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Operation"}, {{"Temp", each _, type table [Date=nullable date, Operation=nullable text, Index=number]}}),
//Function Start
fxProcess=(Tbl)=>
let
#"Added Index" = Table.AddIndexColumn(Tbl, "Index.1", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try [Index]-#"Added Index"[Index]{[Index.1]-1} otherwise 2),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] > 1)),
#"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "Index.2", 1, 1, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Index1",{"Index", "Index.1", "Custom"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Operation modified", each if [Index.2]=1 then [Operation] else [Operation]&" ("&Text.From([Index.2])&")"),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Index.2"})
in
#"Removed Columns1",
//Function End
#"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcess", each fxProcess([Temp])),
#"Expanded fxProcess" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxProcess", {"Date", "Operation modified"}, {"Date", "Operation modified"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded fxProcess",{"Temp"}),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Operation", "Date"}, #"Removed Columns", {"Operation", "Date"}, "Removed Columns", JoinKind.LeftOuter),
#"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Operation modified"}, {"Operation modified"}),
#"Filled Down" = Table.FillDown(#"Expanded Removed Columns",{"Operation modified"})
in
#"Filled Down"