Create new column based on 3 other columns

[rodfernandez]

Hi everyone,
First of all, sorry for my poor english 😅
I'm new to Power Query and I'm a bit stuck trying to create the following rule.

I have the following table, with the first 4 columns and I need to create column number 5 (named "NEED THIS")

Row	ID	Time		Task	NEED THIS
1	AAA	27-10-2021 13:02:55		1	1
2	AAA	27-10-2021 13:02:57		1
3	AAA	27-10-2021 13:03:07		1
4	AAA	27-10-2021 13:03:58		2	2
5	AAA	27-10-2021 13:04:05		2
6	AAA	27-10-2021 13:04:38		3	3
7	AAA	27-10-2021 13:05:22		4	4
8	AAA	27-10-2021 13:05:31		4
9	AAA	27-10-2021 13:06:11		2	2b
10	AAA	27-10-2021 13:06:24		2
11	AAA	27-10-2021 13:07:47		3	3b
12	AAA	27-10-2021 13:08:37		4	4b
13	AAA	27-10-2021 13:08:43		4
14	AAA	27-10-2021 13:09:57		3	3c
15	AAA	27-10-2021 13:10:44		3
16	AAA	27-10-2021 13:12:20		4	4c
17	AAA	27-10-2021 13:13:49		5	5
18	AAA	27-10-2021 13:13:59		5
19	AAA	27-10-2021 13:15:02		6	6
20	BBB	29-10-2021 15:22:18		1	1
21	BBB	29-10-2021 15:22:52		1
22	BBB	29-10-2021 17:02:11		2	2
23	BBB	29-10-2021 17:05:29		3	3
24	BBB	29-10-2021 17:05:35		3
25	BBB	29-10-2021 17:44:03		2	2b
26	BBB	29-10-2021 18:01:11		3	3b
27	BBB	30-10-2021 10:28:12		4	4
28	BBB	30-10-2021 10:28:51		4
29	BBB	30-10-2021 10:40:48		5	5
30	BBB	30-10-2021 11:01:09		6	6
31	BBB	30-10-2021 11:02:50		6

The column I need is for the purpose of identifying the first time a task occurs for the same ID. For some reason, sometimes it appears that the same task occurs more than once in a row, for those cases, I am only interested in identifying the first time it occurs. for example, in row 7 and 8 the same task (4) occurs more than once in a row,  in cases like this I am only interested in identifying the first time it occurs (row 7).

But I am also interested in being able to identify when a task is repeated, but not in a row. For example, in row 12 and 13, task 4 occurs again for the same ID (AAA), but this set of tasks does not occur immediately after or before tasks 4 that are in row 7 and 8, in this case I I would like to be able to identify the first time this task is repeated (row 12), but adding a letter to it, since it is the second time it happens for the same ID (if it is the second time it happens, I would like to add a letter "b", if it is the third time it happens, I would like to add a letter "c" ...and so on).

If someone can help me or guide me, I will be deeply grateful

Thanks !!

[edhans · Accepted solution]

Here you go @rodfernandez 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZNNbsQwCIWvMsp6RuLHxA675BrRbNr736FAnGashkqQBfqEIe+x7xNOz2ldV/tSfSG8CAgfyAqkIlbFyPdzn+gfsnbyESRnpMVIlpyU5tVIJyUji4J08ug55yR7T450smakKHm/Eulky0nGTh6vLxk5K+K50VegCDlLZdgJU5mqlnou1dumQjXleq7V2VSqpoWHxTDVajn0jxG+DzZTC0FL6Wxvm8mFpATntL1tJphF8R8vkYFmilnIifYJMslQzNtWnSPjBHygbdscXS7U7aLx4se1YI4KDUcQXvuLVr+syzMHyhlqjZfB3OGgBGUZNCBJ0GLHxaNpab5lmwIew344keovy3CxoNQUxwOjlqMyXljseYcWiza4gOEWRR8WlkFaxgw1vaCjNsD7Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Row = _t, ID = _t, Time = _t, Task = _t, #"NEED THIS" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Time", type datetime}}, "en-BM"),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type with Locale", 
            {"ID", "Task"}, 
            {
                {"First Group Index", each Table.AddIndexColumn(_, "First Index", 1, 1, Int64.Type )},
                {"All Rows", each _, type table [Row=nullable text, ID=nullable text, Time=nullable datetime, Task=nullable text, NEED THIS=nullable text]}}, GroupKind.Local),
    #"Grouped Rows1" = 
        Table.Group(
            #"Grouped Rows", 
            {"ID", "Task"}, 
            {
                {"Second Group Index", each Table.AddIndexColumn(_, "Second Index", 1, 1, Int64.Type)}
            }
        ),
    #"Expanded Second Group Index" = Table.ExpandTableColumn(#"Grouped Rows1", "Second Group Index", {"First Group Index", "All Rows", "Second Index"}, {"First Group Index", "All Rows", "Second Index"}),
    #"Expanded First Group Index" = Table.ExpandTableColumn(#"Expanded Second Group Index", "First Group Index", {"Time", "NEED THIS", "First Index"}, {"Time", "NEED THIS", "First Index"}),
    #"Added Letter" = 
        Table.AddColumn(
            #"Expanded First Group Index", 
            "Letter", 
            each 
                if [First Index] = 1  and [Second Index] = 1 then [Task]
                else if [First Index] = 1 then [Task] &  Character.FromNumber(96+[Second Index]) 
                else null,
            type text
            ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Letter",{"ID", "Task", "Time", "NEED THIS", "Letter"})
in
    #"Removed Other Columns"

It returns this. I used null. You could replace that with "" if you want, which is empty. 

What I did was:

1. Grouped by task and *D, but used GroupKind.Local so it would break the groups by the repeating tasks. So not all 2s were grouped together. Only Groups of 2s were.
2. Then I grouped the entire thing again by the ID and task.
3. Each grouping added an index, and preserved all rows.
4. Then I expanded carefully the indexes and all rows.
5. Then used a formula to find where the groups were 1 for first index and something for the second. If the first, just returned the task. For the 2nd and following, added b, c, d, etc.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[rodfernandez]

Amazing thanks!

[edhans]

Glad I was able to help out @rodfernandez