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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

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