cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rodfernandez
Helper I
Helper I

Create new column based on 3 other columns

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")

RowIDTime TaskNEED THIS
1AAA27-10-2021 13:02:55 11
2AAA27-10-2021 13:02:57 1 
3AAA27-10-2021 13:03:07 1 
4AAA27-10-2021 13:03:58 22
5AAA27-10-2021 13:04:05 2 
6AAA27-10-2021 13:04:38 33
7AAA27-10-2021 13:05:22 44
8AAA27-10-2021 13:05:31 4 
9AAA27-10-2021 13:06:11 22b
10AAA27-10-2021 13:06:24 2 
11AAA27-10-2021 13:07:47 33b
12AAA27-10-2021 13:08:37 44b
13AAA27-10-2021 13:08:43 4 
14AAA27-10-2021 13:09:57 33c
15AAA27-10-2021 13:10:44 3 
16AAA27-10-2021 13:12:20 44c
17AAA27-10-2021 13:13:49 55
18AAA27-10-2021 13:13:59 5 
19AAA27-10-2021 13:15:02 66
20BBB29-10-2021 15:22:18 11
21BBB29-10-2021 15:22:52 1 
22BBB29-10-2021 17:02:11 22
23BBB29-10-2021 17:05:29 33
24BBB29-10-2021 17:05:35 3 
25BBB29-10-2021 17:44:03 22b
26BBB29-10-2021 18:01:11 33b
27BBB30-10-2021 10:28:12 44
28BBB30-10-2021 10:28:51 4 
29BBB30-10-2021 10:40:48 55
30BBB30-10-2021 11:01:09 66
31BBB30-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 !!

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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. 

edhans_0-1635374542821.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

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. 

edhans_0-1635374542821.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Amazing thanks!

Glad I was able to help out @rodfernandez 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors